The SQL Server-Procedure Cache object is a link list of all procedures. The procedure cache holds entries for each procedure and other maintenance overhead. Each entry in the procedure cache points to one or more buffers in the procedure buffer. The procedure buffer is where the procedure instructions are stored.
The figure below conceptually illustrates:
As previously stated, a procedure cache entry will have one or more procedure buffers associated with it.
The key at the bottom of the figure shows the possible states for both the procedure cache and the procedure buffer.
Procedure A is currently in an active and used state. As you can see from the example, the procedure cache that is labeled A has three procedure buffers associated with it. Based on its current state, Procedure A will contribute to the following counters:
Procedure B is currently in a used state. As you can see from the example, the procedure cache that is labeled B has two procedure buffers associated with it. Based on its current state, Procedure B will contribute to the following counters:
Procedure C will also contribute to the counters listed for Procedure B, because it is also in a used state.
The following counters are in the SQLServer-Procedure Cache object.
| Counter | Description |
|---|---|
| Max Procedure Buffers Active % | Specifies the maximum percentage of procedure buffers that have been active at one time during the monitoring session. A buffer is considered to be active when it contains a procedure that is currently executing. |
| Procedure Buffers Active % | Specifies the percentage of procedure buffers that are currently active. When a buffer contains a currently executing procedure, the buffer is said to be active. |
| Max Procedure Buffers Used % | Specifies the maximum percentage of procedure buffers that have been used at one time during the monitoring session. A procedure buffer is considered used when it is associated with a procedure cache entry. A used procedure buffer may or may not be active. |
| Procedure Buffers Used % | Specifies the percentage of procedure buffers that are used in the procedure cache. A procedure buffer is considered used when it is associated with a procedure cache entry. A used procedure buffer may or may not be active. |
| Total Procedure Buffers | Specifies the number of procedure buffers that are in the procedure cache. This number is a fixed number that is based on the procedure cache that is allocated. |
| Max Procedure Cache Active % | Specifies the maximum percentage of procedure cache that has been active during the monitoring session. A procedure cache entry is considered to be active when its associated procedure is currently executing. |
| Procedure Cache Active % | Specifies the percentage of the procedure cache that is currently active. When the procedure that is associated with a procedure cache entry is currently executing, the procedure cache entry is said to be active. |
| Max Procedure Cache Used % | Specifies the maximum percentage of procedure cache that has been used during the monitoring session. A procedure cache entry is considered used when it is currently allocated. A used procedure cache entry may or may not be active. |
| Procedure Cache Used % | Specifies the percentage of procedure cache that is used. This number indicates the percentage of slots that are currently associated with any procedure. |
| Procedure Cache Size | Specifies the size, in 2K pages, of the procedure cache. The size of the procedure cache can fluctuate depending on the activity of other SQL Server processes that may require procedure cache slots. |