August 14, 2013 at 3:42 am
Expertrs,
I need to give fragementation report for one of the database.
I was executing the below query, however it takes lot of time to excecute.
Will it cause any blocking when I execute this query ?
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
Please guide.
Thanks.
August 14, 2013 at 4:41 am
i dont think it will affect
but add this condition also, hope it will ignore heaps
index_type_desc <> 'HEAP'
Regards
Durai Nagarajan
August 14, 2013 at 12:25 pm
Do you need DETAILED information ? Maybe LIMITED would be good enough ?
From MSDN:
The DETAILED mode scans all pages and returns all statistics.
The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA allocation unit of the index
http://technet.microsoft.com/en-us/library/ms188917(v=sql.105).aspx
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply