September 30, 2010 at 5:06 pm
Hi,
I'm analyzing when exactly we required to perform Index defrag (Index reorg/rebuild) in SQL Server 2005 and I come across the article at mssqltips.com
which says:
Generally you should include some simple logic to weed-out small indexes (say for example, indexes with less than 5,000 or 10,000 pages). In many, many database schemas, there are 100's or 1000's of 'small' tables with multiple indexes - spending time rebuilding these can add up as the number grows. Use that time instead on the 10's or 100's of tables that really make a difference.
I would like to have your inputs on this considering to EXCLUDE indexes that having >5000 or 10,000??
Thanks
September 30, 2010 at 7:21 pm
pshaship (9/30/2010)
Hi,I'm analyzing when exactly we required to perform Index defrag (Index reorg/rebuild) in SQL Server 2005 and I come across the article at mssqltips.com
which says:
Generally you should include some simple logic to weed-out small indexes (say for example, indexes with less than 5,000 or 10,000 pages). In many, many database schemas, there are 100's or 1000's of 'small' tables with multiple indexes - spending time rebuilding these can add up as the number grows. Use that time instead on the 10's or 100's of tables that really make a difference.
I would like to have your inputs on this considering to EXCLUDE indexes that having >5000 or 10,000??
Thanks
There is a lot of debate about how effective it is to to defrag "smaller" indexes. The usual number trotted out is 1000 pages, which is in the documentation and comes from Paul Randal. But even that number is somewhat arbitrary. You can't defrag an index that is smaller than 8 pages, no matter what. Personally, I draw the line around 100 pages, but most of my systems can afford that. If they were larger, I might move that number higher, just to avoid work that doesn't give a lot of reward.
"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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply