Microsoft – SQL Server – Regularizing \ Normalizing Query Plan Queries

Background

For forever, I have used Microsoft’s SQL Server Management Studio Reports to identify TOP N Queries.

Actually, since Microsoft SQL Server 2005.

And, in the last few years I have really taken a liking to the ClearTrace tool; it quickly normalizes SQL Server Profiler Trace files.

With the growing popularity of ORM Tools such as Jboss’s Hibernate & NHibernate.Net and Microsoft’s Entity Framework, it seems Stored Procedures usage is weening a bit and being displaced by so called Prepared Statements.

SQL Server 2008+

If you find yourself needing a quick way to coalesce numerous entries in your Query Plan I will suggest you try out this query snippet shared by Microsoft’s Bart Duncan.

Query Fingerprints and Plan Fingerprints (The Best SQL 2008 Feature That You’ve Never Heard Of)

http://blogs.msdn.com/b/bartd/archive/2008/09/03/query-fingerprints-and-plan-fingerprints_3a00_-the-best-new-sql-2008-feature-you_2700_ve-never-heard-of.aspx

SELECT TOP 100
       query_hash
     , query_plan_hash
     , cached_plan_object_count
     , execution_count
     , total_cpu_time_ms
     , total_elapsed_time_ms
     , total_logical_reads
     , total_logical_writes
     , total_physical_reads
     , sample_database_name
     , sample_object_name
     , sample_statement_text
FROM
(
    SELECT
        query_hash, query_plan_hash,
        COUNT (*) AS cached_plan_object_count,
        MAX (plan_handle) AS sample_plan_handle,
        SUM (execution_count) AS execution_count,
        SUM (total_worker_time)/1000 AS total_cpu_time_ms,
        SUM (total_elapsed_time)/1000 AS total_elapsed_time_ms,
        SUM (total_logical_reads) AS total_logical_reads,
        SUM (total_logical_writes) AS total_logical_writes,
        SUM (total_physical_reads) AS total_physical_reads
    FROM sys.dm_exec_query_stats
    GROUP BY query_hash, query_plan_hash
) AS plan_hash_stats
CROSS APPLY
(
    SELECT TOP 1
        qs.sql_handle AS sample_sql_handle,
        qs.statement_start_offset AS sample_statement_start_offset,
        qs.statement_end_offset AS sample_statement_end_offset,
        CASE
            WHEN [database_id].value = 32768 THEN 'ResourceDb'
            ELSE DB_NAME (CONVERT (int, [database_id].value))
        END AS sample_database_name,

        OBJECT_NAME (
                         CONVERT (int, [object_id].value)
                       , CONVERT (int, [database_id].value)
                    ) AS sample_object_name,

        SUBSTRING (
            sql.[text],
            (qs.statement_start_offset/2) + 1,
            (
                (
                    CASE qs.statement_end_offset
                        WHEN -1 THEN DATALENGTH(sql.[text])
                        WHEN 0 THEN DATALENGTH(sql.[text])
                        ELSE qs.statement_end_offset
                    END
                    - qs.statement_start_offset
                )/2
            ) + 1
        ) AS sample_statement_text

    FROM sys.dm_exec_sql_text(plan_hash_stats.sample_plan_handle) 
          AS sql 

    INNER JOIN sys.dm_exec_query_stats AS qs 
           ON qs.plan_handle = plan_hash_stats.sample_plan_handle

    CROSS APPLY sys.dm_exec_plan_attributes (plan_hash_stats.sample_plan_handle) AS [object_id]

    CROSS APPLY sys.dm_exec_plan_attributes (plan_hash_stats.sample_plan_handle) AS [database_id]

    WHERE [object_id].attribute = 'objectid'
        AND [database_id].attribute = 'dbid'

) AS sample_query_text

ORDER BY total_cpu_time_ms DESC;

References:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s