November 24, 2013 at 12:37 pm
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.
November 24, 2013 at 1:43 pm
Do 1 index at a time.
November 24, 2013 at 3:50 pm
I actually tried that.... still did not seem to finish. I was thinking about running T-SQL code stating to run OFFLINE. I had tried to run from Management Studio by right clicking the one Index and having it Reogranize. I did not see a way to force i tto be performed OFFLINE though.
November 25, 2013 at 9:31 am
http://technet.microsoft.com/en-us/library/ms188388.aspx
I agree - do one at a time, and just let it run to completion, however long it takes - if you do an OFFLINE rebuild, it's faster, but it is indeed offline for the duration. Make sure you've got plenty of tempdb space if you're using SORT_IN_TEMPDB = ON, and plenty of free space in the database.
I would absolutely REBUILD them if you can afford to - you get better ending results. If you don't have Enterprise edition, and can't afford the downtime of an offline rebuild, then you may be stuck with REORGANIZE, though, since you need an online operation to guarantee users won't be stuck.
See the
ONLINE= (ON | OFF)
syntax.
Note that ONLINE = ON is only available on Enterprise edition.
November 25, 2013 at 9:55 am
Thank you for the information and advice.
Charlie
December 2, 2013 at 11:54 pm
I've seen in my case where an index on one huge table was taking too much time. I did it with SORT_IN_TEMPDB = ON and it completed faster (though still took some time but comparatively). As Nadrek said, you should ensure that tempdb has enough space to perform the index rebuild operation.
December 3, 2013 at 12:47 pm
you can also use something like Ola.Hallengren's script and set a max time to run. let it run after production hours and have it finish before the day starts. This should help you get more of a hands off approach to resolving this.
.
December 6, 2013 at 6:10 am
If I were you I'd poke around to find an index manager that looks at the indexes that exceeds a threshold of fragmentation. Typically there is a much smaller subset of indexes that exceed say a 30% or more of fragmentation.
If you have very large tables who's indexes are severely fragmented then manually address them first then manage the smaller tables second. It may take some custom code to automate this process and may be well worth the effort.
Keep in mind that this is only the tip of the iceberg when it comes down to tuning. It is, however a good place to start.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 31, 2013 at 9:50 am
There is an excellent index defrag tool with tons of power that is free from Michelle Ufford. We use it in production here and I cannot say enough good things about Michelle or her indexing SP. Please find it here:
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
You need to get this into a scheduled job and run it weekly. If your database is very dynamic, run daily as a scheduled job. You may have a bitter pill to swallow the first time out, but after that the runs will be much shorter and your database will be much better behaved.
Thanks
John.
December 31, 2013 at 11:22 am
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.
January 2, 2014 at 8:19 am
Jeff Moden (12/31/2013)
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.
If anyone has a version of this updated for 2008+, please let me know, but the best document I've seen so far is http://download.microsoft.com/download/D/9/4/D948F981-926E-40FA-A026-5BFCF076D9B9/TEMPDB_Capacity_Planning_Index.doc
See page 8 of 10 for a chart, but ONLINE clustered index rebuilds take more space than the amount of space the amount the clustered index takes.
As far as time goes:
1) Check for fragmentation at a filesystem level first - Jeff's right to point out autogrowth, and the side effect of autogrowth is what can be called "Filesystem File level fragmentation".
2) Check for excessive VLFs second - autogrowth on log files often leads to this. Kimberly L. Tripp is one of the authorities here, see http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/[/url]
3) If you have either space or time constraints, start small - deliberately start with the smallest indexes/tables that have fragmentation you consider significant, and work up; this will provide progressively more free space in your user database MDFs as you continue, will start providing performance benefits, and... see 4.
4) Whatever you do, if maintenance windows are a concern, start estimating! How long did it take to do a ~100k page nonclustered index? A ~100k page clustered index? It's not going to be terribly consistent, but you'll get an idea of what takes how long - rebuilding clustered and nonclustered indexes are very different (or they were in 2000). You do not want to show up monday morning and see a critical table still locked, with business users unable to get their jobs done.
January 8, 2014 at 6:07 pm
Nadrek (1/2/2014)
4) Whatever you do, if maintenance windows are a concern, start estimating! How long did it take to do a ~100k page nonclustered index? A ~100k page clustered index? It's not going to be terribly consistent, but you'll get an idea of what takes how long - rebuilding clustered and nonclustered indexes are very different (or they were in 2000). You do not want to show up Monday morning and see a critical table still locked, with business users unable to get their jobs done.
+1
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply