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?
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"
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With