Sample Header Ad - 728x90

Operational stats mention the same table twice, what does this mean?

0 votes
1 answer
161 views
While investigating why my application mentions deadlocking, I fell upon this question , whose answer mentions an SQL query, showing operational logs. However, the results of that query contain the same tablename twice, for some tables. How should I interpret that result? Hereby the mentioned SQL query: SELECT t.name AS [TableName], fi.page_count AS [Pages], fi.record_count AS [Rows], CAST(fi.avg_record_size_in_bytes AS int) AS [AverageRecordBytes], CAST(fi.avg_fragmentation_in_percent AS int) AS [AverageFragmentationPercent], SUM(iop.leaf_insert_count) AS [Inserts], SUM(iop.leaf_delete_count) AS [Deletes], SUM(iop.leaf_update_count) AS [Updates], SUM(iop.row_lock_count) AS [RowLocks], SUM(iop.page_lock_count) AS [PageLocks] FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) AS iop JOIN sys.indexes AS i ON iop.index_id = i.index_id AND iop.object_id = i.object_id JOIN sys.tables AS t ON i.object_id = t.object_id AND i.type_desc IN ('CLUSTERED', 'HEAP') JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS fi ON fi.object_id=CAST(t.object_id AS int) AND fi.index_id=CAST(i.index_id AS int) GROUP BY t.name, fi.page_count, fi.record_count, fi.avg_record_size_in_bytes, fi.avg_fragmentation_in_percent ORDER BY [RowLocks] desc
Asked by Dominique (609 rep)
Dec 5, 2022, 11:07 AM
Last activity: Dec 5, 2022, 01:27 PM