 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008Index Rebuild/Reorg using Maintenance Plans
I created two maintenance plans: one to reorg the indexes on two specific databases and another to rebuild the indexes on those same databases. When I ran a report called Index Physical Stats available from the Standard Reports in SSMS, the report indicated a number of indexes as needing reorganizing and/or rebuilding. So I executed the two maintenance plans and they succeeded. I then re-ran the Index Physical Stats report. The report is still recommending that I reorganize or rebuild a number of indexes. My expectation that after successfully executing the two maintenance plans to reorganize and rebuild, I would not see those recommendations in the report. Am I missing something? Will there always be fragmentation and thus a few indexes that the report will show as needing reorganizing or rebuilding? Thanks!  SignatureMichael Strozzo SQL DBA If the tables are Heaps you can reindex all day long and it wont change a thing. You need a clustered index on the table in order to be able to remove fragmentation with a rebuild or reorganize. If the indexes only have a few pages they will use mixed extents which will always show some level of fragmentation. If you post the actual results of the indexes that are causing you headaches we can tell you more of what the problems are. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >I created two maintenance plans: one to reorg the indexes on two specific > databases and another to rebuild the indexes on those same databases. [quoted text clipped - 12 lines] > > Thanks! Andrew, Thanks for the information. In looking at the still fragemented indexes most are non-clustered indexes. There were a few clustered indexes still showing as needing reorganizing/rebuilding, but as you pointed out, they had few pages. The largest one I saw had 30 pages and was still showing as needing reorganizing. ?Would you define a few pages as less than 50? Is that a good estimate? One other question: Is there a way to tell if a table uses mixed extents? This would enable you to identify those tables where fragmentation would always exist to some degree. Thanks, SignatureMichael Strozzo SQL DBA > If the tables are Heaps you can reindex all day long and it wont change a > thing. You need a clustered index on the table in order to be able to remove [quoted text clipped - 19 lines] > > > > Thanks! Few pages = less than 1000. The report should account for that, but apparently it doesn't. SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > Andrew, > [quoted text clipped - 32 lines] >> > >> > Thanks! Anything less than 8 pages will always use mixed extents. When you get more than 8 and rebuild the index it will take those pages and give them their own extent. Personally I usually don't worry about any fragmentation unless the number of pages is at least 100. But the rule of thumb in general seems to be 1000 or more. To me anything between 100 and 1000 will only take a second to rebuild anyway so I usually set my threshold at 100. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Andrew, > [quoted text clipped - 43 lines] >> > >> > Thanks! Andrew, Thanks. Wow--1000 pages--I didn't realize the number would be so high. Your lower number of 100 seems to be a more proactive approach. Do you use the report "Index Physical Stats" to show you the number of pages? Thanks, SignatureMichael Strozzo SQL DBA > Anything less than 8 pages will always use mixed extents. When you get more > than 8 and rebuild the index it will take those pages and give them their [quoted text clipped - 50 lines] > >> > > >> > Thanks! Yes you can get the fragmentation level and number of pages from the same DMV. In my code that looks for indexes to rebuild or defrag there is always an optional parameter to ignore any indexes with xx many pages or less. Then you can programmatically determine this if need be from one index to the next. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Andrew, > [quoted text clipped - 73 lines] >> >> > >> >> > Thanks! Michael To get entire picture of fragmentation I use SELECT db_name(database_id) AS DatabaseName, OBJECT_NAME(i.object_id) AS TableName ,i.name AS TableIndexName ,phystat.avg_fragmentation_in_percent, 'ALTER INDEX '+i.name+ ' ON '+OBJECT_NAME(i.object_id)+' REORGANIZE;' FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 40 and page_count>=1000 >I created two maintenance plans: one to reorg the indexes on two specific > databases and another to rebuild the indexes on those same databases. [quoted text clipped - 12 lines] > > Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.