Liron Amitzi Oracle ACE

Oded Raz Oracle ACE Director

SQL Server 2008 – Using Query Hashes and Query Plan Hashes

Posted by Danny Ravid on Dec 5th, 2010 and filed under Main-Performance, SQL Server, SQL Server - Latest Articles. You can follow any responses to this entry through the RSS 2.0. You can leave a response or trackback to this entry

When searching for resource-intensive queries, we can use the sys.dm_exec_query_stats and sys.dm_exec_requests.

These dynamic management views already where introduced in SQL server 2005.
In SQL Server 2008 there are a couple of columns added to the sys.dm_exec_query_stats and sys.dm_exec_requests - The query_hash and the query_plan_hash column.

By using these columns we can find and tune similar queries that collectively consume significant system resources, and help determine the aggregate resource usage for similar queries and similar query execution plans.

First let’s look at the following queries:

use AdventureWorks
go
SELECT SOD.ProductID ,
sum((UnitPrice * OrderQty ) - (UnitPriceDiscount * OrderQty ) ) TotalSaleAmount
FROM Sales.SalesOrderHeader SOH
JOIN Sales.SalesOrderDetail SOD
   ON SOH.SalesOrderID = SOD.SalesOrderID
JOIN Production.Product P
   ON P.ProductID = SOD.ProductID
WHERE SOH.CustomerID = 520
AND SOD.OrderQty > 10
GROUP BY SOD.ProductID;
go

SELECT SOD.ProductID ,sum((UnitPrice * OrderQty ) - (UnitPriceDiscount * OrderQty ) ) TotalSaleAmount
FROM Sales.SalesOrderHeader SOH
JOIN Sales.SalesOrderDetail SOD
   ON SOH.SalesOrderID = SOD.SalesOrderID
JOIN Production.Product P
   ON P.ProductID = SOD.ProductID
WHERE SOH.CustomerID = 523
AND SOD.OrderQty > 10
GROUP BY SOD.ProductID;
go

These queries are only different in the constant value supplied for the CustomerID column; Now let’s look at the Query_Hash values of this 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 SOD.ProductID ,sum((UnitPrice * OrderQty ) - (UnitPriceDiscount * OrderQty ) ) TotalSaleAmount FROM Sales.SalesOrderHeader SOH JOIN Sales.SalesOrderDetail SOD    ON SOH.SalesOrderID = SOD.SalesOrderID JOIN Production.Product P    ON P.ProductID = SOD.ProductID WHERE SOH.CustomerID = 520 AND SOD.OrderQty > 10 GROUP BY SOD.ProductID;'
OR st.text = ' SELECT SOD.ProductID ,sum((UnitPrice * OrderQty ) - (UnitPriceDiscount * OrderQty ) ) TotalSaleAmount FROM Sales.SalesOrderHeader SOH JOIN Sales.SalesOrderDetail SOD    ON SOH.SalesOrderID = SOD.SalesOrderID JOIN Production.Product P    ON P.ProductID = SOD.ProductID WHERE SOH.CustomerID = 523 AND SOD.OrderQty > 10 GROUP BY SOD.ProductID;'

We can see that although these queries’ text does not exactly match, sql server 2008 identifies these queries as having the same structure and therefore have the same hash values. This gives a significant advantage to help us identifying similar queries. We can for instance use this information to find which queries would benefit from using parameterization to improve performance. For example, let’s change the logic of the queries:

use AdventureWorks
go
SELECT SOD.ProductID , sum((UnitPrice * OrderQty ) - (UnitPriceDiscount * OrderQty ) ) TotalSaleAmount
FROM Sales.SalesOrderHeader SOH
JOIN Sales.SalesOrderDetail SOD
   ON SOH.SalesOrderID = SOD.SalesOrderID
JOIN Production.Product P
   on P.ProductID = SOD.ProductID
WHERE SOH.CustomerID = 520
AND SOD.OrderQty > 10
GROUP BY SOD.ProductID;
go
SELECT  SOD.ProductID , sum((UnitPrice * OrderQty ) - (UnitPriceDiscount * OrderQty ) ) TotalSaleAmount
from Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD
   on SOH.SalesOrderID = SOD.SalesOrderID
