Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Tuning never finishes


Index Tuning never finishes

Author
Message
rummings
rummings
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 461
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.
Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
Do 1 index at a time.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
rummings
rummings
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 461
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.
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
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.
rummings
rummings
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 461
Thank you for the information and advice.

Charlie
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 Visits: 2774
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.
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 585
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.

.
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1396
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
SQLBlimp
SQLBlimp
SSC Eights!
SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)

Group: General Forum Members
Points: 820 Visits: 495
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44981 Visits: 39869
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search