Pre-Compiled Stored Procedures: Fact or Myth

September 30, 2009 at 9:01 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , , , )


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:

Precompile

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.

22 Comments

  1. Brent Ozar said,

    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.

  2. John Vanda said,

    Loved reading this post and seeing the CacheHit/CacheMiss/CacheRemove events in the Profiler. Thank you.

  3. SuperDBA said,

    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?

  4. scarydba said,

    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.

  5. scarydba said,

    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.

  6. SuperDBA said,

    Nooooooooooooooooooooo!
    that which we thought to be true is not!

  7. KevRiley said,

    Why is there a SP:CacheMiss, after the SP:CacheInsert (and subsequent batch complete) and before the SP:CacheHit?

  8. scarydba said,

    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.

  9. scarydba said,

    Deep, slow breaths there SuberDBA. You’ll be fine.

    Ha!

  10. KevRiley said,

    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…….

  11. scarydba said,

    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.

  12. Gail said,

    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….

  13. scarydba said,

    Oh. Is it really? Cool. How would you decode the hash? Or do I need to wait for the blog post?

  14. Gail said,

    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……

  15. Jason Strate said,

    Very nice. Such a common misconception.

  16. James said,

    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.

  17. scarydba said,

    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.

  18. SuperDBA said,

    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?

  19. scarydba said,

    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.

  20. SuperDBA said,

    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.

  21. Gail said,

    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.

  22. SuperDBA said,

    Thanks Gail! That solidifys alot of what I thought!

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: