Поиск и настройка сходных запросов с помощью хэширования запросов и планов запросов
При поиске запросов с большими потребностями в ресурсах следует принимать во внимание поиск и настройку сходных запросов, совокупно использующих значительную часть ресурсов системы. Динамические административные представления sys.dm_exec_query_stats и sys.dm_exec_requests предоставляют значения хэширования для запросов и планов запросов, которые можно использовать для выявления агрегированного использования ресурсов сходными запросами и сходными планами выполнения запросов.
В данном разделе определяется, что такое хэширование запросов и хэширование планов запросов, приводятся примеры использования значений хэша для выяснения совокупной стоимости сходных запросов и планов выполнения и предлагаются способы повышения производительности для сходных запросов и планов выполнения.
Основные сведения о хэшировании запросов и планов запросов
Хэш-значение для запроса — это двоичное хэш-значение, вычисленное для данного запроса и используемое для выявления запросов со сходной логикой. Оптимизатор запросов вычисляет хэш-значение во время компиляции запроса. Запросы, которые отличаются только литеральными значениями, будут иметь одинаковые хэш-значения. Например, следующие два запроса будут иметь одинаковые хэш-значения, поскольку они отличаются только литеральными значениями, присваиваемыми объектам FirstName и LastName.
USE AdventureWorks2008R2;
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = 'Amanda' AND P.LastName = 'Allen';
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = 'Logan' AND P.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, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = ''Amanda'' AND P.LastName = ''Allen'';
' OR st.text = 'SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = ''Logan'' AND P.LastName = ''Jones'';
';
GO
Следующие два запроса будут иметь различные хэш-значения, поскольку они отличаются логикой (в одном запросе используется AND, а в другом OR), а не только литералами.
USE AdventureWorks2008R2;
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = 'Amanda' AND P.LastName = 'Allen';
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = 'Logan' OR P.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, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = ''Amanda'' AND P.LastName = ''Allen'';
' OR st.text = 'SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City
FROM Person.Person AS P
JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
WHERE P.FirstName = ''Logan'' OR P.LastName = ''Jones'';
';
GO
Хэш-значение плана запроса — это двоичное хэш-значение, вычисленное для данного плана выполнения запроса и используемое для выявления планов выполнения запроса со сходной логикой. Оптимизатор запросов вычисляет хэш-значение плана запроса во время компиляции запроса на основе таких параметров плана выполнения запроса, как логические и физические операторы, а также на основе подмножества важных атрибутов операторов. Планы выполнения запроса, имеющие одно и то же дерево физических и логических операторов, а также одинаковые значения атрибутов для подмножества важных атрибутов операторов, будут иметь одинаковые хэш-значения плана запроса.
Когда запросы с одинаковыми хэш-значениями выполняются на различных данных, оптимизатор запросов может выбрать для этих запросов разные планы выполнения из-за разницы в количестве элементов в результатах запроса, поэтому у этих запросов будут разные хэш-значения планов запроса.
В следующем примере показаны два сходных запроса, у которых могут быть одинаковые хэш-значениями запроса, но возможны разные планы выполнения запроса. Есть два способа просмотра хэш-значений: результирующая инструкция SELECT и XML-схема инструкции Showplan, где они перечислены в элементе StmtSimple как значения атрибутов QueryHash и QueryPlanHash.
USE AdventureWorks2008R2;
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
Если оценки количества элементов для запроса ProductID = 3 высоки, оптимизатор запроса может применить в плане запроса оператор просмотра индекса. Если оценки количества элементов для запроса ProductID = 1 невысоки, оптимизатор запроса может применить в плане запроса оператор поиска индекса.
Отсутствие уникальности у хэш-значений
Возможны конфликты хэш-значений, то есть ситуации, когда непохожие запросы и планы запросов имеют одно и то же хэш-значение. Хотя вероятность конфликта хэш-значений очень низка, приложения, которые полагаются на уникальность хэш-значения запросов и планов запросов, могут работать с ошибками из-за повторяющихся хэш-значений. В частности, хэш-значения запросов и планов запросов не следует использовать как первичный ключ или в столбце уникальных значений.
Вычисление совокупной стоимости запросов
В следующем примере возвращаются сведения о пяти первых запросах по среднему времени загрузки ЦП. В примере производится статистическая обработка запросов по хэш-значению, так что логически эквивалентные запросы группируются по совокупному потреблению ресурсов.
USE AdventureWorks2008R2;
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
В следующем примере возвращаются сведения о пяти первых планах запроса по среднему времени загрузки ЦП. В примере производится статистическая обработка запросов по хэш-значению плана запроса, так что запросы с одним и тем же хэш-значением плана запроса группируются по совокупному потреблению ресурсов.
USE AdventureWorks2008R2;
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
Использование хэш-значений запросов и планов запросов для повышения производительности запросов
Отслеживание и рассмотрение изменений в планах выполнения
Когда запрос перекомпилируется и оптимизатор запросов создает для него новый план выполнения, производительность запроса может как возрасти, так и упасть. С помощью хэш-значения плана запроса можно фиксировать, хранить и сравнивать планы выполнения запросов в различные моменты времени. Знание того, какие планы выполнения изменились, поможет диагностировать влияние изменений данных и конфигурации на производительность.
Например, после изменения конфигурации системы можно сравнить хэш-значения планов запроса для ответственных запросов с их же хэш-значениями до изменений системы. По различиям в хэш-значениях планов запросов можно понять, изменились ли планы выполнения важных запросов в результате изменения конфигурации системы. Можно также принять решение об остановке выполняющегося длительного запроса, если хэш-значение его плана в представлении sys.dm_exec_requests отличается от хэш-значения первоначального плана запроса, о котором известно, что у него была высокая производительность.
Параметризация сходных запросов для повышения повторного использования кэшированных планов
Если у нескольких запросов совпадают хэш-значения и хэш-значения планов запросов, можно повысить производительность, создав один параметризованный запрос. Вызов одного запроса с параметрами вместо вызова нескольких запросов с различными литеральными значениями позволяет повторно использовать кэшированный план выполнения запроса. Дополнительные сведения о преимуществах повторного использования кэшированных планов запроса см. в разделе Кэширование и повторное использование плана выполнения.
Если изменять приложение нельзя, того же результата можно добиться с помощью шаблонов структур планов с принудительной параметризацией. Дополнительные сведения см. в разделе Указание механизма параметризации запросов с помощью структур плана.