Tips for DBA: Table operational/physical stats
Вашему вниманию предлагается сценарий, который для каждой таблицы текущей базы данных показывает статистику по операциям INSERT, UPDATE и DELITE. Кроме этого, вы может оценить, к чему эти операции приводят, с точки зрения роста строк данных, занимаемых страниц и фрагментации. Сценарий основан на использовании двух функций динамического управления: sys.dm_db_index_operational_stats и sys.dm_db_index_physical_stats
/* Внимание!!! Запрос исполняется несколько минут */
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)
AND fi.index_id < 2
GROUP BY t.name, fi.page_count, fi.record_count
, fi.avg_record_size_in_bytes, fi.avg_fragmentation_in_percent
ORDER BY [TableName]