Pre-Compiled Stored Procedures: Fact or Myth
There’s a very common belief among DBA’s: Stored procedures are pre-compiled. I had someone tell me all about it just last week. There are lots of sources that say what I’m getting ready to say, so this really shouldn’t come as news to anyone,but it will. No, they’re not. Stored procedures are not pre-compiled.
When you create or update a stored procedure on the server, it gets a syntax check. But there is no compile process, let alone a pre-compile process. That stored procedure exists exactly as it is named, a procedure that is stored on the server. It’s just a collection of TSQL code, possibly with some parameters, waiting to be called. Until it’s called, it is not ready to be executed. Don’t believe me? I’m not a huge sports fan (except for MMA), but I’m willing to use a common sports expression. Let’s go to the slow motion instant replay, also known as trace events.
I’m going to run each one of the following statements, one at a time and capture a set of trace events, also listed below. First the code:
CREATE PROCEDURE dbo.MyTest AS SELECT * FROM sys.dm_exec_query_stats AS deqs ; GO EXEC dbo.MyTest ; GO EXEC dbo.MyTest ; GO DROP PROCEDURE dbo.MyTest ; GO
The events I’m capture are:
- SQL:BatchCompleted
- SP:CacheMiss
- SP:CacheHit
- SP:CacheInsert
- SP:CacheRemove
After I run the script, here is what is visible in the trace:
With the first statement to create the procedure, you see that there was a CacheMiss event, since the DDL statement isn’t cached. You’ll get a miss event every time this runs. Next, the execute statement runs and you can see another CacheMiss event followed immediately by a CacheInsert event. This is the creation of the execution plan and it’s addition to the cache. The statement completes and the second execute statement fires. This gets a CacheHit event, since the last execute created a plan, and then it completes. The final statement, again, DDL, so you see a CacheMiss event and then the CacheRemove event as the procedure is dropped.
Nothing was added to the cache until the procedure was called. There was no pre-compile process. None. To quote a popular television show, myth busted.
Home of the Scary DBA 
Brent Ozar said,
September 30, 2009 at 9:43 am
Here’s why I tend to use that term – I want people to think of them as units that have fairly consistent execution plans, as opposed to ad-hoc T-SQL that may have to have a plan built each time due to minute differences. But you’re right – I gotta remove that term from my vocabulary.
John Vanda said,
September 30, 2009 at 9:49 am
Loved reading this post and seeing the CacheHit/CacheMiss/CacheRemove events in the Profiler. Thank you.
SuperDBA said,
September 30, 2009 at 10:47 am
Yes – but isn’t it pre-compiled after it runs the first time?
Otherwise – what’s the point of the recompile event?
I would have expected to see that in the trace the first time it was executed?
scarydba said,
September 30, 2009 at 11:48 am
Then you’re going to love the next post Brent. I’m going to poke a hole in the proc vs. ad hoc SQL myth too.
scarydba said,
September 30, 2009 at 11:51 am
It’s compiled after the first run, yes. But that’s not precompiled. Every query gets compiled after it’s first run (except trivial plans, DDL), so that’s nothing special. A recompile event is when the plan has, for various reasons, been marked for recompile.
The argument people have been making, incorrectly, for years is that stored procedures are pre-compiled. Meaning, they’re compiled before they’re executed. But, they’re not. They’re compiled at execution, but so are ad hoc queries.
SuperDBA said,
September 30, 2009 at 11:52 am
Nooooooooooooooooooooo!
that which we thought to be true is not!
KevRiley said,
October 1, 2009 at 4:41 am
Why is there a SP:CacheMiss, after the SP:CacheInsert (and subsequent batch complete) and before the SP:CacheHit?
scarydba said,
October 1, 2009 at 6:57 am
Good question. That’s because the batch itself, not simply the procedure’s execution plan, may have a cache entry. In this case, the batch itself is trivial, so no plan is stored. So, for the batch, a cachemiss, but for the procedure, a cachehit. It’s stuff like that which can lead people to the conclusion that “procedures are precompiled” but that’s not what’s going on.
scarydba said,
October 1, 2009 at 6:58 am
Deep, slow breaths there SuberDBA. You’ll be fine.
Ha!
KevRiley said,
October 1, 2009 at 8:48 am
are there other columns to include in profiler to say what was ‘missed’, or(perhaps better) say what was ‘hit’ – as the TextData for SP:CacheHit is blank?
It manages to tell you what was inserted and removed…….
scarydba said,
October 1, 2009 at 10:33 am
Not that I recall. You would have a coordination between the SPID’s, the execution time, or even statement execution completion as a way to understand the cache hits.
Gail said,
October 2, 2009 at 5:10 am
Kev, the database ID and ObjectID are populated. That’s enough to identify what proc/function was the target of the cache hit/insert/remove. (most of the time anyway)
Won’t help with ad-hoc SQL though. There the ‘objectid’ is (I believe) a hash of the query text.
Hmm…. maybe worth a blog post on this….
scarydba said,
October 2, 2009 at 6:21 am
Oh. Is it really? Cool. How would you decode the hash? Or do I need to wait for the blog post?
Gail said,
October 2, 2009 at 7:13 am
I have absolutely no idea how to match the ad-hoc SQL to the ‘objectID’ that appears in Profiler. I suspect it’s the ‘hash’ of the query text that SQL uses to see if there’s an existing plan for the ad-hoc statement exists. It doesn’t appear (that I can see) in the plan cache DMVs though. There object_id is null for ad-hoc.
I was going to blog just on the case where it’s a stored proc, but now……
Jason Strate said,
October 5, 2009 at 11:02 pm
Very nice. Such a common misconception.
James said,
October 7, 2009 at 1:15 am
Good post!!
IMHO
The statement is right, but it’s not been proved by the 5 events set in the profiler. There’s not an event called pre-compile or compile, how can we know pre-compiling is done or not.
scarydba said,
October 7, 2009 at 6:32 am
Well, I guess the whole concept is flawed. There is no such thing as a compile in SQL Server. There are execution plans. These are created and stored in cache. Evidence of their creation is shown by the CacheInsert event.
SuperDBA said,
October 7, 2009 at 6:37 am
Hmmm. if there is no such thing as a compile, what does the recompile event indicate then?
and the what do the sp_recompile sprocs do? and the definition with recompile on an object?
I’m assuming by recompile they really mean figure out weather to use the cached (or stored) execution plan or figure out a new one?
scarydba said,
October 7, 2009 at 7:54 am
Oh nuts. You got me. I forgot about the recompile event. That’s why you’re Super and I’m just Scary. I was trying to weasel out on the language. Unfortunately Microsoft isn’t completely consistent in what they call this.
Call it what it you want, compile, build, insert… there’s a process that creates execution plans. When you run to the script that creates a query that gets stored within the datbase, aka stored procedure, the process that creates execution plans is not called. No compile/build/create/insert event occurs. We can discuss the symantics all day (and go in circles since MS is a bit vague with the names or processes), but at the core, we’re still not going to see this process, call it what you will, run during the act of saving/creating a stored procedure. It only occurs when that stored procedure is run. Just as it occurs when an ad hoc query is run or when a parameterized query is run.
BTW, telling a procedure to recompile either through a call so sp_recompile or through the recompile query hint or as a part of the process within SQL Server which recompiles procedures due to changes in data, all these simply mark an execution plan as invalid. It doesn’t get reused and the execution plans is recreated/recompiled/reinserted/rewhatevered.
SuperDBA said,
October 7, 2009 at 8:01 am
Yah I remember the sp_recompile proc being confusing, basically if you ran it against an object like a table then all the objects (views, sprocs) that used that table would ‘recompile’ the next time they were called.
I guess it’s marking the plans invalid and makeing sql create new ones.
Gail said,
October 7, 2009 at 11:28 am
The recompile event indicates that a query/proc has started executing and that during execution the execution plan been used is found to be invalid (for whatever reason). The execution plan is discarded and a new one is generated.
sp_recompile tells SQL that the executions plan of the object (in the case of a stored procedure) or all objects that reference the object (in the case of tables or views) are invalid and need to be regenerated next time the query/procedure runs.
WITH RECOMPILE means do not cache an execution plan, regenerate the plan upon every execution. Grant’s got a recent blog post on that.
SuperDBA said,
October 7, 2009 at 11:29 am
Thanks Gail! That solidifys alot of what I thought!