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.
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.
Loved reading this post and seeing the CacheHit/CacheMiss/CacheRemove events in the Profiler. Thank you.
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?
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.
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.
Nooooooooooooooooooooo!
that which we thought to be true is not!
Why is there a SP:CacheMiss, after the SP:CacheInsert (and subsequent batch complete) and before the SP:CacheHit?
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.
Deep, slow breaths there SuberDBA. You’ll be fine.
Ha!
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…….
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.
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….
Oh. Is it really? Cool. How would you decode the hash? Or do I need to wait for the blog post?
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……
Very nice. Such a common misconception.
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.
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.
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?
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.
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.
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.
Thanks Gail! That solidifys alot of what I thought!
here is no such thing as a compile, what does the recompile event indicate then?
Plywood, there is a compile, there just isn’t a pre-compile. When you create a stored procedure in SQL Server, other than a syntax check, nothing else happens until that stored procedure is called. Then it gets compiled.
“It’s compiled after the first run, yes. But that’s not precompiled. Every query gets compiled after it’s first run”
But they still compile (if only after the first call). ORM ad hoc calls dont compile do they? Doesnt that mean there is an inherit performance penalty in ORM.
At the moment im trying to decide on wether to use SPs or ORM. I have some experience using Nhibernate but i found I was spending aboutjust as much time figuring out the XML en specifics of Nhibernate as I was using regular SP’s
To put it in other words: If i want performance shoudl i use ORM? If I want speed of development and maintainability should I use ORM? Thanks in advance for your advice.
regards,
Hein
PS, pls excuse my English, I’m no native speaker (Dutch actually)
Hello Hein,
Yes, ORM queries do get compiled. All queries get compiled. So no, there isn’t an inherent performance penalty in ORM. Further, a tool like nHibernate will create parameterized queries, which is basically the same thing as a stored procedure. So far, it’s all wins for ORM… But, depending on how you program your ORM software (and nHibernate is notorious) you can get different sized parameters for ‘pig’, ‘horse’, and ‘elephant’ which causes you to have three different execution plans. That’s a fail.
Assuming a well-coded ORM that doesn’t muck up the parameters, for the most part, an ORM tool is neither slower, nor faster than stored procedures. But, the code generated can be lowest common denominator TSQL, which, for complicated SELECT statements can mean radically slower performance. That, and a number of other reasons, is why I advocate for a mixed approach. Most stuff, the easy stuff, can, and should, be done through the ORM tool. Complicated stuff goes back to stored procs.
And your English is fine. Thanks for commenting. If you search my blog I talk about ORM tools in several other posts.
Thanks for the Article, This question came in an Interview and I remembered the right answer 😀 Thanks a lot.