Join Production.Product P
   on P.ProductID = SOD.ProductID
where SOH.CustomerID = 523
OR SOD.OrderQty > 10
GROUP BY SOD.ProductID;

go

Now, let’s find out the query hash for these two 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 SOD.ProductID , sum((UnitPrice * OrderQty ) - (UnitPriceDiscount * OrderQty ) ) TotalSaleAmount FROM Sales.SalesOrderHeader SOH JOIN Sales.SalesOrderDetail SOD    ON SOH.SalesOrderID = SOD.SalesOrderID JOIN Production.Product P    ON P.ProductID = SOD.ProductID WHERE SOH.CustomerID = 520 AND SOD.OrderQty > 10 GROUP BY SOD.ProductID;'
OR st.text = 'SELECT SOD.ProductID , sum((UnitPrice * OrderQty ) - (UnitPriceDiscount * OrderQty ) ) TotalSaleAmount from Sales.SalesOrderHeader SOH join Sales.SalesOrderDetail SOD    ON SOH.SalesOrderID = SOD.SalesOrderID JOIN Production.Product P    ON P.ProductID = SOD.ProductID WHERE SOH.CustomerID = 523 OR SOD.OrderQty > 10 GROUP BY SOD.ProductID;' 

We see that these 2 queries has different query hashes, so they to do not share the same logic. Now let us examine the query plan hash column, how can we use this column to find performance issues? Consider that you have similar queries like we have seen, but somehow they do not have the same query plan, this sometimes indicates a performance problem; Let’s examine the following example (from SQL 2008 BOL):

USE AdventureWorks;
GO
SET STATISTICS XML ON;

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 

If you examine the query’s plan you will see that they are different, although the query hashes are the same because we just changed the value for the ProductID column.

SELECT ST.text AS "Query Text",QS.query_plan_hash ,QP.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text (QS.sql_handle) ST
CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP
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;'; 

Below you can find a query to find similar queries with differnat query plans, in order to find the cost of each distinct query plan,we will use Xquery on the Query Plan Xml Column :

SELECT max([QueryText]) QueryText , max(SimilarQueries.query_plan.value('(//@StatementSubTreeCost)[1]','float')) QueryCost
FROM (SELECT QS1.*,ST.text AS QueryText,QP1.query_plan
      FROM sys.dm_exec_query_stats QS1
      CROSS APPLY sys.dm_exec_sql_text (QS1.sql_handle) ST
      CROSS APPLY sys.dm_exec_query_plan(QS1.plan_handle) QP1
      JOIN sys.dm_exec_query_stats QS2
         ON QS1.query_hash = QS2.query_hash
      WHERE QS1.query_plan_hash <> QS2.query_plan_hash ) as SimilarQueries
GROUP BY SimilarQueries.query_plan_hash
ORDER BY 1 

We can determine that the second query plan is more costly. it will be much nicer if we can get in the same result set the Xml Query Plan itself; this requires some TSQL juggling around, since the XML data type cannot be used in a group by or with an aggregation function.

SELECT QueryText ,QueryCost,QP.query_plan QueryPlan
FROM (select max(QueryText) QueryText ,max(SimilarQueries.query_plan.value('(//@StatementSubTreeCost)[1]','float')) QueryCost ,max(plan_handle) plan_handle
      FROM  (SELECT QS1.*,ST.text AS QueryText,QP1.query_plan
             FROM sys.dm_exec_query_stats QS1
             CROSS APPLY sys.dm_exec_sql_text (QS1.sql_handle) ST
             CROSS APPLY sys.dm_exec_query_plan(QS1.plan_handle) QP1
             JOIN sys.dm_exec_query_stats QS2
                on QS1.query_hash = QS2.query_hash
             WHERE where QS1.query_plan_hash <> QS2.query_plan_hash ) as SimilarQueries
     GROUP BY SimilarQueries.query_plan_hash) SimilarQueriesCost
CROSS APPLY sys.dm_exec_query_plan(plan_handle) QP
ORDER BY 1

In SQL 2008 Management Studio when we will click the Query Plan Xml Column you will immediately get the graphical representation of the query plan, this enables us to examine immediately the execution plan of the costly query plans.

Leave a Reply

 
Log in / Advanced NewsPaper by Gabfire Themes