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

1 Answer 1


Of course it may return the same TableName twice (or more), because the query you're using is looking at system views that return information about indexes, and one table can have multiple indexes on it.


Returns current lower-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.


Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition.

  • I have replaced SELECT t.name by SELECT i.name, t.name and GROUP BY t.name by GROUP BY i.name, t.name and I still have double entries (for the same index name and table name). Is it still normal?
    – Dominique
    Dec 5, 2022 at 13:47
  • @Dominique notice I highlighted this "one row is returned for each level of the B-tree in each partition" in the second paragraph I quoted. Your query not only returns one row per index per table, but also per level of the B-Tree in each of those indexes. Not sure why you'd want to group though, that defeats the purpose of this data.
    – J.D.
    Dec 5, 2022 at 13:54
  • Ok, thanks. I was looking for a way to see the usage and locks of some tables in order to solve a deadlock situation, but I believe this SQL query can't be used for that, right?
    – Dominique
    Dec 5, 2022 at 14:31
  • @Dominique No I don't believe so. I saw your other question about deadlocks too. The easiest way to understand what's going on is by profiling the database with a tool, either sp_WhoIsActive (for a quick overview of what's blocking / being blocked) or the SQL Server Profiler itself (more complex). But that means you'd need access to your customer's SQL Server instance or they'd need to provide repeatable steps to recreate the deadlock / blocking on your end.
    – J.D.
    Dec 5, 2022 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.