SQL Server compile and recompile demystified (?)

Ever wondered what the difference is between a SQL Server compile and a SQL Server (re)compile? Wonder no more! Or rather wonder on …  Microsoft has made a mess of explaining the difference in my opinion. But let me try to sort it out, as far as I have been able to deduce the truth.

Compile: Means one of two things, either you compile something for the first time. OR you compile something again that has already been compiled, but the plan is deemed unfit for reuse by the current session. For example due to differences in SET options. So if you trace the events in Profiler, you will get something like: SP:StmtStarting SP:ChacheRemove SP:LockAquired [COMPILE] SP:ChacheInsert SP:StmtStarting (Again)

Note: No SP:Recompile event

Recompile: Recompile occurs while running a statement. Which means that you compile something again that has already been compiled before. Sounds familiar? Profiler will look like: SP:StmtStarting SP:ChacheRemove SP:LockAquired [COMPILE] SP:Recompile SP:ChacheInsert SP:StmtStarting (Again) Again, looks familiar? 🙂

Hope this can clear things up a bit! For a good, but not entirely complete dive into the different causes of (re)compile, have a look at http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx