Вашему вниманию предлагается сценарий, который для каждой таблицы текущей базы данных показывает статистику по операциям INSERT, UPDATE и DELITE. Кроме этого, вы может оценить, к чему эти операции приводят, с точки зрения роста строк данных, занимаемых страниц и фрагментации. Сценарий основан на использовании двух функций динамического управления: sys.dm_db_index_operational_stats и sys.dm_db_index_physical_stats. Сценарий выполняется несколько минут.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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] |
