Does SQL server optimize or pre-parse stored procedures?

Question

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.

Answer 1

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

Answer 2

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:

• http://msdn.microsoft.com/en-us/library/aa174792(SQL.80).aspx
• http://msdn.microsoft.com/en-us/library/aa902682%28SQL.80%29.aspx

Answer 3

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.