If stored procedure execution plans are cached once and then reused, what version of the execution plan is cached for stored procedures that have dynamically created queries?
Dynamic SQL queries and stored procedures generate separate execution plans and are cached individually. When a stored procedure has a dynamic SQL block executed within it, there is an execution plan which is extremely generic and only indicates the execution of the dynamic SQL. There will then be a second execution plan generated and is only reused if the hashed SQL Text exactly matches the stored hash from a previous execution. This hashing is why the use of sp_executesql is recommended. In that case, a parameterized version of the SQL Text can be cached and reused, where the use of literals in any predicate will only allow for plan reuse if the same literals are reused.
To back up my claim and help enhance understanding, please continue as I demonstrate the scenarios.
For our demonstration, I will show you the execution plan caching for two different stored procedures. You can find my complete scripts, including the tables and data, here. I won’t be cluttering this post with everything necessary for setup, just what is relevant.
For this demo, it is important to create stored procedures which generate different dynamic SQL based on input parameters. It is also important to analyze any differences which might exist between using EXEC() and sp_executesql.
Each of these procedures contain the exact same queries. The only difference is their method of executing the dynamic SQL. Now, all there is to do is execute each procedure from a cold cache, using all possible input parameters. We will then examine the plans and how they were cached.
The results do not really matter here. Instead, here is the query to look up the execution plans from cache. I’ve excluded ‘%dm_exec_sql_text%’ so that I can ignore the cache query itself. I’ve also included ‘%E.empid%’ because this is a string which exists in all variations of the stored procedure and the dynamic SQL being generated.
From these results we can understand a lot about what has happened.
- The dynamic SQL is cached separately from the stored procedures.
- The dynamic SQL’s cached plan can be reused, as noted by the UseCounts column.
- Each unique string generated by the dynamic SQL is cached separately.
When inspecting a couple more columns, we can see that each cached plan is assigned a bucketid. This bucketid is the result of hashing the SQL Text that was compiled. This is how SQL Server knows that two dynamic SQL queries are identical and can utilize plan cache reuse.
When we click into the query_plan column data, the execution plans open. The stored procedures compile with an EXECUTE STRING operator and the ad hoc (dynamic SQL) queries have execution plans which are more like we are used to seeing.
For the stored procedures that we setup, there is no difference in the plan caching when using sp_executesql vs. EXEC(). This is because the SQL Text for each was identical. For identical strings, these methods do not actually cache differently. You will see differences when using temporary tables and when your queries are using dynamic predicates, such as replacing pieces of the string with an ID value or search keyword. These pieces of the demonstration were beyond the scope I set for this post, however. Check out this post if you want to learn more.