Monday, April 11, 2011

Pulling Query Execution Plans

Sometimes, you want to pull the Query Execution Plans for any currently running processes on SQL Server.  I use a fairly common method that you may have seen before.

Query Execution Plans

Basic Query

Let’s begin begin with a basic query against sys.dm_exec_requests:

[sql]

-- basic query

select * from sys.dm_exec_requests    -- one row for each request on the server
go

[/sql]

This query returns one row for every request that is currently running on SQL Server.  Technically, everything you need is already here.  But for us mere humans, it helps to dress up the query a little bit.

Filter System Processes and Observer Effect

The first thing I like to do is filter out the systems processes.  The vast majority of the time, I am focused on the user queries, so I do not need to look at them.  In SQL Server, the system processes will have and ID from 1 through 50.  By adding a WHERE clause for Session ID greater than 50, we can easily filter out any system processes that are running on the server.

The second thing I like to do is filter out my own process.  Having your own requests mixed in with the user requests can muddy up your results.  This is known as the Observer Effect.

Here is the updated query:

[sql]

-- filter out system processes and the observer effect

select * from sys.dm_exec_requests
where session_id > 50                -- filter out system processes
and session_id <> @@SPID;            -- filter out myself
go

[/sql]

Design Patterns

In the software development world, the idea of Design Patterns is a powerful programming technique.  The basic idea is that idea that most programming problems have already been solved before and you can reuse the solutions in order to create better software.

One of the most common SQL Design Patterns you will see is what I call the CROSS APPLY Design Pattern.  This is used to link a DMV to a DMF.

If you review the results of the previous query, notice two columns; sql_handle and plan_handle.  By using these two columns, we can link in the actual SQL command that goes with the request, and the corresponding Query Execution Plan.

Link in the SQL Text

First, let’s link in the SQL Text:

[sql]

-- link in SQL text

select er.*, st.text                                -- SQL text
from sys.dm_exec_requests er                       -- base DMV
cross apply sys.dm_exec_sql_text(er.sql_handle) st -- link to the DMF
where session_id > 50
and session_id <> @@SPID;
go

[/sql]

This query uses the sql_handle column to link the sys.dm_exec_requests DMV to the sys.dm_exec_sql_text DMF.  Once we have done this, we can now return the SQL command that was issues as part of the request.

Link in the Execution Plan

By applying the same technique that we used to obtain the SQL Text, we can pull in the Execution Plan:

[sql]

-- link in XML query execution plan

select er.*, qp.query_plan                               -- XML query execution plan
from sys.dm_exec_requests er                            -- base DMV
cross apply sys.dm_exec_query_plan(er.plan_handle) qp    -- link to the DMF
where session_id > 50
and session_id <> @@SPID;
go

[/sql]

Link in Both

Now, let’s put both of these together and grab the SQL text and the Query Plan:

[sql]

-- link in both

select er.*, st.text, qp.query_plan
from sys.dm_exec_requests er
cross apply sys.dm_exec_sql_text(er.sql_handle) st       -- SQL text
cross apply sys.dm_exec_query_plan(er.plan_handle) qp    -- query plan
where session_id > 50
and session_id <> @@SPID;
go

[/sql]

Simmer Down

The base DMVs often give you quite a lot of columns to parse through.  One technique I like to use is to pare down the resulting columns so that I only see the ones that are most useful to me.  Here is an example of one version of this query that I run almost daily.

[sql]

-- simmer down

select er.session_id, es.host_name, db_name(er.database_id) as 'database', er.status,
er.command, er.blocking_session_id as 'blocker',
er.wait_type, er.wait_time, er.wait_resource, er.percent_complete,
er.cpu_time, er.total_elapsed_time, er.total_elapsed_time - er.cpu_time as 'signal time',
er.reads, er.writes, er.logical_reads, er.granted_query_memory * 8 as 'query mem kb',
st.text, qp.query_plan
from sys.dm_exec_requests er
join sys.dm_exec_sessions es
on er.session_id = es.session_id
cross apply sys.dm_exec_sql_text(er.sql_handle) st
cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where er.session_id > 50
and er.session_id <> @@SPID
order by er.session_id;
go

[/sql]

A good exercise is to look through the documentation for the various columns and then add or remove the ones that are important to you.  The columns and information that are useful to you will most likely differ from what I have listed here.  Over time, your own experience will allow you to modify this code and tailor it to your needs.

Mathemagical

You may have noticed I did a little math on one of the columns.  granted_query_memory returns the number of pages that have been allocated to the query.  Remember that in SQL Server, pages are 8k.  So, I multiplied granted_query_memory by eight and renamed the result to something more meaningful to me.  Your preference might be to think in pages, or perhaps you prefer base-5, or some other strange numerical system.  Myself, I like kb.

Conclusion

Looking at the currently running requests and pulling their Execution Plans is an essential skill for a DBA.  By digging in and looking at what is going on inside the server, you can get a better idea as to what the root of your performance problems may be.

Furthermore, the CROSS APPLY Design Pattern is one of the more powerful TSQL programming tricks available to you.  Try to understand how it works and leverage it on new DMVs and DMFs.  I still remember the day the light suddenly came on for me.  Since then, I am constantly finding new ways to apply this pattern to new areas.

No comments:

Post a Comment