March 15, 2011 at 5:33 pm
I use this query "select p.database_id,p.object_id,p.index_id,p.index_type_desc,p.page_count,p.avg_fragmentation_in_percent,
o.name objectnames,o.type_desc,o.modify_date
from sys.dm_db_index_physical_stats (8,181575685,11,NULL,'Detailed') p
join sys.objects o
on o.object_id=p.object_id
where page_count>1000 and avg_fragmentation_in_percent>30
order by avg_fragmentation_in_percent desc"
Below is the output:
818157568511NONCLUSTERED INDEX1333589.891263592051 MyTableUSER_TABLE2011-01-27 09:45:19.057
Number of pages on MyTable are 13335 and frag. level is 90%. Even after i do a re-org it still shos 90 %.ANy idea? i cant rebuild because for now..why isnt reorg working?
March 16, 2011 at 5:42 am
Reorg is specifically for indexes that are only lightly fragmented as a way to give them an augmented performance metric at very low cost. You're looking 90% fragmentation. you need to rebuild that index.
"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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply