SQL Server sp_ExecuteSQL and Execution Plans
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?
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'
sp_executesql version will have an
objtype of "prepared"
How to Reduce the CPU Overhead of Dynamic SQL, For frequently executed queries, “EXEC sp_executesql @sql”is a good choice but at the risk of encountering parameter sniffing issues. For queries that aren't executed very often, “EXEC (@sql)” can be used, but you could end up with a bloated plan cache with lots of single-use execution plans. This can be a pro in that SQL Server can create a plan for each execution. This can be a con in that SQL Server needs to recompile/optimize for each execution. Let’s start with using sp_executesql to parameterize a query where SQL Server would also allow parameters: DECLARE @ExecStr NVARCHAR(4000);
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 ;
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.
Introduction to the sp_executesql stored procedure with examples, In spite of that, the EXEC statement created new query plans for each query execution. This type of usage could consume SQL Server resources� As a result, sp_executesql generated a one query plan in the first execution of the query and then it used the same query plan again and again. In spite of that, the EXEC statement created new query plans for each query execution. This type of usage could consume SQL Server resources and could be caused by performance issues.
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
sp_executesql (Transact-SQL), The line from MSDN is talking about using EXEC() , like this: SET @sql = ' SELECT foo FROM dbo.bar WHERE x = ''' + @x + ''';'; EXEC(@sql);. We know sp_execute, sp_executesql and exec are three different approaches to execute dynamic T-SQL queries or statements. Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime.
When does sp_executesql refresh the query plan?, sp_executesql (also known as “Forced Statement Caching”) Allows for statements to be parameterized. Only allows parameters where SQL Server would normally allow parameters; however, this string can be built using forms of dynamic constructs. There are several ways to get the estimated execution plan in SQL Server. Once the query is written completely, you can hit “ Ctrl + L ” and it will generate the estimated execution plan. You can also right-click on the query window and select “ Display Estimated Execution Plan ” from the context menu that appears.
EXEC and sp_executesql - how are they different?, The query is very poorly constructed (one of those SQL generators) consisting of many joins, cross DB joins (same server), sub queries,� There will then be a second execution plan generated and is only reused if the hashed SQL Text exactly matches the stored hash from a previous execution. This hashing is why the use of sp_executesql is recommended. In that case, a parameterized version of the SQL Text can be cached and reused, where the use of literals in any predicate will only allow for plan reuse if the same literals are reused. To back up my claim and help enhance understanding, please continue as I demonstrate the
- I wonder when the "sp_executesql doesn't cache plans" myth will ever die - read The curse and blessings of dynamic SQL
- @OMG Ponies - could parameter sniffing be an issue with sp_ExecuteSQL?
- @JNK: Since experiencing the behavior, I've put in anti-param sniffing by default anyway.
- @JNK - It caches the plan and reuses it so yes.
- 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?
- @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.
- 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