9

I have a query which is super fast in SQL Server Management Studio and super slow when run under sp_ExecuteSQL.

Is this to do with caching of execution plans not happening when run under sp_ExecuteSQL?

Pang
  • 9,564
  • 146
  • 81
  • 122
AJM
  • 32,054
  • 48
  • 155
  • 243

3 Answers3

10

No.

You can see both execution plans and compare them using the following query.

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%Some unique string in your query%' 
                                          and attribute='set_options'

The sp_executesql version will have an objtype of "prepared"

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 3
    Why would execution plans be so radically different? For example, I've looked at my query execution plan directly from Sql Management Studio (takes 3 seconds), and the execution plan from the sp_executeSql (takes 5+ minutes). the plan from the sp_executeSql completely ignores a few of the key indexes that the direct call found. Can someone explain why a call from management studio finds keys, but the call via the sp_ExecuteSql does not? – Nathan Tregillus Mar 28 '11 at 16:46
  • @NathanTregillus - Probably parameter sniffing, you can look at the cached plan XML to see the values of the parameters that the plan was actually compiled with. – Martin Smith Dec 19 '12 at 09:46
  • thanks for the response @MartinSmith. It was actually due to how we use the contextInfo as a filter in our view, and how it is not factored into the execution plan – Nathan Tregillus Dec 26 '12 at 23:55
1

Experienced the same behaviour. ( set options equal ) Regular Query producing parallel plan and using sp_executesql it produced a serial plan.

declare @xyzParam1 datetime,@xyzParam2 datetime
select @xyzParam1='Sep  1 2014 12:00:00:000AM',@xyzParam2='Sep 26 2014 11:59:59:000PM'
SELECT  *  FROM Theview WHERE  departuretime BETWEEN @xyzParam1 AND  @xyzParam2
;

vs

 exec sp_executesql N'SELECT  *  FROM Theview WHERE  departuretime BETWEEN @xyzParam1 AND  @xyzParam2',N'@xyzParam1 datetime,@xyzParam2 datetime',@xyzParam1='Sep  1 2014 12:00:00:000AM',@xyzParam2='Sep 26 2014 11:59:59:000PM'

I managed to obtain an optimal result modifying the used view because it contained e.g. left joins for data that was always expected. ( converted to INNER join )

Now the regular query picks the same plan as the one obtained using sp_executesql and performance is way better.

Magier
  • 437
  • 1
  • 6
  • 18
ALZDBA
  • 21
  • 2
1

I resolved a difference in query plan between ad-hoc TSQL in SSMS and sp_executesql by updating stats. This was a simple query that touched the same table twice. UPDATE STATISTICS sometable

Eric Petro
  • 11
  • 1
  • Statistics being outdated were the culprit here, too. Of course option(recompile) solved the problem, as well as deleting a simple space in the query that was being passed to sp_executesql, but, in our case, the statistics were more than 1 year old and that was the real problem. – mprost Nov 25 '22 at 12:41