November 12, 2012 at 12:25 pm
I was hoping to rebuild all indexes on one of our SQL Server 2008 databases over the weekend. The database is used on our campus to record data from numerous monitoring units around our campus. This is a warehouse so it is very large. I tried to rebuild the indexes, but it ran for over 12 hours... so I tried to Reorganize the indexes.... still would not complete. I then tried to individually rebuild the most fragmented indexes. It was working.... then I hit the one that would not finish. How should I handle indexes that will never rebuild? Thank you in advance.
Charlie
November 12, 2012 at 12:52 pm
Were the rebuilds blocked by something?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2012 at 1:01 pm
To be honest... I did not check... I will test off hours and let you know. Thanks for the advice!
November 13, 2012 at 1:51 pm
Hi,
You may have space issues for your tempdb, or generally space issues, and then it is slow.
Are you rebuilding online or offline?
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 13, 2012 at 1:58 pm
Hi Gail, Sorry in the delay getting back to you. I ran the test last night from home. The Index rebuild was running and another process was being blocked by the Index Rebuild (checking in Activity Monitor).
Charlie
November 13, 2012 at 2:00 pm
Hi IgoMi,
I have a lot of Space on the tempdb. The tempdb is located on a SAN. I was Rebuilding offline... then I tried to Reorganize the Index. Still ran forever.
Charlie
November 13, 2012 at 2:05 pm
Is the rebuild/reorg blocked by something else? If so, it will be sitting doing nothing until whatever is blocking it has finished.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2012 at 2:06 pm
IgorMi (11/13/2012)
Hi,You may have space issues for your tempdb, or generally space issues, and then it is slow.
Lack of space will result in either the files growing or the rebuild failing, not it running slow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2012 at 11:18 pm
rummings (11/12/2012)
I was hoping to rebuild all indexes on one of our SQL Server 2008 databases over the weekend. The database is used on our campus to record data from numerous monitoring units around our campus. This is a warehouse so it is very large. I tried to rebuild the indexes, but it ran for over 12 hours... so I tried to Reorganize the indexes.... still would not complete. I then tried to individually rebuild the most fragmented indexes. It was working.... then I hit the one that would not finish. How should I handle indexes that will never rebuild? Thank you in advance.Charlie
Try Ola's Index Optimization script
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
It help me to reduce my time from 12 hours to 2-3 hours for index rebuild/Re-organize .
-----------------------------------------------------------------------------
संकेत कोकणे
November 14, 2012 at 12:33 pm
Our index maintenance process includes a "SET LOCK_TIMEOUT" statement so that if any one index rebuild is blocked longer than the duration, it times out and moves on to the next index.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply