 |
 | 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 / March 2008SQL 2005 Rebuild Index
My department is experiencing difficulty with SQL Server 2005's Rebuild Index functionality. We are upgrading from 2000 to 2005 in the near future and I was tasked with creating a maintenance plan consisting of: Integrity Checks Backups Index Rebuilds DB History Cleanup DB Log and Backup Cleanup Under 2000 SP4, an Index Rebuild of a 10GB database took approximately 24 minutes. Under 2005 SP1, that same rebuild took 72 minutes. The maintenance plan's log isn't written until after the operation is complete and instead of several "Rebuilding indexes for table 'ABCDEFG' entries appearing every second as in 2000 SP4, it instead provides what I believe to the the SQL code used to perform the Index Rebuild and then only at the end. The problem I see with this is the lack of information as to where a possible fault might occur. Under 2000 SP4, were the maintenance plan to experience a breakdown at a certain table, we would be able to discern the location of the problem because the log would stop there. Under 2005 SP1, all we know is the code that was run. Is there any advice on how to speed up Index Rebuilds or is SQL Server 2005 just a whole lot slower in this regard. Victor Desjardins I would write a custom job that only reindexes indexes that need it like if the index is over 5 or 10% fragemented. There is an example on how to do this in the BOL documentation for sys.dm_db_index_physical_stats.  SignatureJason Massie www: http://statisticsio.com rss: http://feeds.feedburner.com/statisticsio > My department is experiencing difficulty with SQL Server 2005's Rebuild > Index [quoted text clipped - 31 lines] > > Victor Desjardins Note that fragmentation as reported by sys.dm_db_index_physical_stats or DBCC SHOWCONTIG is not a necessary condition for rebuilding an index. Ultimately, it depends on the nature of your workload whether fragmentation may have a material impact of your app's performance. If your app is not sensitive to index fragmentation, you have an option of just living with fragmentation. Linchi >I would write a custom job that only reindexes indexes that need it like if >the index is over 5 or 10% fragemented. There is an example on how to do [quoted text clipped - 37 lines] >> >> Victor Desjardins I copied the example from the BOL and ran it unaltered against an unused test database. I was expecting that once the job was run, there would be 0% fragmentation like with a file degragmenter. I copied the entire output (Executed: ALTER INDEX ... REBUILD) to WinMerge so I could compare the results after one pass with those from a second. The results, though very fast (maybe 3 minutes of execution time) were practically unchanged. 528 fragmented tables before. 528 tables after. Maybe one or two would shift from REBUILD to REORGANIZE but sometimes, it would go the other way. Eventually, one table did disappear from the list. Is this expected behaviour and if so, how am I to know that defragmentation has occured? It seems strange that we would have to resort to a SQL script to do something that a Maintenance Plan should be capable of accomplishing. > I would write a custom job that only reindexes indexes that need it like if > the index is over 5 or 10% fragemented. There is an example on how to do [quoted text clipped - 35 lines] > > > > Victor Desjardins If your table is a HEAP (No clustered index) then you can rebuild it all you want and the fragmentation will not change one bit. If the index has less than 8 pages rebuilding is essentially wasted effort as well. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >I copied the example from the BOL and ran it unaltered against an unused >test [quoted text clipped - 57 lines] >> > >> > Victor Desjardins >I copied the example from the BOL and ran it unaltered against an unused test database. I was expecting that once the job was run, there would be 0% fragmentation like with a file degragmenter. Coruscant, it seems like tables with few pages always report a high level of fragmentation. It's also so that fragmenation is not a problem in tables with few pages. Kalen Delaney has written a blog post about this recently. http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisite d.aspx >It seems strange that we would have to resort to a SQL script to do something that a Maintenance Plan should be capable of accomplishing. I have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size. http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html http://blog.ola.hallengren.com/_attachments/3440068/IndexOptimize.sql Ola Hallengren http://ola.hallengren.com > I copied the example from the BOL and ran it unaltered against an unused test > database. I was expecting that once the job was run, there would be 0% [quoted text clipped - 49 lines] > > > > > > Victor Desjardins |
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.