What happens when you use WITH RECOMPILE

August 21, 2009 at 8:09 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL)


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.

14 Comments

  1. Gail said,

    Easier way to test this (and how I discovered it in the first place) is to use Profiler (on a dev server) and trace the cache hit, cache insert and cache remove events.

    I’ve used this feature once before to alleviate contention around the procedure cache. The proc in question was called somewhere around 100 times a minute and recompiled every time (temp tables SQL 2000) and we had some nasty waits as SQL tried to recompile, cache the plan then execute the proc.

    Execution time dropped from 1.5 sec to 1.2 sec just by adding WITH RECOMPILE to the procedure’s definition

  2. scarydba said,

    Oh yeah, I hadn’t thought about watching the cache remove event. See, I said you were smarter.

    I would have used Profiler, but I wanted to play with DMV’s some more. I’m just so happy with DMV’s these days.

  3. raulsantosneto said,

    You guys are both scary DBAs! ;)

  4. Gail said,

    I’m not a DBA. ;-)

  5. scarydba said,

    For anyone else reading, I’m pretty sure I’m putting out some bad info on the trivial plans not going into the cache. Another case of my memory being poor.

  6. Peter said,

    It is a pretty brain dead implementation if you ask me. If the procedure is executed once without the ‘with recompile’, it is in the cache, so far so good.

    If you then change the procedure to use ‘with recompile’ and execute it again, the cached plan is reused AGAIN. In effect ignoring your directive to recompile upon execution. This means that as a developer you have absolutely no control over the usage of the cache or not. You might just as well consiter the’with recompile’ a non existing feature or a mere hint.

    A directive should overrule default behavior, regardless of the state the cache or server is in. And this one simply doesn’t, it acts more like a hint that can be ingores at any time by the server.

    The same happends with regular SQL statements when you use the option (recompile) clause. Here you have even less control as statments can be fired from multiple locations in an application, not all adhering to the ‘option( recompile )’. You need only one statment missing it, and all others wil have no effect!

    It reminds me of the brain dead posix design regarding file locking. There too, if one application doesn’t follow the convention, all other applications relying on locks will fail. It is like having a RDBMS where one session does “dirty writes” without locks causing all other session that are using locking and rely on it to get invalid results.

    Caching is an art as much as a science, and unfortunatly the SQL server devs have it backwards in current SQL Server implementations. Very few people seem to be able to design proper caching these days and there is nearly always a self defeating usability element present.

    End of rant ;)

  7. Mark said,

    Great explanation! Makes more sense now.

    My buddy always says…. do you think thats true or do you know thats true.

    Can’t rely on memory these days

  8. Gail said,

    Sorry Peter, but I must disagree.

    When a proc is modified (ALTER PROCEDURE), SQL removes any execution plans for that proc from cache. So an execution after altering the proc to add WITH RECOMPILE cannot use the same plan that previous calls to the proc used. The ALTER invalidates all plans.

    As for the ad-hoc query with OPTION(RECOMPILE), SQL uses a hash of the query text to match queries to plans. So
    SELECT * FROM tbl1
    and
    SELECT * FROM tbl1 OPTION(RECOMPILE)
    are completely different queries that hash to different cache buckets and could not use each other’s exec plan. Even white space is enough for two queries to get different plans

    It’s fairly easy to test this. Run profiler with the SP:CacheMiss, SP:CacheHit, SP:CacheInsert and SP:CacheRemove events to see when things are added and removed from cache.
    For the ad-hoc code, run both queries a couple times then check sys.dm_exec_query_stats for the execution count. It’ll match the number of times the one without recompile ran.

  9. scarydba said,

    Sorry Peter & Gail, I was just coming to respond. Gail’s nailed it. When you alter the procedure, it gets removed from cache, so the behavior you’re outlining doesn’t follow from there.

    As far as calling recompile from statements, yeah, you have to have it everywhere in order for it to work. That’s why, if you really do need a procedure to recompile, it’s best to create it using WITH RECOMPILE.

  10. scarydba said,

    Actually, Gail said it better, it doesn’t get removed from cache immediately, it gets marked as invalid and will be removed from cache during the next clean-up cycle. But it won’t be reused.

  11. Gail said,

    Well, the ALTER PROCEDURE causes an SP:CacheRemove event so it looks like it might get removed immediately, though I don’t know if the SP:CacheRemove event means ‘remove this plan from cache’ or ‘mark as for removal the next time the cleanup runs’

    It’s not necessary to have OPTION (RECOMPILE) on all instances of a query. The ones that have it will recompile, the ones that don’t will use a cached plan. Since they’re different queries (according to the cache lookup code) they won’t interfere with each other.

  12. Gail said,

    I also tested the EXEC … WITH RECOMPILE syntax

    Exec SomeProc causes a CacheMiss and a CacheHit event before the SP_Complete.

    EXEC SomeProc WITH RECOMPILE causes just a CacheMiss event. No CacheHit, so no reuse of the cached plan.

    The next execution of the proc without the RECOMPILE option again uses the cached plan

    So with RECOMPILE on the execution means that that and only that execution recompiles, any other executions without that specified do use the cached plan.

  13. Peter said,

    Gail, I tested it quickly just before I posted and I did see execution count bump up from one to two. To make sure I did not look wrong I tested it again and I see it behaves how you describe it.

    I guess I must have misread and looked at the inspection query instead (forgot to scroll to the end to see the actual statement).

    So me bad ;)

    The BOL desctription is misleading for the option (recompile) case:

    quote:

    Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed.

    Comment:

    It explains the plan is rebuild next time due to it not being left in the cache on the first execution and not because of the option( recompile ) itself. Which kind of implies that any suitable plan already in the cache is used when present.

    Now if the hashing of the sql is used for a plan lookup followed by a full text compare then it works as you stated Gale. BOL itself doesn’t make that clear however. And I could have sworn that many times over I seen option( recompile ) only alter its plan after a DBCC FreeProcCache during optimisation tests.

    I will keep an eye on this and see if in the future I can isolate a case. It is an interesting topic and worth going in depth on it.

  14. Log Buffer #159: a Carnival of the Vanities for DBAs | Pythian Group Blog said,

    […] Scary DBA looks into what happens when you use WITH RECOMPILE. He (or she) writes, “I saw this question and my immediate thought was ‘Well, duh, the […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: