How to get Execution Plan after query was run?

Question

How to get Execution Plan after query was run?

Dan121

SSCommitted

Points: 1616
More actions
December 11, 2014 at 9:24 am

#293397

I am wondering is there possibility to get execution plan after the query ran by application? Another word, when I run 'top poor performance query' it gives me a 'link' for query plan, not the graphical execution plan.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You need to copy and paste the plan into a text file and save it with a .sqlplan extension. You can then open it in graphical form in SSIS.

John

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

John Mitchell-245523 (12/11/2014)
You need to copy and paste the plan into a text file and save it with a .sqlplan extension. You can then open it in graphical form in SSIS.
John

I believe that you mean SSMS.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 3

Have you tried clicking on that link? It will open a graphical plan within SSMS.

But, if not, you can always query sys.dm_exec_text_query_plan. That will output just a raw varchar(max) column that you can save to a file with the right extension and then you'll have an execution plan.

Note, these plans retrieved from cache are "actual" plans in that they are the plans that were used to manipulate your data. But, they're not "actual" plans in that they will have no run-time information (Actual Executions, Actual Rowcount, etc.).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

Further on Grant't post, here's a code snip for retrieving the execution plan

😎

USE tempdb;

GO

SET NOCOUNT ON;

/*

The query

*/

select

*

from sys.objects

/* get the execution plan */

;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

,QUERY_PLAN AS

(

SELECT

XP.query_plan

FROM sys.dm_exec_requests ER

CROSS APPLY sys.dm_exec_query_plan(ER.plan_handle) XP

WHERE ER.session_id = @@SPID

)

SELECT

*

FROM QUERY_PLAN QP;

Dan121 SSCommitted Points: 1616 More actions · Answer 5

Dan121

SSCommitted

Points: 1616

December 12, 2014 at 6:59 am

#1764402

Thank you all smart folks 🙂