Buscar y optimizar consultas similares utilizando hash del plan de consulta y de consulta
Al buscar consultas que usan los recursos de forma intensiva, debería considerar cómo buscar y optimizar las consultas similares que en conjunto consumen los recursos del sistema significativos. Las vistas de administración dinámica sys.dm_exec_query_stats y sys.dm_exec_requests proporcionan los valores hash de consulta y de plan de consulta que puede utilizar para ayudar a determinar el uso de recursos agregados para consultas similares y planes de ejecución de consulta similares.
Este tema define el hash de consulta y el hash de plan de consulta, proporciona ejemplos de cómo usar los valores de hash para buscar el costo acumulado de las consultas y planes de ejecución similares, y proporciona maneras de mejorar su rendimiento.
Descripción del hash de consulta y del hash de plan de consulta
El hash de consulta es un valor hash binario calculado en la consulta y se usa para identificar las consultas con una lógica similar. El optimizador de consultas calcula el hash de consulta durante la compilación de la consulta. Las consultas que sólo difieren en valores literales tienen el mismo hash de consulta. Por ejemplo, las dos consultas siguientes tienen el mismo hash porque sólo difieren en los valores literales asignados a FirstName y LastName.
USE AdventureWorks;
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = 'Amanda' AND C.LastName = 'Allen';
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = 'Logan' AND C.LastName = 'Jones';
GO
--Show the query hash for both queries.
SELECT st.text AS "Query Text", qs.query_hash AS "Query Hash"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
WHERE st.text = 'SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = ''Amanda'' AND C.LastName = ''Allen'';
' OR st.text = 'SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = ''Logan'' AND C.LastName = ''Jones'';
';
GO
Las dos consultas siguientes tienen hash de consulta diferentes porque sus diferencias son lógicas (AND frente a OR) y no se limitan únicamente a los literales.
USE AdventureWorks;
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = 'Amanda' AND C.LastName = 'Allen';
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = 'Logan' OR C.LastName = 'Jones';
GO
--Show the query hash for both queries.
SELECT st.text AS "Query Text", qs.query_hash AS "Query Hash"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
WHERE st.text = 'SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = ''Amanda'' AND C.LastName = ''Allen'';
' OR st.text = 'SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = ''Logan'' OR C.LastName = ''Jones'';
';
GO
El hash de plan de consulta es un valor hash binario calculado en el plan de ejecución de consulta y se usa para identificar los planes de ejecución de consulta similares. El optimizador de consultas calcula el hash de plan de consulta durante la compilación de la consulta, utilizando valores del plan de ejecución como los operadores lógicos y físicos, y un subconjunto de atributos de operadores importantes. Los planes de ejecución de consulta que tengan la misma estructura de árbol de operadores lógicos y físicos, así como valores de atributos idénticos para el subconjunto de atributos de operadores importantes, tendrán el mismo hash de plan de consulta.
Cuando se ejecutan consultas con hash de consulta idénticos con datos diferentes, las diferencias en la cardinalidad de los resultados pueden hacer que el optimizador de consultas elija planes de ejecución de consulta distintos, lo que resulta en hash de plan de consulta diferentes.
El ejemplo siguiente muestra cómo dos consultas similares pueden tener el mismo hash de consulta, pero podrían no tener el mismo plan de ejecución de consulta. Hay dos maneras de ver los valores hash: la instrucción SELECT final y Showplan XML, en los que se muestran en el elemento StmtSimple como valores de atributo para QueryHash y QueryPlanHash.
USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 1;
GO
SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 3;
GO
SET STATISTICS XML OFF;
GO
--Show the query_hash and query plan hash
SELECT ST.text AS "Query Text", QS.query_hash AS "Query Hash",
QS.query_plan_hash AS "Query Plan Hash"
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text (QS.sql_handle) ST
WHERE ST.text = 'SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 1;
' OR ST.text = 'SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 3;
';
GO
Si los cálculos de la cardinalidad para ProductID = 3 son altos, el optimizador de consultas podría utilizar el operador index scan en el plan de consulta. Si los cálculos de cardinalidad para ProductID = 1 son bajos, el optimizador de consultas podría utilizar el operador index seek.
No unicidad para valores hash
Es posible que haya colisiones de valores hash en las que consultas y planes de consulta distintos tengan el mismo valor hash. Aunque la probabilidad de que haya colisiones de valores hash es muy pequeña, las aplicaciones que dependen de la unicidad de hash de consulta y del hash de plan de consulta pueden tener errores debido a los valores hash duplicados. Por ejemplo, el hash de consulta y el hash de plan de consulta no se deberían utilizar como clave principal o en una columna única.
Buscar el costo acumulativo de las consultas
En el siguiente ejemplo se devuelve información acerca de las cinco consultas según el mayor promedio de tiempo de CPU. Este ejemplo agrega las consultas según su hash de consulta para que las consultas lógicamente equivalentes se agrupen según su consumo acumulado de los recursos.
USE AdventureWorks;
GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO
En el ejemplo siguiente se devuelve información acerca de los cinco primeros planes de consulta según el promedio de tiempo de CPU. Este ejemplo agrega las consultas según su hash de plan de consulta para que las que tengan el mismo hash de plan de consulta se agrupen según su consumo acumulado de recursos.
USE AdventureWorks;
GO
SELECT TOP 5 query_plan_hash AS "Query Plan Hash",
SUM(total_worker_time)/SUM(execution_count) AS "Avg CPU Time",
MIN(CAST(query_plan as varchar(max))) AS "ShowPlan XML"
FROM sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_query_plan(QS.plan_handle)
GROUP BY query_plan_hash
ORDER BY 2 DESC;
GO
Usar el hash de consulta y el hash de plan de consulta para mejorar el rendimiento de las consultas
Seguir e investigar los cambios del plan de ejecución
Se pueden producir degradaciones o mejoras en el rendimiento cuando se vuelve a compilar una consulta y el optimizador de consultas genera un plan de ejecución de consulta diferente. Con el hash de plan de consulta, puede capturar, almacenar y comparar los planes de ejecución de consulta a lo largo del tiempo. Saber qué planes de ejecución han cambiado puede ayudarle a diagnosticar el efecto en el rendimiento de los cambios en los datos y la configuración.
Por ejemplo, después de cambiar la configuración del sistema, puede comparar los valores hash de plan de consulta de las consultas esenciales con sus valores hash de plan de consulta originales. Las diferencias en los valores pueden indicarle si el cambio de la configuración del sistema produjo planes de ejecución de consulta actualizados para las consultas importantes. También podría decidir detener la ejecución de una consulta de larga duración si su hash de plan de consulta en sys.dm_exec_requests difiere de su hash de plan de consulta de línea base, que se sabe que tiene un buen rendimiento.
Parametrizar consultas similares para mejorar la reutilización del plan en la caché
Si un conjunto de consultas tiene el mismo hash de consulta y hash de plan de consulta, podría mejorar el rendimiento creando una consulta parametrizada. Llamar a una consulta con parámetros en lugar de a varias consultas con valores literales permite reutilizar el plan de ejecución de consulta almacenado en la memoria caché. Para obtener más información sobre las ventajas de la reutilización de los planes de consulta almacenados en la memoria caché, vea Almacenar en caché y volver a utilizar un plan de ejecución.
Si no puede modificar la aplicación, puede utilizar las guías de plan de la plantilla con parametrización forzada para lograr un resultado similar. Para obtener más información, vea Especificar el comportamiento de parametrización de consultas por medio de guías de plan.