July 14, 2009 at 7:29 am
Noticed this again today.
After rebuilding an index, I went to look at fragmentation by right clicking on the index under the table selecting properties and then the fragmentation page. I know I could and should have selected from the dmv but hey its a small enough table, right!
The table was relatively small 500,000 rows. Anyway it was taking forever so I did the old sp_who2 and then dbcc inputbuffer and soon I realised why it is so slow:
declare @database_id int
select @database_id = db_id()
SELECT i.name AS [Index_Name],
CAST(i.index_id AS int) AS [Index_ID],
fi.index_depth AS [Depth],
fi.page_count AS [Pages],
fi.record_count AS [Rows],
fi.min_record_size_in_bytes AS [MinimumRecordSize],
fi.max_record_size_in_bytes AS [MaximumRecordSize],
fi.avg_record_size_in_bytes AS [AverageRecordSize],
fi.forwarded_record_count AS [ForwardedRecords],
fi.avg_page_space_used_in_percent AS [AveragePageDensity],
fi.index_type_desc AS [IndexType],
fi.partition_number AS [PartitionNumber],
fi.ghost_record_count AS [GhostRows],
fi.version_ghost_record_count AS [VersionGhostRows],
fi.avg_fragmentation_in_percent AS [AverageFragmentation]
FROM sys.tables AS tbl INNER JOIN sys.indexes AS i
ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.dm_db_index_physical_stats(@database_id, NULL, NULL, NULL, 'SAMPLED') AS fi
ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
WHERE (i.name=N'pk_my_table')and((tbl.name=N'my_table' and SCHEMA_NAME(tbl.schema_id)=N'dbo'))
ORDER BY [Index_Name] ASC
The code sent by SMS was passing 'SAMPLED' to the sys.dm_db_index_physical_stats but it also had NULL for the table name. No wonder my IO count was going well into the millions, when I killed it!
When I ran the code above but with the table name in the sys.dm_db_index_physical_stats request it returned in seconds.
Interesting.
Nigel Moore
======================
July 14, 2009 at 8:09 am
Are you running with the latest service pack? There was an issue with database contexts in maintenance plans prior to SP2. This might also be related.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply