Reading different explanations about execution plan caching by Microsoft SQL Server, I'm confused about the benefits of using stored procedures instead of non-dynamic queries.
By a non-dynamic query I mean a fully parametrized query string which doesn't change through multiple invocations.
As I understand it:
-
The execution plan is cached both for a stored procedure and an ordinary query.
-
For a stored procedure, the execution plan is precomputed, which leads to a slight benefit over ordinary queries the first time the stored procedure is invoked.
The sources look rather contradictory to me:
-
Execution Plan Caching and Reuse article on MSDN doesn't make difference between parametrized queries and stored procedures. The subsections emphasize the importance of parametrized queries in order to make it easy for SQL Server to cache the execution plan.
-
SQL Server query execution plans – Basics claims the opposite (emphasis mine):
When it comes to executing ad hoc queries, query plans are created based on complete code, so different parameters or any change in code will prevent reuse of the existing plan.
-
On DBA.StackExchange, the comment on an answer related to the benefits of stored procedures indicates that parametrized queries have exactly the same effect than stored procedures.
So, in the context where the execution plan is not thrown out of the cache and when, for the sake of experiment, I want to run billions of times a rather complicated query which would benefit from an execution plan and which takes one parameter which changes every time, would there be any benefit in terms of execution plan caching¹ to use stored procedures instead of an ordinary parametrized query?
¹ Outside the scope of the execution plan, there would be minor performance benefits of using a stored procedure, for example in terms of network footprint: passing the name of the stored procedure and its parameters is slightly better than passing the whole query. Those benefits are outside the scope of my question, which is purely about execution plan cache.
Best Answer
The answer is also available as a standalone blog article.
In order to find it out, I did some tests. The goal is to have the same parametrized query executed either directly from C# or by calling a stored procedure and to compare the runtime performance.
I started to create a stored procedure which does a sample query using Adventure Works database:
Then, I use the following piece of code to compare the performances:
Notice
option (recompile)
andwith recompile
. This will force SQL Server to discard previously cached execution plans.Each query is run une hundred times with a different parameter every time. The time spent by the server is measured at client side.
By running
DBCC FreeProcCache; DBCC DropCleanbuffers;
before gathering metrics, I make sure that all previously cached execution plans are removed.Running this code gives the following output:
Let's run it again:
It seems that the performance is very close between stored procedures and direct queries. Running the code a dozen times, I notice that stored procedures seem to be slightly fast, but the gap is very narrow. Possibly passing around the whole query creates this additional cost, which may increase if SQL Server is hosted on a dedicated machine with a slow LAN between it and the application server.
Let's now turn execution plan caching on and see what happens. To do this, I remove
option (recompile)
andwith recompile
from the code. Here's the new output:It becomes clear that caching has exactly the same effect for both direct queries and stored procedures. In both cases, it reduces the time to nearly zero milliseconds, and the most expensive query is the first one—the one which runs after the removal of cached execution plans.
Running the same code again shows a similar pattern. Sometimes, queries are faster, and sometimes stored procedures are. But every time, the first query is the most expensive one, and all others are close to zero milliseconds.
Reopening SQL connection
If the SQL connection is opened for every query, such as in this slightly modified code:
the observed metrics are very similar:
with
option (recompile)
andwith recompile
and:without.
Under the hood
Let's see what happens under the hood. The following query shows cached execution plans:
When running this query after executing the stored procedures one hundred times, the result of the query looks like this:
When running the query directly one hundred times, the result is:
Conclusion
The execution plan is cached for stored procedures and direct queries.
The performance between stored procedures and direct queries is very similar when the SQL Server and the application are hosted on the same machine. When SQL Server is hosted on a dedicated server accessed through LAN, using stored procedures may result in better performance.