I saw this question and my immediate thought was “Well, duh, the execution plan is recreated by a recompile.” But, someone a bit smarter than myself suggested that, in fact, no plan was stored in cache, forcing a new plan to be created each time the query was run.
So, which is it? Does a plan get added to cache and then removed every time the procedure is called, or do you get nothing in cache and the “recompile” is actually a compile every time? As Mr. Owl says, let’s find out. I have a small script that looks like this:
CREATE TABLE [dbo].[Test]( [col] [varchar] (10) NULL ); CREATE TABLE [dbo].[Test2] ( [col] VARCHAR(10) NULL ); INSERT INTO dbo.Test (col) VALUES ('Val1'), ('Val2'), ('Val3') ; INSERT INTO dbo.Test2 (col) VALUES ('Val1'), ('Val2'), ('Val3') CREATE PROCEDURE dbo.spTest WITH RECOMPILE AS SELECT t.col FROM dbo.Test AS T JOIN dbo.Test2 t2 ON T.col = t2.col WHERE t.col = 'Val2' ;
The reason I’m using two tables is because a single table query as simple as this would create a trivial execution plan. These are never cached. Now I can execute the query and then check to see what was put into the cache, just like this:
EXEC spTest ; SELECT * FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text LIKE '%spTest%'
When this is run, I get nothing returning. It’s not in the cache. But if you alter the procedure to take out the WITH RECOMPILE statement and then re-run the procedure and search against the DMV’s, it’s there. Gail had it right.
Funny thing. Because I had thought about this the wrong way, I quickly checked my book to see how badly I was off… I had it correct there. Whew!
Lesson learned: Don’t rely on my memory.