While I respect the submitter, I humbly disagree with the answer provided and not for "religious reasons". In other words, I believe there is no facility that Microsoft has provided which decreases the need for the guidance to use stored procedures.
Any guidance provided to a developer which favors the use of raw text SQL queries must be filled with many caveats, such that I think the most prudent advice is to greatly encourage the use of Stored Procedures and, discourage your developer teams from engaging in the practice of embedding SQL statements in code, or submitting raw, plain-old text-based SQL requests, outside of SQL SPROCs (stored procedures).
I think the simple answer to the question of why use a SPROC is as the submitter surmised: SPROCs are parsed, optimized, and compiled. As such, their query/execution plans are cached because you've saved a static representation of a query and you, normally, will be varying it only by parameters, which is not true in the case of copied/pasted SQL statements which likely morph from page-to-page-and component/tier, and are often variablized to the extent that different tables, even database names, can be specified from call-to-call. Allowing for this type of dynamic ad hoc SQL submission, greatly decreases the likelihood of the DB Engine to re-use the query plan for your ad hoc statements, according to some very strict rules. Here, I am making the distinction between dynamic ad hoc queries (in the spirit of the question raised) versus the use of the efficient System SPROC sp_executesql.
More specifically, there are the following components:
- Serial and parallel query plans which don't hold user context and allow for reuse by the DB engine.
- Execution context which allows for reuse of a query plan by a new user with different data parameters.
- Procedure cache which is what the DB engine queries in order to create the efficiencies we seek.
When a SQL statement is issued from a web page, termed an "ad hoc statement", the engine looks for an existing execution plan to handle the request. Because this is text submitted from a user, it will be ingested, parsed, compiled, and executed, if it is valid. At this time it will receive a query cost of zero. Query cost is used when the DB engine uses its algorithm in order to determine which execution plans to evict from cache.
Ad hoc queries receive an original query cost value of zero, by default. Upon subsequent execution of the exact same ad hoc query text, by another user process (or the same one), the current query cost is reset to the original compile cost. Since our ad hoc query compile cost is zero, this does not bode well for the possibility of reuse. Obviously, zero is the least-valued integer, but why would it be evicted?
When memory pressures arise, and they will if you have a often-used site, the DB engine uses a cleanup algorithm to determine how it can reclaim memory that the Procedure cache is using. It uses the current query cost to decide which plans to evict. As you might guess, plans with a cost of zero are the first to be evicted from cache because zero essentially means "no current users of, or references to, this plan".
- Note: Ad hoc execution plans - The current cost is increased by each user process, by the plan's original compile cost. However, no plan's maximum cost can be more than its original compile cost...in the case of ad hoc queries...zero. So, it will be "increased" by that value...zero - which essentially means it will remain the lowest cost plan.
Therefore, it is quite likely that such a plan will be evicted first when memory pressures arise.
So, if you have a server build-out with lots of memory "beyond your needs", you may not experience this issue as often as a busy server that has only "sufficient" memory to handle its workload. (Sorry, server memory capacity and utilization are somewhat subjective/relative, though the algorithm is not.)
Now, if I am factually incorrect about one or more points, I'm certainly open to being corrected.
Lastly, the author wrote:
"Now we have statement-level optimization, so a properly parameterized query coming from an application can take advantage of the same execution plan as that query embedded in a stored procedure."
I believe the author is referring to the "optimize for ad hoc workloads" option.
If so, this option allows for a two-step process which avoids immediately sending the full query plan to the Procedure cache. It only sends a smaller query stub there. If an exact query call is sent back down to the server while the query stub is still in the Procedure cache, the full query execution plan is saved to the Procedure cache, at that time. This saves on memory, which during memory pressure incidents, may allow the eviction algorithm to evict your stub less frequently than a larger query plan that was cached. Again, this depends upon your server memory and utilization.
However, you have to turn this option on, since it's off by default.
Lastly, I want to stress that, often, the very reason developers would embed SQL in pages, components, and other places, is because they wish to be flexible and submit dynamic SQL query to the database engine. Therefore, in a real-world Use Case, submitting the very same text, call-over-call, is unlikely to occur as are the caching/efficiencies we seek, when submitting ad hoc queries to SQL Server.
For additional information, please see: