I’ve looked at cache hit and miss events and at the cache insert and remove events. The only cache-monitoring event (of current interest) left is the recompile event.

There are two recompile events available in Profiler in SQL 2008

• SP:Recompile under Stored Procedures
• SQL:StmtRecompile under T-SQL

Which to use when?

Books Online has the following to say on the two events.

The SQL:StmtRecompile event class indicates statement-level recompilations caused by all types of batches: stored procedures, triggers, ad hoc batches, and queries. Starting in SQL Server 2005, the SQL:StmtRecompile event class should be used instead of the SP:Recompile event class.

and

The SP:Recompile event class indicates that a stored procedure, trigger, or user-defined function has been recompiled. In SQL Server 2005 and later, recompilations reported by this event class occur at the statement level, whereas those in SQL Server 2000 occurred at the batch level.

In SQL Server 2005 and later, the preferred way to trace statement-level recompilations is to use the SQL:StmtRecompile event class. Starting in SQL Server 2005, the SP:Recompile event class is deprecated.

So it appears that they show the same thing and SP:Recompile is deprecated. That simplifies the entire situation, the only one that I’m going to look at in that case is the SQL:StmtRecompile event.

So what does the event look like?

Not too different from the other cache events. One difference is that the ad-hoc SQL statement no longer has an Object ID, only the stored procedure has an Object ID and it matches (as one would expect) with the Object ID in the system tables.

Another difference is that if the statement being recompiled is part of a procedure, the stored procedure name does not appear in the TextData column, rather the statement does. The way to tell whether the recompile was ad-hoc code or part of a procedure is to look at the ObjectID and ObjectName columns, which are only populated if the recompiled statement was part of a procedure (or trigger or function, etc)

One column that’s important here is the EventSubClass. The different values of this column, nicely interpreted by Profiler, give the reason that the recompile was necessary.

As per Books Online, the EventSubClass can have the following values.

1. Schema changed
2. Statistics changed
3. Deferred compile
4. Set option changed
5. Temp table changed
6. Remote rowset changed
7. For Browse permissions changed
8. Query notification environment changed
9. Partition view changed
10. Cursor options changed
11. Option (recompile) requested

Some of those are not occurrences that will be encountered all that often, and I have found at least one that’s not on that list. (I remember seeing a ’12′ in the subclass column once, but can’t recall what the description was)

So, now that we’ve seen what the event looks like, let’s see when it fires. The event subclass gives a good list of the causes, I’m not going to go over all of the subclasses, just some of the more common (and easier to demo). I won’t guarantee that what follows is a comprehensive list of everything that causes the various recompiles, I’m sure to miss something.

Using the same example table as previous cache-investigation posts did.

Schema Changes

This recompile occurs when any of the base tables for the query have changed, when any of the indexes for the query have been rebuild or changed and when sp_recompile is run for any of the base tables.

It does not occur for a procedure if that procedure is altered, nor does it appear if sp_recompile is run for the procedure. Both of those result in a sp:CacheRemove event.

Statistics Changed

Fairly obvious. The recompile occurred because statistics that the statement in question used have changed.

The update of some rows is necessary to get the recompile. If the stats are updated and there were no data modifications made since the previous update stats, the recompile does not occur.

This recompile also occurs when an autoupdate of the stats that the query uses has occurred.

Deferred compile

A deferred compile occurs when an object referenced by a statement within the batch does not exist when the batch is first compiled.

CREATE PROCEDURE [dbo].[TestingCacheEvents2]
AS
SELECT ID, SomeDate, Status
FROM TestingCacheEvents
WHERE Status = 'G'
CREATE TABLE #Temp (Status char(2), CountStatus int)
INSERT INTO #Temp
SELECT status, count(*)
FROM TestingCacheEvents
GROUP BY Status
DROP TABLE #Temp

Now in SQL 2000 and earlier, this form of procedure would have recompiled (entirely) on every execution due to the creation of the temp table part way through. If the procedure ran frequently, this could have a devastating effect on performance. In SQL 2005 and above however this does not happen. As can be seen in the following screenshot, the second execution does not incur a recompile.

Set option changed

This one occurs when a set option is changed within the procedure, not outside the procedure.

CREATE PROCEDURE [dbo].[TestingCacheEvents3]
AS
SELECT ID, SomeDate, Status
FROM TestingCacheEvents
WHERE Status = 'G'
SET ARITHABORT OFF
SELECT DATEDIFF(dd,SomeDate,GETDATE())
FROM TestingCacheEvents
WHERE Status = 'G'

Option Recompile requested

If this one isn’t obvious, then I don’t know what could be.

CREATE PROCEDURE [dbo].[TestingCacheEvents5]
AS
SELECT ID, SomeDate, Status
FROM TestingCacheEvents
WHERE Status = 'G'
SELECT Status, COUNT(*)
FROM TestingCacheEvents
GROUP BY Status
OPTION (RECOMPILE)

That covers all the cache-monitoring events that I want to look at. Now I can get onto some fun stuff that uses this.