April 20, 2010 at 3:16 pm
Let's you have a table with a clustered index and multiple nonclustered indexes. In general, don't you want to drop all the non-clustereds, rebuild the clustered and then rebuild all the non-clustereds?
What I'm wondering is if the following does this in the most efficient order? In other words, can I count on the engine to do this in the most optimized way?
ALTER INDEX ALL ON tblname REBUILD WITH(ONLINE = ON)
April 20, 2010 at 4:39 pm
It will rebuild the indexes, but it might not be the most optimal method, but I only say that because, depending on the table, some tables might be better off being modified in different ways. For the most part, rebuild the cluster then the non-clusters, but, I very seldom rebuild all indexes on a table every time. Instead we check the fragmentation and then only rebuild as needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 20, 2010 at 4:47 pm
Many indexes only need to have a Reorg performed on them.
There is a great script for doing this by Michelle Ufford, here[/url].
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 20, 2010 at 5:40 pm
Grant Fritchey (4/20/2010)
It will rebuild the indexes, but it might not be the most optimal method, but I only say that because, depending on the table, some tables might be better off being modified in different ways. For the most part, rebuild the cluster then the non-clusters, but, I very seldom rebuild all indexes on a table every time. Instead we check the fragmentation and then only rebuild as needed.
That's what we do as well. I am doing this one nightly for political reasons.
April 21, 2010 at 5:18 am
Whisper9999 (4/20/2010)
Grant Fritchey (4/20/2010)
It will rebuild the indexes, but it might not be the most optimal method, but I only say that because, depending on the table, some tables might be better off being modified in different ways. For the most part, rebuild the cluster then the non-clusters, but, I very seldom rebuild all indexes on a table every time. Instead we check the fragmentation and then only rebuild as needed.That's what we do as well. I am doing this one nightly for political reasons.
Oooh, one of those. Sorry about that.
Yes, this should do what you need, but I would keep an eye on it the first few times it runs, just in case.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply