Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

You'll have to forgive my naivety as I'm not a DBA but my understanding is that over time the statistics of a database change and a stored procedure must be recompiled to keep the query plan up to date with the latest statistics.

Assuming I have a stored procedure in my database that is recompiled against the latest statistics at some regular interval, what are the implications of in-lining the stored procedure in code and wrapping it in an sp_executesql statement? Do I lose the refreshing of the query plan that used to happen as part of the recompilation of the procedure?

If there's anything else (other than permissions) that I need to consider before I make this change then I'd appreciate your insights.

EDIT: I just read this on the MSDN:

The ability of the SQL Server query optimizer to match the new Transact-SQL string with an existing execution plan is hampered by the constantly changing parameter values in the text of the string, especially in complex Transact-SQL statements.

So assuming the stored procedure I'm attempting to in-line and wrap in sp_executesql does indeed contain some parameters, is this saying that although my execution plan is cached, I'm making it more difficult for SQL Server to find and reuse it?

share|improve this question

migrated from stackoverflow.com Jul 30 '13 at 14:45

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 6 down vote accepted

The line from MSDN is talking about using EXEC(), like this:

SET @sql = 'SELECT foo FROM dbo.bar WHERE x = ''' + @x + ''';';
EXEC(@sql);

In my testing, modern versions of SQL Server are still able to reuse a plan like this, but there may be other variables (such as version, or for example if you add conditional WHERE clauses based on the presence of certain parameters - in which case that will generate a different plan).

If you use sp_executesql then the parameter values can still cause parameter sniffing issues (just like with normal SQL), but this has nothing to do with whether SQL Server can re-use the plan. This plan will get used over and over again, just as if you hadn't used sp_executesql at all, unless variables that would cause a direct query to get recompiled, in which case this one will get recompiled too (essentially, SQL Server doesn't store anything with the plan that says "this was executed from sp_executesql, but this one wasn't):

SET @sql = N'SELECT foo FROM dbo.bar WHERE x = @x;';
EXEC sp_executesql @sql, N'@x VARCHAR(32)', @x;

As a bonus, this has built-in protection against dynamic SQL and avoids you having to worry about doubling up single quotes due to string delimiters. I blogged about some of this here.

If you are having issues with plan re-use and/or parameter sniffing, some things you should look into are OPTION (RECOMPILE), OPTIMIZE FOR, optimize for ad hoc workloads and simple/forced parameterization. I addressed a few similar questions in response to a recent webcast here, it may be worth a skim:

http://sqlperformance.com/performance-palooza

The gist is: don't be afraid to use sp_executesql, but only use it when you need it, and only spend energy over-optimizing it when you have an actual performance issue. The example above is a terrible one because there's no reason to use dynamic SQL here - I've written this answer assuming you have a legitimate use case.

share|improve this answer
    
Great answer, covers everything I was asking. –  james lewis Jul 30 '13 at 15:48

The queries which are run via sp_executesql follow the same rules of execution plans as normal queries which aren't run through sp_executesql. If the query text changes then a new plan is created. If the text doesn't change because of the user of parameters then the plan is reused. When the stats are updated then plans expire and new plans are generated the next time the query is run.

share|improve this answer
    
Thanks for your answer, I made an edit regarding parameters as I've now realized that each time I call sp_ExecuteSql I will be using a different string as the query due to the fact that, from the perspective of Sql Server, I've replaced the parameters with hard coded values (they will be input in code before I send my query to Sql Server). Do you know of a way around this? Would declaring variables in my in-line sql statement help the query optimizer to find my cached query plan? –  james lewis Jul 30 '13 at 15:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.