February 26, 2016 at 7:27 am
One of my reorg process is select those high fragmentation index, but it gives me many duplicates, anyone could help me to fix it?
SELECT
DPS.index_id,
QUOTENAME(I.name) Name,
QUOTENAME(DB_NAME()) +'.'+ QUOTENAME(OBJECT_SCHEMA_NAME(I.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(I.[object_id])) ObjectName,
DPS.avg_fragmentation_in_percent,
DPS.fragment_count,
DPS.avg_fragment_size_in_pages,
DPS.index_depth,
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL ,NULL, NULL, NULL) AS DPS
INNER JOIN sys.indexes AS I
ON DPS.[object_id]= I.[object_id]
AND DPS.index_id = I.index_id
INNER JOIN sysindexes AS SI with (NOLOCK)
ON SI.id = DPS.object_id
AND SI.indid = DPS.index_id
WHERE
DPS.avg_fragmentation_in_percent > 5 and DPS.index_id > 0
and SI. rows > 100 and SI.rows <= 100000
ORDER BY
DPS.avg_fragmentation_in_percent DESC
February 26, 2016 at 10:17 am
It might be caused by partitions. I'm not sure what do you want to do with that.
February 26, 2016 at 11:26 am
I want to select any indexes which are higher than 5% for reorgs later
February 26, 2016 at 12:47 pm
Is it the alloc_unit_type_desc (LOB Data) from dm_db_index_physical_stats causing your pain?
February 29, 2016 at 11:46 am
Can you provide more detail information?
February 29, 2016 at 12:09 pm
Run sys.dm_db_index_physical_stats, is the "duplicate" due to LOB data?
February 29, 2016 at 12:26 pm
Do you have any suggestions for me to overcome this issue?
February 29, 2016 at 12:51 pm
Does this help?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply