Stored Procedures Event Classes
The following table describes the Stored Procedures event classes in the Stored Procedures event category.
| Event class | Description |
|---|---|
| RPC Output Parameter | Displays information about output parameters of a previously executed remote procedure call (RPC). |
| RPC:Completed | Occurs when an RPC has been completed. |
| RPC:Starting | Occurs when an RPC has started. |
| 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 executed stored procedures are found in the cache. For example, if the SP:CacheMiss event class occurs frequently, 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), removed from the cache (aged out of the cache), and 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.
A stored procedure has a compiled version with shared data and an execution context version with 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.
The execution of a stored procedure can be monitored by the SP:Starting, SP:StmtStarting, SP:StmtCompleted, and SP:Completed event classes and all the TSQL event classes.
Note SP:StmtStarting is provided for backward compatibility only. You should now use SQL:StmtStarting to trace this event. If you do choose to trace SP:StmtStarting, SQL Profiler will trace SQL:StmtStarting, as the two events are mapped together.