Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Index Tuning never finishes Expand / Collapse
Author
Message
Posted Sunday, November 24, 2013 12:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:29 AM
Points: 121, Visits: 392
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.
Post #1517106
Posted Sunday, November 24, 2013 1:43 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:16 AM
Points: 1,618, Visits: 1,554
Do 1 index at a time.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1517119
Posted Sunday, November 24, 2013 3:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:29 AM
Points: 121, Visits: 392
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.
Post #1517130
Posted Monday, November 25, 2013 9:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 892, Visits: 2,473
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.
Post #1517333
Posted Monday, November 25, 2013 9:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:29 AM
Points: 121, Visits: 392
Thank you for the information and advice.

Charlie
Post #1517348
Posted Monday, December 2, 2013 11:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
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.
Post #1519090
Posted Tuesday, December 3, 2013 12:47 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 2:06 PM
Points: 611, Visits: 516
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.

.
Post #1519378
Posted Friday, December 6, 2013 6:10 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 11:09 AM
Points: 989, Visits: 1,329
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
Post #1520536
Posted Tuesday, December 31, 2013 9:50 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 5, 2014 9:21 AM
Points: 776, Visits: 337
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.
Post #1526792
Posted Tuesday, December 31, 2013 11:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,768, Visits: 32,431
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526820
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse