dm_db_index_physical_stats

Last Post 28 May 2009 03:00 AM by fosdba. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
fosdba
New Member
New Member

--
26 May 2009 10:23 PM
Hi there, is there something else that can be used to retrieve fragmentation values faster than dm_db_index_physical_stats on bigger tables ?

I've got a table with more or less 3.5 mil records with a non-clustered index on a numeric (19,0) data type, which allows nulls and are in a ascending order, but it takes about 7 minutes to retrieve this indexes fragmentation percentage with the above dm passing the limited parameter !

Any suggestions ?

Thanks in advance.
fosdba
New Member
New Member

--
27 May 2009 09:58 PM
I'm not sure about the hardware, cos it's an Itanuim Instance with lots of memory and processing power. The database is quite big in the region of 400 GB sitting on SAN storage on it's own luns. Maybe I can move it to faster storage, will have a look.

Thanks for for response.
fosdba
New Member
New Member

--
28 May 2009 03:00 AM
Hi there,

I found my mistake ... I was query the DM like below,

Select * from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
where object_id = '1653580929' (This took ages !)

instead of passing in my parameters for the indexes,

Select @Percent = avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(DB_ID(), @ObjectID, @IndexID, NULL, 'LIMITED')

Thanks again

You are not authorized to post a reply.

Acceptable Use Policy