索引,表增删改统计，加锁查具体情况(推荐)-优快云博客

本文提供了一段SQL脚本，用于从系统视图中获取数据库索引的操作统计数据，包括读写比例、读取次数、写入次数等关键指标，并通过连接多个表来丰富输出结果。

SELECT databases.name ,
        objects.name ,
        indexes.name ,
        读写比例 = CAST(( range_scan_count + singleton_lookup_count ) AS DECIMAL(18,
                                                              2))
        / CASE WHEN CAST(( leaf_insert_count + leaf_update_count
                           + leaf_delete_count + nonleaf_insert_count
                           + nonleaf_update_count + nonleaf_delete_count ) AS DECIMAL(18,
                                                              2)) = 0 THEN 1
               ELSE CAST(( leaf_insert_count + leaf_update_count
                           + leaf_delete_count + nonleaf_insert_count
                           + nonleaf_update_count + nonleaf_delete_count ) AS DECIMAL(18,
                                                              2))
          END ,
        reads = range_scan_count + singleton_lookup_count ,
        writes = leaf_insert_count + leaf_update_count + leaf_delete_count
        + nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count ,
        leaf_insert_count ,
        leaf_update_count ,
        leaf_delete_count ,
        nonleaf_insert_count ,
        nonleaf_update_count ,
        nonleaf_delete_count ,
        range_scan_count ,
        singleton_lookup_count ,
        row_count ,
        page_lock_count ,
        partition_stats.row_count ,
        stats.*
FROM   sys.dm_db_index_operational_stats(7, NULL, NULL, NULL) stats
        LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id
        LEFT JOIN sys.databases databases ON databases.database_id = stats.database_id
        LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id
                                          AND stats.object_id = indexes.object_id
        LEFT   JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id
                                                              AND indexes.index_id = partition_stats.index_id
WHERE objects.name NOT LIKE 'sys%'
ORDER BY row_count DESC