Or “Monitoring plan cache usage”
For people interested in the details of how SQL is using and reusing execution plans, there are some useful events in profiler for watching this in detail, under the Stored procedure group:
Additionally there’s the SQL:StmtRecompile event under the TSQL group.
For now, I just want to look briefly at the CacheMiss and CacheHit events.
One word of caution early on, these are frequently occurring events and it may not be a good idea to trace these on busy production servers. If you do need to, keep the duration of the trace short and the columns to a minimum.
The cache miss event fires any time SQL looks for the execution plans for an object or ad-hoc batch and does not find it in the plan cache.
For an object (scalar function, multi-statement table-valued function, stored procedure or trigger) the match is done on the object ID (along with some of the connection’s SET options and possibly the database user and c couple other factors1). For an ad-hoc batch, the match is done on a hash of the text of the batch (along with some of the connection’s SET options and possibly the database user)
When testing stored procedures from Management Studio (or another SQL querying tool), two CacheMiss events will appear in the trace.
What’s going on here?