Advertisements
Home > Dynamic Management Views/Functions, Performance Tuning > Get Top 50 SQL Statements/Query Plans

Get Top 50 SQL Statements/Query Plans

Finding out what statements causing grief on SQL Server has become significantly easier with SQL Server 2005+.  I am using some of the DMV/DMF to pull this information together.  Please note on a 64-bit system with lots of memory this can take a bit of time to run because of Sort & pulling execution plan information.  If that is an issue, I recommend getting the full information without sort by and dumping it to a temp table.  From there do your analysis, here are few options for running this statement:

Following query returns everything it is very expensive and takes a while to complete, even if you dump it to tempdb it can take 10+ mintutes on systems with lots of memory.

-- Get Top 50 Statements that have been recorded in SQL Server since last restart.
--

DECLARE @Top50By INT

-- 1 = Duration, 2 = CPU, 3 = Reads
SET @Top50By = 1

     SELECT   TOP 50
              SUBSTRING(st.text,
                        (qs.statement_start_offset/2)+1,
                        ((CASE qs.statement_end_offset WHEN -1 THEN
                             DATALENGTH(st.text)
                          ELSE
                             qs.statement_end_offset
                          END - qs.statement_start_offset)/2) + 1)  AS SQLStatement
            , qp.query_plan                                         AS QueryPlan
            , execution_count                                       AS NumOfExecutions
            , (total_elapsed_time/execution_count)/1000             AS AVGDuration_ms
            , (total_worker_time/execution_count)/1000              AS AVGCPU_ms
            , (total_logical_reads/execution_count)                 AS AVGReads
            , (total_logical_writes/execution_count)                AS AVGWrites
       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
   ORDER BY CASE
            WHEN @Top50By = 1 THEN
               (total_elapsed_time/execution_count)/1000
            WHEN @Top50By = 2 THEN
               (total_worker_time/execution_count)/1000
            WHEN @Top50By = 3 THEN
               (total_logical_reads/execution_count)
            END DESC

Same code, but I have taken out the execution plan, so it should execute faster. The idea is once you know which queries you want to address those are the only plans you will bring up; making it faster. However this method if you save the data, there is no grantee the plan will be there in the future.

-- Get Top 50 Statements that have been recorded in SQL Server since last restart.
--

DECLARE @Top50By INT

-- 1 = Duration, 2 = CPU, 3 = Reads
SET @Top50By = 1

     SELECT   TOP 50
              SUBSTRING(st.text,
                        (qs.statement_start_offset/2)+1,
                        ((CASE qs.statement_end_offset WHEN -1 THEN
                             DATALENGTH(st.text)
                          ELSE
                             qs.statement_end_offset
                          END - qs.statement_start_offset)/2) + 1)  AS SQLStatement
            , qs.plan_handle										AS PlanHandle
            , execution_count                                       AS NumOfExecutions
            , (total_elapsed_time/execution_count)/1000             AS AVGDuration_ms
            , (total_worker_time/execution_count)/1000              AS AVGCPU_ms
            , (total_logical_reads/execution_count)                 AS AVGReads
            , (total_logical_writes/execution_count)                AS AVGWrites
       FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
   ORDER BY CASE
            WHEN @Top50By = 1 THEN
               (total_elapsed_time/execution_count)/1000
            WHEN @Top50By = 2 THEN
               (total_worker_time/execution_count)/1000
            WHEN @Top50By = 3 THEN
               (total_logical_reads/execution_count)
            END DESC
Advertisements
  1. No comments yet.
  1. April 15, 2013 at 11:31 am

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: