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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy