Why does the first script show me indexes over 1000 page count that have hardly any fragmentation but when I run the 2nd script it shows me a list of indexes that are heavily fragmented that also have over 1000 pages.
What is the different between these two scripts?
DECLARE @DatabaseID int
SET @DatabaseID = DB_ID()
SELECT DB_NAME(@DatabaseID) AS DatabaseName,
schemas.[name] AS SchemaName,
objects.[name] AS ObjectName,
indexes.[name] AS IndexName,
objects.type_desc AS ObjectType,
indexes.type_desc AS IndexType,
dm_db_index_physical_stats.partition_number AS PartitionNumber,
dm_db_index_physical_stats.page_count AS [PageCount],
dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
SELECT SO.name AS TABLE_NAME,
SI.name AS INDEX_NAME,
SO.type_desc,
DMV.index_type_desc,
DMV.alloc_unit_type_desc,
DMV.index_depth,
DMV.index_level,
DMV.page_count,
DMV.avg_fragmentation_in_percent,
DMV.partition_number
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') as DMV
LEFT OUTER JOIN sys.objects AS SO
ON DMV.OBJECT_ID = SO.OBJECT_ID
LEFT OUTER JOIN sys.indexes AS SI
ON DMV.OBJECT_ID = SI.OBJECT_ID
AND DMV.INDEX_ID = SI.INDEX_ID
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
AND page_count > 1000
AND avg_fragmentation_in_percent > 20
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.