sp_sysmon reports on statement caching and stored procedure executions. The statement cache is monitored by the following counters:
Statements Found in Cache – the number of times a query plan was reused. A low number of cache hits may indicate the statement cache is too small.
Statements Not Found – indicates a lack of repeated SQL statements. The sum of statements found in cache and statements not found is the total number of eligible SQL statements submitted.
Statements Cached – the number of SQL statements added to the cache. This is typically the same number as Statements Not Found. Smaller values for statements cached means the statement cache is full of active statements.
Statements Dropped – the number of statements that were dropped from the cache. A high value may indicate an insufficient amount of procedure cache memory or that you have configured a statement cache size that is too small.
Statements Restored – the number of query plans regenerated from the SQL text. High values indicate an insufficient procedure cache size.
Statements Not Cached – the number of statements Adaptive Server would have cached if the statement cache were enabled. However, Statements Not Cached does not indicate how many unique SQL statements would be cached.
This is sample output from sp_sysmon:
Procedure Cache Management per sec per xact count % of total -------------------------- ----------- ------------ ---------- ---------- Procedure Requests 6.6 3.7 33 n/a Procedure Reads from Disk 1.0 0.6 5 15.2% Procedure Writes to Disk 0.4 0.2 2 6.1% Procedure Removals 2.6 1.4 13 n/a Procedure Recompilations 0.8 0.4 4 n/a Recompilations Requests: Execution Phase 0.6 0.3 3 75.0% Compilation Phase 0.2 0.1 1 25.0% Execute Cursor Execution 0.0 0.0 0 0.0% Redefinition Phase 0.0 0.0 0 0.0% Recompilations Reasons: Table Missing 0.6 0.3 3 n/a Temporary Table Missing 0.2 0.1 1 n/a Schema Change 0.0 0.0 0 n/a Index Change 0.0 0.0 0 n/a Isolation Level Change 0.2 0.1 1 n/a Permissions Change 0.0 0.0 0 n/a Cursor Permissions Change 0.0 0.0 0 n/a SQLStatement Cache: Statements Cached 0.0 0.0 0 n/a Statements Found in Cache 0.7 0.0 2 n/a Statements Not Found 0.0 0.0 0 n/a Statements Dropped 0.0 0.0 0 n/a Statements Recompiled 0.3 0.0 1 n/a Statements Not Cached 1.3 0.0 4 n/a
