1

Possible Duplicate:
How do I obtain a Query Execution Plan?

how can I see the execution plan in SQL Server 2005 for a dynamic sql that is executed? I cannot save any files on the computer where the database resides. Here's a small example:

declare @sqlcmd nvarchar(1000)
declare @param1 nvarchar(14)

set @param1 = '11111111%'
set @sqlcmd = 'SELECT * FROM myTable WHERE customer_id LIKE @customer_id'
EXECUTE sp_executesql @sqlcmd, N'@customer_id nvarchar(14)', @customer_id = @param1

So I would like to see the execution plan that actually is used for SELECT query.

Community
  • 1
  • 1
KOT
  • 1,986
  • 3
  • 21
  • 35

2 Answers2

4

Just press ctrl + m in SQL Server Management Studio to activate "Include Actual Execution Plan". This will show the real execution plan just as with any ordinary query.

Andreas Ågren
  • 3,879
  • 24
  • 33
  • Ah, yes, but it seems like I can't get it to text? Set showplan_text on doesn't give me the plan it seems. – KOT Feb 22 '11 at 12:18
  • No it doesn't, but if you absolutely have to see it in that format then you could do a print of the generated query and run it manually in SSMS. – Andreas Ågren Feb 22 '11 at 12:21
  • 1
    Using SSMS 2016 I was able to get the execution plan from dynamic SQL to display *in text*. Step 1, SSMS Options, Query Results, SQL Server, Results to text, set max chars to 8192 (important else plan is truncated). Next, open a new New Query window, enter the command SET STATISTICS XML ON; then your sql... declare .... etc.. set output to Text, then execute. Plan will be xml text output. That said the max output appears to be 8192 which could be exhausted fast with multiple statements. – Jeff Mergler Oct 07 '16 at 17:20
1

You can use profiler to capture the execution plans. You can then use something like SQL Sentry's Plan Explorer (http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp) to be able to view/break down the captured query plans.

Ian Chamberland
  • 111
  • 2