Uso de DMV para determinar las estadísticas de uso y el rendimiento de las vistas
Este artículo trata la metodología y los scripts usados para obtener información sobre el rendimiento de las consultas que utilizan vistas. El objetivo de estos scripts es proporcionar los indicadores de uso y el rendimiento de diversas vistas que se encuentran en una base de datos.
sys.dm_exec_query_optimizer_info
La DMV sys.dm_exec_query_optimizer_info expone estadísticas sobre las optimizaciones que ha realizado el optimizador de consultas de SQL Server. Estos valores son acumulativos y empiezan a registrarse cuando se inicia SQL Server. Para obtener más información sobre el optimizador de consultas, vea la Guía de arquitectura de procesamiento de consultas.
La siguiente expresión de tabla común (CTE) usa esta DMV para proporcionar información sobre la carga de trabajo, como el porcentaje de consultas que hacen referencia a una vista. Los resultados que devuelve esta consulta no indican un problema de rendimiento por sí mismos, pero puede exponer los problemas subyacentes cuando se combina con quejas de usuarios relacionadas con el bajo rendimiento de las consultas.
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
Combine los resultados de esta consulta con los de la vista del sistema sys.views para identificar las estadísticas de consultas, el texto de las consultas y el plan de ejecución almacenado en caché.
sys.views
La siguiente CTE proporciona información sobre el número de ejecuciones, el tiempo de ejecución total y las páginas leídas de la memoria. Los resultados se pueden usar para identificar las consultas que pueden ser candidatas para la optimización.
Nota:
Los resultados de esta consulta pueden variar según la versión de 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
La consulta final proporciona información sobre las vistas sin usar mediante la DMV sys.dmv_exec_cached_plans. Sin embargo, la caché del plan de ejecución es dinámica y los resultados pueden variar. Por lo tanto, use esta consulta con el paso del tiempo para determinar si realmente se utiliza una vista o no.
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