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.
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
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.
You guys are both scary DBAs! 😉
I’m not a DBA. 😉
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.
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 😉
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
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.
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.
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.
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.
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.
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.
[…] 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 […]
So…what happens when you use WITH RECOMPILE ?
Looks like you had a nice discussion with yourself and in the end you say, see, Gail was right. So what’s the answer? And who is Gail?
This is the top Google hit, so please try posting concise, useful information.