SQL Server 2014无VIEW SERVER STATE权限如何查询高频访问表

优先方案：申请数据库级VIEW DATABASE STATE权限 #

sys.dm_db_index_usage_stats 针对单个数据库查询时，仅需要当前数据库的 VIEW DATABASE STATE 权限，不需要服务器级的VIEW SERVER STATE权限。该权限仅开放单个数据库的状态查询权限，风险极低，绝大多数托管方都会同意授予。
托管方执行授权语句：

USE 你的业务数据库名
GO
GRANT VIEW DATABASE STATE TO [你的登录用户名];

授权后你可以用修改后的语句查询，无需服务器级权限：

USE 你的业务数据库名
GO
SELECT t.NAME AS tname,
    SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS accesses
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id
GROUP BY t.name
ORDER BY accesses DESC

替代方案1：使用查询存储做近似统计 #

如果无法申请到VIEW DATABASE STATE权限，且你的数据库开启了查询存储（SQL Server 2016及以上版本默认开启），可以用查询存储的数据统计高频访问表，该方案仅需要普通的数据库查询权限即可使用。
查询语句示例：

USE 你的业务数据库名
GO
SELECT 
    t.name AS tname,
    SUM(rs.count_executions) AS total_access_count
FROM sys.query_store_query qsq
INNER JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
INNER JOIN sys.query_store_runtime_stats rs ON qsp.plan_id = rs.plan_id
INNER JOIN sys.tables t ON CHARINDEX(t.name, qsq.query_sql_text) > 0
WHERE t.name != 'sysdiagrams' -- 过滤系统表
GROUP BY t.name
ORDER BY total_access_count DESC

注意：该方案统计结果存在少量误差，若表名出现在SQL注释、字符串中会出现误判，可作为近似参考使用。

替代方案2：仅统计增删改高频表的触发器方案 #

如果仅需要统计写入操作（增/删/改）的高频表，不需要统计查询操作，可以通过自建触发器实现统计，该方案无需任何额外高权限，仅需要当前库的建表、建触发器权限即可。

1. 首先创建统计表：

CREATE TABLE table_write_stats (
    table_name sysname PRIMARY KEY,
    write_count BIGINT NOT NULL DEFAULT 0
)
-- 初始化所有业务表的统计记录
INSERT INTO table_write_stats (table_name)
SELECT name FROM sys.tables WHERE name != 'table_write_stats'

2. 给每个业务表创建触发器，示例：

CREATE TRIGGER trg_表名_write_count ON 你的业务表名
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    UPDATE table_write_stats 
    SET write_count = write_count + 1
    WHERE table_name = '你的业务表名'
END

3. 统计时直接查询统计表即可：

SELECT table_name AS tname, write_count AS accesses
FROM table_write_stats
ORDER BY write_count DESC

注意：该方案会对表的写入性能有轻微损耗，不建议在高并发写入的核心表上使用。

内容的提问来源于stack exchange，提问作者Tyler Haag