October 10, 2013 at 2:31 am
I am going to incorporate into my reindexing script that any index under a certain size should be ignored.
Whats the rule of thumb for this? There must be a size where reindexing\rebuilding an index under a certain size just becomes inefficient.
October 10, 2013 at 3:10 am
Also,
Can you still reindex\Rebuild an Index if page locking is not allowed? Only Row locking?
October 10, 2013 at 6:47 am
SQLSteve (10/10/2013)
I am going to incorporate into my reindexing script that any index under a certain size should be ignored.Whats the rule of thumb for this? There must be a size where reindexing\rebuilding an index under a certain size just becomes inefficient.
Size of the index is pretty much irrelevant but i'll come back to that in a minute, it all depends on how fragmented the index is. You can use the dmv sys.dm_db_index_physical_stats to identify the fragmentation of our indexes.
From here you can decide wether or not it is best to re-organise or rebuild your index.
When performing a rebuild or reorganize of indexes there will be an impact on the amount of transaction log activity generated, generally linked to the size of the index, if you are using the full recovery model it can be beneficial to change to the bulk logged model during the index maintenace to reduce t-log growth, but be wary as this may affect your backup \ restore DR plans.
You can perfom a reorganize or rebuild online in certain editions of SQL Server such as Enterprise edition but not all editions. There are reasons why you can't even if the version supports it such as rebuilding an index which has a BLOB data column.
MCITP SQL 2005, MCSA SQL 2012
October 10, 2013 at 6:54 am
SQLSteve (10/10/2013)
I am going to incorporate into my reindexing script that any index under a certain size should be ignored.Whats the rule of thumb for this? There must be a size where reindexing\rebuilding an index under a certain size just becomes inefficient.
We've been running a customized version of Andrew Kelly's rebuild_indexes_by_db scripts for years with great success. In it, he provides a parameter that ignores indexes with a minimum 8k page count and he defaults that value to 10 pages. So indexes with 10 or fewer pages will be ignored.
You can find the article here: http://sqlmag.com/database-performance-tuning/rebuild-only-indexes-need-help.
October 10, 2013 at 7:06 am
Sorry I had skimmed through your question and thought it said over a specifc size. Yes, as mentioned in the other reply perfoming index maintenance on very small indexes will return little to no benefit.
MCITP SQL 2005, MCSA SQL 2012
October 10, 2013 at 7:07 am
RTaylor2208 (10/10/2013)
Sorry I had skimmed through your question and thought it said over a specifc size. Yes, as mentioned in the other reply perfoming index maintenance on very small indexes will return little to no benefit.
No problem thanks for your input 🙂 I current ignore any index below 5% fragmented, reorganize 5-40% and rebuild over 40%
October 10, 2013 at 7:08 am
George M Parker (10/10/2013)
SQLSteve (10/10/2013)
I am going to incorporate into my reindexing script that any index under a certain size should be ignored.Whats the rule of thumb for this? There must be a size where reindexing\rebuilding an index under a certain size just becomes inefficient.
We've been running a customized version of Andrew Kelly's rebuild_indexes_by_db scripts for years with great success. In it, he provides a parameter that ignores indexes with a minimum 8k page count and he defaults that value to 10 pages. So indexes with 10 or fewer pages will be ignored.
You can find the article here: http://sqlmag.com/database-performance-tuning/rebuild-only-indexes-need-help.%5B/quote%5D
Thanks George will have a look through the document and bare the 8k page count in mind!
October 10, 2013 at 7:13 am
You generally don't want to rebuild small indexes because it's a waste of time, fragmentation doesn't cause problems with small indexes. The rough threshold I usually hear is somewhere around 1000 pages. Now that's not set in stone, there are certainly cases where you want to rebuild indexes smaller than that (very low page density being an example), but purely from a logical fragmentation point it's not really worth rebuilding indexes much smaller than that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2013 at 7:27 am
I have found that unless I'm dealing with a VLD I can create a maintenance plan to reindex all of the databases. Once a database reaches the VLD size then it is no longer practical to reindex all of the indexes in the database. It would be a process that takes too much time.
You got the right idea about managing the worse offending fragmented indexes. There are scripts out there that you can alter to do what you want to do.
A bunch of years ago I found a script here in SSC and was able to modify it to do some additional things I needed. None the less the best part of that was I could set a threshold of fragmentation and manage only those indexes that were at or above that threshold. Once I understood the dynamics of the VLD I was able to tweak the threshold so I didn't overrun my maintenance window, which was 2 hours.
This process worked very well and it was reported that there was an overall performance gain with the application. Additionally nightly processes which included billing runs, dunning emails and GL exports to name a few ran much more efficiently.
Bottom line is it pays big time to put the effort to manage indexes, large or small, to maintain the best performance.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 10, 2013 at 7:33 am
I've often found that performance improvements attributed to index rebuilds are due rather to the stats updates that happen at the same time. I have on a number of occasions replaced index rebuild jobs with stats updates, put far more limited index rebuilds back and not noticed any difference in performance afterwards
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2013 at 7:57 am
I agree with Gail that the noticable performance improvement is more likely attributable to the updated statistics that occur with an index rebuild. That is another reason I like Andrew Kelly's script, it can be configured to update statistics on indexes that are re-organizized and also do so using a full scan instead of a sample.
I know a lot of DBA's who use Ola Hallengren's scripts instead and they seem to be happy with the results. I don't have as much experience with them but would highly recommend you check them out as well.
The bottom line is that you should implement a process that works best for your specific scenario.
October 10, 2013 at 9:08 am
Appreciate your feedback guys and Gail I have also had situations where processes have come to a standstill, I have updated stats and it has helped it move forward.
Update stats is always the last step in my maintenance procedure.
I will look to incorporate the minimum size into my scripts.
Thanks alot
October 10, 2013 at 9:37 am
SQLSteve (10/10/2013)
Appreciate your feedback guys and Gail I have also had situations where processes have come to a standstill, I have updated stats and it has helped it move forward.Update stats is always the last step in my maintenance procedure.
I will look to incorporate the minimum size into my scripts.
Thanks alot
Just make certain that whatever process you put in place doesn't update statistics with a sample that have just been updated with a full scan by an index rebuild. I cannot tell you how many times I have seen maintenance processes in place where indexes are rebuilt and then the statistics are updated with a 10% sample.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply