1

I have confusion regarding query plan when using dynamic query.

Case 1: Insert the result of dynamic query in temp table and then selecting the temp table

When I run the following query:

USE AdventureWorks;


DECLARE @Sql NVARCHAR(255)
DECLARE @FromID INT
DECLARE @ToID INT
SET @FromID = 10
SET @ToID = 100000


CREATE TABLE #Temp ( EmployeeID INT )

SELECT  @sql = 'Select EmployeeID from HumanResources.Employee Where 
EmployeeID between @FromID AND @ToID';

INSERT  INTO #Temp
        EXEC sp_executesql @sql, N'@FromID int,@ToID int', @FromID = @FromID,
            @ToID = @ToID

SELECT   EmployeeID
FROM    #Temp

DROP TABLE #Temp

The execution plan I see is:

enter image description here

The query plan shows nothing about my select query which I wrote inside the dynamic sql query i.e the plan regarding this stuff "Select EmployeeID from HumanResources.Employee Where EmployeeID between @FromID AND @ToID" is missing. Now consider the second case:

Case 2: Just executing the dynamic query (no use of temp table here):

USE AdventureWorks;


DECLARE @Sql NVARCHAR(255)
DECLARE @FromID INT
DECLARE @ToID INT
SET @FromID = 10
SET @ToID = 100000


SELECT  @sql = 'Select EmployeeID from HumanResources.Employee Where EmployeeID between @FromID AND @ToID';


EXEC sp_executesql @sql, N'@FromID int,@ToID int', @FromID = @FromID,
    @ToID = @ToID

For this query I am seeing the following plan:

enter image description here

This time it shows the execution plan of following stuff "Select EmployeeID from HumanResources.Employee Where EmployeeID between @FromID AND @ToID"

My question here is that what happened in the first case? Why it didn't show anything about select query (present inside dynamic query) plan? Is it performance wise good or bad to use dynamic query in the way I used in first case? I was thinking to use this case:

INSERT  INTO #Temp
        EXEC sp_executesql @sql, N'@FromID int,@ToID int', @FromID = @FromID,
            @ToID = @ToID

because after that query I will apply the paging on the #Temp table.

Also where goes the following part in the first execution plan:

INSERT  INTO #Temp
        EXEC sp_executesql @sql, N'@FromID int,@ToID int', @FromID = @FromID,
            @ToID = @ToID
Rocky Singh
  • 15,128
  • 29
  • 99
  • 146
  • In SSMS 2012 against a 2012 instance I see three plans [screenshot](http://i.stack.imgur.com/U6uiI.png). I seem to remember encountering this issue before and using SQL Profiler to retrieve the plans though. – Martin Smith Jun 01 '13 at 12:42
  • @MartinSmith I am using SQL Server 2008 R2 at this moment. Since you said you are seeing 3 plans, are you seeing the plans which I mentioned are missing in my results? Is this a bug or what as per SQL Server 2008 R2 context? – Rocky Singh Jun 01 '13 at 12:43
  • 1
    I've added the screenshot to my previous comment for `Case 1` in your question (After fixing it to work against AdventureWorks2012 schema). Think this is just a bug that has now been fixed. [Related Connect Item](http://connect.microsoft.com/SQLServer/feedback/details/697046#details) – Martin Smith Jun 01 '13 at 12:47
  • 1
    Thanks Martin for letting me know about that. So the missing plan execution stuff is fixed in SQL Server 2012. I think until I shift to that version I have no correct way to compare the plans when I am using dynamic sql query with "insert" (since that plan is not appearing in execution plan), Am I correct? – Rocky Singh Jun 01 '13 at 12:59
  • 1
    You can use SQL Profiler/SQL trace (filtered on the spid of your connection) to get the plans. – Martin Smith Jun 01 '13 at 13:00
  • See [How do I obtain a Query Execution Plan?](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) for alternative ways of obtaining a query execution plan – Justin Jun 04 '13 at 15:29

0 Answers0