쿼리 및 쿼리 계획 해시를 사용하여 비슷한 쿼리 검색 및 튜닝
리소스 사용량이 많은 쿼리를 검색할 때는 전체적으로 상당햔 양의 시스템 리소스를 소비하는 비슷한 쿼리를 찾고 튜닝하는 방법을 고려해야 합니다. 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 문과 Showplan XML입니다. 이 두 가지는 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에 대한 카디널리티 예측이 높은 경우 쿼리 최적화 프로그램은 쿼리 계획에서 Index Scan 연산자를 사용할 수 있습니다. ProductID = 1에 대한 카디널리티 예측이 낮은 경우 쿼리 최적화 프로그램은 쿼리 계획에서 Index Seek 연산자를 사용할 수 있습니다.
해시 값에 대한 비고유성
비슷하지 않은 쿼리 및 쿼리 계획이 동일한 해시 값을 갖는 해시 충돌이 발생할 수 있습니다. 해시 충돌은 일어날 가능성이 매우 작지만 쿼리 해시 및 쿼리 계획 해시의 고유성에 의존하는 응용 프로그램에서는 해시 값 중복으로 인해 오류가 발생할 수 있습니다. 예를 들어 쿼리 해시 및 쿼리 계획 해시는 기본 키로 사용되거나 고유 열에서 사용될 수 없습니다.
쿼리의 누적 비용 찾기
다음 예에서는 평균 CPU 시간에 따라 상위 5개의 쿼리에 대한 정보를 반환합니다. 이 예에서는 논리적으로 동일한 쿼리를 누적 리소스 소비량에 따라 그룹화할 수 있도록 쿼리 해시에 따라 쿼리를 집계합니다.
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
다음 예에서는 평균 CPU 시간에 따라 상위 5개의 쿼리 계획에 대한 정보를 반환합니다. 이 예에서는 쿼리 계획 해시가 동일한 쿼리를 누적 리소스 소비량에 따라 그룹화할 수 있도록 쿼리 계획 해시에 따라 쿼리를 집계합니다.
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의 쿼리 계획 해시가 우수한 성능이 확인된 기준 쿼리 계획 해시와 다를 경우 현재 장기 실행 쿼리의 실행을 중지하도록 결정할 수 있습니다.
비슷한 쿼리에 매개 변수를 사용하여 캐시된 계획 재사용 성능 향상
일련의 쿼리에서 쿼리 해시 및 쿼리 계획 해시가 동일한 경우 하나의 매개 변수가 있는 쿼리를 만들어서 성능을 향상시킬 수 있습니다. 리터럴 값으로 여러 쿼리를 호출하는 대신 매개 변수를 사용하여 하나의 쿼리를 호출하면 캐시된 쿼리 실행 계획을 다시 사용할 수 있습니다. 캐시된 쿼리 계획의 재사용으로 얻는 이점에 대한 자세한 내용은 실행 계획 캐싱 및 다시 사용을 참조하십시오.
응용 프로그램을 수정할 수 없는 경우에는 템플릿 계획 지침에서 강제 매개 변수화를 사용하여 비슷한 결과를 얻을 수 있습니다. 자세한 내용은 계획 지침을 사용하여 쿼리 매개 변수화 동작 지정을 참조하십시오.