Cached Execution Plans in SQL Server

I have been working on a performance problem recently, so you might see several blog entries with information that help me. Hopefully they will help you.

Update: Here is a query to get the execution plan of the most frequently used queries.

Getting the SQL Server execution plan from a production can be difficult, since you are not running the code within Enterprise Manager. You can still get the execution plan of any running statement and display the graphical representation in Enterprise Manager.

If your script is currently running, you can lookup its execution plan in the plan cache. The steps are:

1. Run a query to get the the plan_handle of currently running code
2. Lookup the execution plan for the plan_handle.

The first query is:

SELECT
sder.session_id AS [SPID],
sder.sql_handle as [SQL_Handle],
sder.plan_handle as [PLAN_Handle],
sdes.login_name AS [Login],
sd.name AS [DBName],
sder.start_time AS [Start Time],
sder.status AS [Status],
sder.command AS [Command],
sdet.text AS [SQL Text],
sder.percent_complete AS [Pct Cmplt],
sder.estimated_completion_time AS [Est Cmplt Time],
sder.wait_type AS [Wait],
sder.wait_time AS [Wait Time],
sder.last_wait_type AS [Last Wait],
sder.cpu_time AS [CPU Time],
sder.total_elapsed_time AS [Total Elpsd Time],
sder.reads AS [Reads],
sder.writes AS [Writes],
sder.logical_reads AS [Logical Reads]
FROM
sys.dm_exec_Requests sder
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sdet
JOIN sys.dm_exec_sessions sdes on sder.session_id = sdes.session_id
JOIN sys.databases sd on sder.database_id = sd.database_id
WHERE
sder.session_id <> @@SPID and sder.session_id > 50

The second query is:

SELECT * FROM sys.dm_exec_query_plan ( <PLAN_Handle> )
--here <PLAN_Handle> is supplied based on
--the results from the first query.

This gives you the XML execution plan that you can copy and paste into notepad, save it with a *.sqlplan extension, and double click on the file to view the graphical version of the execution plan.