It depends on where you’re looking and how many statements are in the stored procedure.
Let’s take a look at some demos!
First, let’s create a stored procedure with multiple statements
USE StackOverflow2010 GO CREATE OR ALTER PROCEDURE TestStatements (@Username NVARCHAR(100)) AS BEGIN SELECT Reputation FROM Users WHERE DisplayName = @Username SELECT DownVotes FROM Users WHERE DisplayName = @Username END GO
Great! Now, I’ll execute the procedure.
--Clear your plan cache for this demo EXEC TestStatements 'Arthur' GO 10
Perfect. I ran it 10 times to ensure the plan stays in cache for my other demos. Now, my favorite plan cache view is sys dm_exec_query_stats since it has the statistics on usage.
SELECT s_text.text, s.creation_time, s.last_execution_time, s.execution_count, s.statement_sql_handle FROM sys.dm_exec_query_stats as s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) as s_text
So we have two entries for this stored procedure. I included the statement sql handle to show that each statement handle has its own text. Let’s parse that text to see each statement. I copied the parsing SQL from this Plan Cache article.
SELECT SUBSTRING(text, (statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END - statement_start_offset)/2) + 1) AS query_text, s.creation_time, s.last_execution_time, s.execution_count FROM sys.dm_exec_query_stats as s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) as s_text
So there’s one entry per statement inside sys dm_exec_query_stats. Let’s take a look at another plan cache DMV, dm_exec_cached_plans.
select s_text.text, s.cacheobjtype, s.objtype from sys.dm_exec_cached_plans as s CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) as s_text
In this view, only the top entry is for the stored procedure. In my opinion, this brings us to a total of three objects in the plan cache for this stored procedure.
I’ll make another post about clearing stored procedures from the plan cache! Thanks for reading, stay tuned!