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.

Despite what DBAs will tell you ("use stored procedures because the server will optimize them thus they are faster than ad-hoc queries"), I have heard that SQL server does not actually optimize stored procedures.

So, does SQL Server optimize stored procedures? Does it even parse them beforehand, or does it just store them internally as a sql script?

I did not ask about a specific SQL Server version because I assume history is relevant in a meaningful understanding.


Note that my question is NOT, "Are ad-hoc queries as fast as stored procedures?" There are too many other factors involved in answering this.

share|improve this question
add comment

migrated from stackoverflow.com Oct 5 '11 at 13:51

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

3 Answers

All queries executed by SQL Server need to be compiled first. Stored Procedures are no different in this respect, they don't get any kind of additional optimisation.

They are parsed when created to check invalid syntax but they don't actually get an execution plan generated until first executed. When the plan is compiled it will use the parameter values passed in this initial execution to generate it. This can lead to parameter sniffing issues.

All execution plans generated will remain in the plan cache generally until either evicted due to memory pressure or they becomes invalid due to schema changes or it gets an optimality based recompile. (For adhoc queries the "optimize for ad hoc workloads" option can change this behaviour).

The main benefit of stored procedures is that for subsequent similar queries the plan is more likely to be re-used. For adhoc queries this is dependant on auto parametrisation which is quite conservative so you may well end up with lots of compilations for very similar queries but with different parameter values that all end up in the same plan anyway.

I have left out a lot of details in this answer. See this plan caching white paper for full details

share|improve this answer
add comment

In addtion to Martin Smith's answer

Stored procs are pre-parsed for syntax but not compiled until needed. This can be seen with "Deferred Name Resolution": a stored proc can be created that references non-existent tables. The error will happen at execution time when the stored proc is actually compiled.

The "myth" or pre-compiled goes back to SQL Server 6.5. The optimiser changed massively in SQL Server 7+. This can be seen in 2 MSDN whitepapers for SQL Server 2000:

share|improve this answer
add comment

As I know the SQL will create the execution plan which will be cached in the DB. So anytime you run the procedure the prepared plan will be used (so the server does not to optimize again). In some cases this can save a lot of time - (e.g. when the procedure is called frequently). This can be also disadvantage - the compiled plan is created under specific condition which can change during the time - and therefore the plan can be outdated which can degrade the performance of the execution.

share|improve this answer
1  
The plan doesn't get outdated. The statistics it relies on might be outdated. If the statistics are updated (either manually or automatically) then the plan itself will be recompiled automatically in the absence of a KEEP FIXEDPLAN query hint. Optimality based recompiles also happen if a new index is added that might help the query etc. –  Martin Smith Oct 5 '11 at 11:30
add comment

Your Answer

 
discard

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