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.