CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-execution-plan
AJM
AJM

Reputation: 32490

SQL Server sp_ExecuteSQL and Execution Plans

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?

Upvotes: 9

Views: 7801

Answers (3)

Eric Petro
Eric Petro

Reputation: 11

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

Upvotes: 1

ALZDBA
ALZDBA

Reputation: 21

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.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452988

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"

Upvotes: 10

Related Questions