使用 DMV 来确定视图的使用情况统计信息和性能
本文介绍了一些方法和脚本，用于获取使用视图的查询的性能相关信息 。 这些脚本的目的是提供在数据库中发现的各种视图的使用指标和性能指标。
sys.dm_exec_query_optimizer_info
DMV sys.dm_exec_query_optimizer_info 公开有关 SQL Server 查询优化器所执行的优化统计信息。 这些值是累积的，并且在 SQL Server 启动时开始记录。 有关查询优化器的详细信息，请参阅查询处理体系结构指南。
下面的 common_table_expression (CTE) 使用此 DMV 来提供有关工作负荷的信息，例如引用视图的查询的百分比。 此查询返回的结果并不表明其本身存在性能问题，但可以在与用户的缓慢执行查询投诉结合使用时公开基本问题。
WITH CTE_QO AS
(
SELECT
occurrence
FROM
sys.dm_exec_query_optimizer_info
WHERE
([counter] = 'optimizations')
),
QOInfo AS
(
SELECT
[counter]
,[%] = CAST((occurrence * 100.00)/(SELECT occurrence FROM CTE_QO) AS DECIMAL(5, 2))
FROM
sys.dm_exec_query_optimizer_info
WHERE
[counter] IN ('optimizations'
,'trivial plan'
,'no plan'
,'search 0'
,'search 1'
,'search 2'
,'timeout'
,'memory limit exceeded'
,'insert stmt'
,'delete stmt'
,'update stmt'
,'merge stmt'
,'contains subquery'
,'view reference'
,'remote query'
,'dynamic cursor request'
,'fast forward cursor request'
)
)
SELECT
[optimizations] AS [optimizations %]
,[trivial plan] AS [trivial plan %]
,[no plan] AS [no plan %]
,[search 0] AS [search 0 %]
,[search 1] AS [search 1 %]
,[search 2] AS [search 2 %]
,[timeout] AS [timeout %]
,[memory limit exceeded] AS [memory limit exceeded %]
,[insert stmt] AS [insert stmt %]
,[delete stmt] AS [delete stmt]
,[update stmt] AS [update stmt]
,[merge stmt] AS [merge stmt]
,[contains subquery] AS [contains subquery %]
,[view reference] AS [view reference %]
,[remote query] AS [remote query %]
,[dynamic cursor request] AS [dynamic cursor request %]
,[fast forward cursor request] AS [fast forward cursor request %]
FROM
QOInfo
PIVOT (MAX([%]) FOR [counter]
IN ([optimizations]
,[trivial plan]
,[no plan]
,[search 0]
,[search 1]
,[search 2]
,[timeout]
,[memory limit exceeded]
,[insert stmt]
,[delete stmt]
,[update stmt]
,[merge stmt]
,[contains subquery]
,[view reference]
,[remote query]
,[dynamic cursor request]
,[fast forward cursor request])) AS p;
GO
将此查询结果与系统视图 sys.views 的结果相结合以标识查询统计信息、查询文本和缓存执行计划。
sys.views
以下 CTE 提供有关执行数量、总运行时间和从内存读取页面的信息。 结果可以用于标识可能适合进行优化的查询。
注意
此查询结果因 SQL Server 版本而异。
WITH CTE_VW_STATS AS
(
SELECT
SCHEMA_NAME(vw.schema_id) AS schemaname
,vw.name AS viewname
,vw.object_id AS viewid
FROM
sys.views AS vw
WHERE
(vw.is_ms_shipped = 0)
INTERSECT
SELECT
SCHEMA_NAME(o.schema_id) AS schemaname
,o.Name AS name
,st.objectid AS viewid
FROM
sys.dm_exec_cached_plans cp
CROSS APPLY
sys.dm_exec_sql_text(cp.plan_handle) st
INNER JOIN
sys.objects o ON st.[objectid] = o.[object_id]
WHERE
st.dbid = DB_ID()
)
SELECT
vw.schemaname
,vw.viewname
,vw.viewid
,DB_NAME(t.databaseid) AS databasename
,t.databaseid
,t.*
FROM
CTE_VW_STATS AS vw
CROSS APPLY
(
SELECT
st.dbid AS databaseid
,st.text
,qp.query_plan
,qs.*
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.plan_handle) AS st
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE
(CHARINDEX(vw.viewname, st.text, 1) > 0)
AND (st.dbid = DB_ID())
) AS t;
GO
sys.dmv_exec_cached_plans
最终查询通过使用 DMV sys.dmv_exec_cached_plans 提供有关未使用视图的信息。 但是，执行计划缓存是动态的，并且结果可能会不同。 在这种情况下，随着时间的推移使用此查询来确定视图实际上是否正在使用。
SELECT
SCHEMA_NAME(vw.schema_id) AS schemaname
,vw.name AS name
,vw.object_id AS viewid
FROM
sys.views AS vw
WHERE
(vw.is_ms_shipped = 0)
EXCEPT
SELECT
SCHEMA_NAME(o.schema_id) AS schemaname
,o.name AS name
,st.objectid AS viewid
FROM
sys.dm_exec_cached_plans cp
CROSS APPLY
sys.dm_exec_sql_text(cp.plan_handle) st
INNER JOIN
sys.objects o ON st.[objectid] = o.[object_id]
WHERE
st.dbid = DB_ID();
GO