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.