rummings (11/24/2013)
I have been given several large databases to tune. They have never had the statistics updated and I cannot tell when the indexes were last rebuilt. I tried to set up a Maintenance Plan to Rebuild all indexes.... ran for 12 hours and had not finished. I then modified the plan to Reorganize the indexes. This did not fair any better results. I then ran a query to find which indexes with over 1000 pages were fragmented. Three indexes were returned One was 45% fragmented with over 4 million pages ... another was 90% fragmented with over 2 million pages.Any suggestions as to how to best clean up these indexes? ... or should I just let the process run over the weekend and see if it will complete... then I can apply a weekly regular tuning method.
REBUILDing indexes requires additional space in the database. A LOT of additional space if your tables are large. Typically, and across the database REBUILD of all indexes will require a bit more than 100% of the largest index size (usually, a Clustered Index because it contains ALL of the data for the table as well as a B-Tree).
That bring us to a question... what is the GROWTH setup for on the database? If it's set for something small (typically somthing less than 100MB or {gasp} 1MB), then a huge amount of time will be dedicated to growing the database instead of using it.
I have a 20GB table in my database with 4 small indexes and a fairly wide covering index... it takes about 2 hours to rebuild all of the indexes on that and it's actually in pretty good shape already. Maybe you're just not being patient enough.
--Jeff Moden
Change is inevitable... Change for the better is not.