The Stored Procedures event classes can be used to monitor the execution of stored procedures.
| Event class | Description |
|---|---|
| SP:CacheHit | Procedure is found in the cache. |
| SP:CacheInsert | Item is inserted into the procedure cache. |
| SP:CacheMiss | Stored procedure is not found in the procedure cache. |
| SP:CacheRemove | Item has been removed from the procedure cache. |
| SP:Completed | Stored procedure has completed. |
| SP:ExecContextHit | Execution version of a stored procedure has been found in the cache. |
| SP:Recompile | Stored procedure has been recompiled. |
| SP:Starting | Stored procedure has started. |
| SP:StmtCompleted | Statement within a stored procedure has completed. |
| SP:StmtStarting | Statement within a stored procedure has started. |
By monitoring the SP:CacheHit and SP:CacheMiss event classes, you can determine how often stored procedures are found in the cache when executed. If, for example, the SP:CacheMiss event class occurs frequently, then it can indicate that more memory should be made available to Microsoft® SQL Server™, thereby increasing the size of the procedure cache. By monitoring the Object ID of the SP:CacheHit event class, you can determine which stored procedures reside in the cache.
The SP:CacheInsert, SP:CacheRemove, and SP:Recompile event classes can be used to determine which stored procedures are brought into cache (first executed), then later removed from the cache (aged out of the cache), and also when they get recompiled. For more information about recompiling stored procedures, see Recompiling a Stored Procedure. This information is useful to determine how stored procedures are being used by applications.
By monitoring the SP:Starting, SP:StmtStarting, SP:StmtCompleted, and SP:Completed event classes and all the TSQL event classes, the execution of a stored procedure can be monitored.
A stored procedure has a compiled version that has shared data and an execution context version that has session specific data. When a stored procedure is looked up in the cache, execution contexts are looked for first. If none are found, the cache is searched for compiled plans. Use the SP:ExecContextHit event class to monitor execution contexts. If the SP:ExecContextHit event class is not generated for a stored procedure, then the stored procedure has no execution time cachable queries.
These are the event-specific data columns for the Stored Procedures event category.
| Event class | Data column | Description |
|---|---|---|
| SP:CacheHit | Event Sub Class | Nesting level of the stored procedure. |
| Object ID | System-assigned ID of the stored procedure. | |
| SP:CacheInsert | Event Sub Class | Nesting level of the stored procedure. |
| Object ID | System-assigned ID of the stored procedure. | |
| SP:CacheMiss | Event Sub Class | Nesting level of the stored procedure. |
| Object ID | System-assigned ID of the stored procedure. | |
| SP:CacheRemove | Event Sub Class | Nesting level of the stored procedure. A value of 0 indicates that the stored procedure was explicitly removed from the buffer pool. |
| Object ID | System-assigned ID of the stored procedure. | |
| SP:Completed | Event Sub Class | Nesting level of the stored procedure. |
| Object ID | System-assigned ID of the stored procedure. | |
| Text | Text of the stored procedure. | |
| SP:ExecContextHit | Event Sub Class | Nesting level of the stored procedure. |
| Object ID | System-assigned ID of the stored procedure. | |
| SP:Recompile | Event Sub Class | Nesting level of the stored procedure. |
| Object ID | System-assigned ID of the stored procedure. | |
| SP:Starting | Event Sub Class | Nesting level of the stored procedure. |
| Object ID | System-assigned ID of the stored procedure. | |
| Text | Text of the stored procedure. | |
| SP:StmtCompleted | Event Sub Class | Nesting level of the stored procedure. |
| Integer Data | Actual rows returned by the statement. | |
| Object ID | System-assigned ID of the stored procedure. | |
| Text | Text of the statement in the stored procedure. | |
| SP:StmtStarting | Event Sub Class | Nesting level of the stored procedure. |
| Object ID | System-assigned ID of the stored procedure. | |
| Text | Text of the statement in the stored procedure. |