Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

What happens when you use WITH RECOMPILE


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.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.