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
Remarks:
sys.dm_db_index_operational_stats
(How the Counters in the Metadata Cache Are Reset
The data returned by sys.dm_db_index_operational_stats exists only as
long as the metadata cache object that represents the heap or index is
available. This data is neither persistent nor transactionally
consistent. This means you cannot use these counters to determine
whether an index has been used or not, or when the index was last
used. For information about this, see sys.dm_db_index_usage_stats.
The values for each column are set to zero whenever the metadata for
the heap or index is brought into the metadata cache and statistics
are accumulated until the cache object is removed from the metadata
cache. Therefore, an active heap or index will likely always have its
metadata in the cache, and the cumulative counts may reflect activity
since the instance of SQL Server was last started. The metadata for a
less active heap or index will move in and out of the cache as it is
used. As a result, it may or may not have values available. Dropping
an index will cause the corresponding statistics to be removed from
memory and no longer be reported by the function. Other DDL operations
against the index may cause the value of the statistics to be reset to
zero.)
sys.dm_db_index_usage_stats
, which will reflect usage since the instance was last started. – Dan Guzman Feb 4 '20 at 13:42