Analyzing Query Performance - SQL Server Query Performance Tuning - page 88

The costly queries identified by this approach are a better indication of load than the costly queries with single
execution identified by a session. For example, a query that requires 50 reads might be executed 1,000 times. The
query itself may be considered cheap enough, but the total number of reads performed by the query turns out to be
50,000 (=50 x 1,000), which cannot be considered cheap. Optimizing this query to reduce the reads by even 10 for
individual execution reduces the total number of reads by 10,000 (=10 x 1,000), which can be more beneficial than
optimizing a single query with 5,000 reads.
The problem with this approach is that most queries will have a varying set of criteria in the WHERE clause or
that procedure calls will have different values passed in. That makes the simple grouping by the query or procedure
with parameters just impossible. You can take care of this problem with a number of approaches. Because you
have Extended Events, you can actually put it to work for you. For example, the rpc_completed event captures the
procedure name as a field. You can simply group on that field. For batches, you can add the query_hash field and
then group on that. Another way is to clean the data, removing the parameter values, as outlined on the Microsoft
Developers Network at http://bit.ly/1e1I38f . Although it was written originally for SQL Server 2005, the concepts
will work fine with SQL Server 2014.
Getting the same information out of the sys.dm_exec_query_stats view simply requires a query against the DMV.
SELECT s.totalexecutioncount,
t.text,
s.TotalExecutionCount,
s.TotalElapsedTime,
s.TotalLogicalReads,
s.TotalPhysicalReads
FROM (SELECT deqs.plan_handle,
SUM(deqs.execution_count) AS TotalExecutionCount,
SUM(deqs.total_elapsed_time) AS TotalElapsedTime,
SUM(deqs.total_logical_reads) AS TotalLogicalReads,
SUM(deqs.total_physical_reads) AS TotalPhysicalReads
FROM sys.dm_exec_query_stats AS deqs
GROUP BY deqs.plan_handle
) AS s
CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
ORDER BY s.TotalLogicalReads DESC ;
Another way to take advantage of the data available from the execution DMOs is to use query_hash and
query_plan_hash as aggregation mechanisms. While a given stored procedure or parameterized query might have
different values passed to it, changing query_hash and query_plan_hash for these will be identical (most of the time).
This means you can aggregate against the hash values to identify common plans or common query patterns that you
wouldn't be able to see otherwise. The following is just a slight modification from the previous query:
SELECT s.TotalExecutionCount,
t.text,
s.TotalExecutionCount,
s.TotalElapsedTime,
s.TotalLogicalReads,
s.TotalPhysicalReads
FROM (SELECT deqs.query_plan_hash,
SUM(deqs.execution_count) AS TotalExecutionCount,
SUM(deqs.total_elapsed_time) AS TotalElapsedTime,
SUM(deqs.total_logical_reads) AS TotalLogicalReads,
SUM(deqs.total_physical_reads) AS TotalPhysicalReads