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.
Best Answer
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