SQL Server sp_ExecuteSQL and Execution Plans

Question

I have a query which is superfast 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 spExecuteSQL?

Answer 1

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"