Deprecated: Function split() is deprecated in /home/tbriggs/public_html/s9y/plugins/serendipity_event_metadesc/serendipity_event_metadesc.php on line 101

Viewing the Execution Plan of a Running Query in SQL Server

Or, Simple SQL Server DBA Tricks

Here's a trick I wish I'd discovered long ago... given a long-running query started by session ID of 42, you can get the handle to the cached execution plan for the query by running:

SELECT plan_handle FROM sys.dm_exec_requests WHERE session_id = 42

Given that plan handle, you can get an XML representation of the query plan by running:

SELECT query_plan FROM sys.dm_exec_query_plan (0x06001D009FE38431400399D4000000000000000000000000);

(The hex value there is the value returned by the previous query.)

Now, save the output of that query to a file with a .sqlplan extension, and open it with Management Studio. Viola! A graphical representation of the query plan for the problem query as it was actually executed.

Grabbing the query and generating a plan for it will usually give you the same result, I believe. It has always bugged me that there was a potential difference between the plan generated for a problem query at run-time vs. the plan generated at problem diagnosis time though. This relieves that little concern and provides other options for working with the query plan as well (it is XML, after all).

Trackback specific URI for this entry

No Trackbacks


No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.