monCachedStatement

Description

Stores detailed monitoring information about the statement cache, including information about resources used during the previous executions of a statement, how frequently a statement is executed, the settings in effect for a particular plan, the number of concurrent uses of a statement, and so on. This information can be helpful when troubleshooting, and when deciding which statements to retain in the cache.

The columns in monCachedStatement allow two attributes: “counter” if the column has a counter value, and “reset” if you can reset the column using sp_sysmon.

Enable the enable monitoring configuration parameter, and set the statement cache size parameter greater than 0 for this monitoring table to collect data.

Columns

The columns for monCacheStatement are:

Names

Datatypes

Attribute

Description

SSQLID

int

Unique identifier for each cached statement. This value is treated as a primary key for monCachedStatement, and is used in functions.

show_cached_text uses SSQLID to refer to individual statements in the cache.

HashKey

int

Hash value of the SQL text of the cached statement. A hash key is generated based on a statement’s text, and can be used as an approximate key for searching other monitoring tables.

UserID

int

User ID of the user who initiated the statement that has been cached.

SUserID

int

Server ID of the user who initiated the cached statement.

DBID

int

Database ID of the database from which the statement was cached.

DBName

varchar(30)

Null

Name of database from which the statement was cached.

CachedDate

datetime

Timestamp of the date and time when the statement was first cached.

LastUsedDate

datetime

Tmestamp of the date and time when the cached statement was last used. Use this information with CachedDate to determine how frequently this statement is used, and whether it is helpful to have it cached.

CurrentUsageCount

int

Counter

Number of concurrent users of the cached statement.

StatementSize

int

Size of the cached statement, in bytes.

MaxUsageCount

int

Counter

Maximum number of times the cached statement’s text was simultaneously accessed.

SessionSettings

These session-level settings are associated with each cached statement.

ParallelDegree

Degree of parallelism used by the query that is stored for this statement

QuotedIdentifier

Specifies whether the plan compiled with set quoted_identifier is enabled.

TransactionIsolationLevel

Transaction isolation level for which the statement was compiled.

TransactionMode

Specifies whether “chained transaction mode” is enabled for the statement.

SAAuthorization

Specifies whether the plan was compiled with sa_role authorization.

SystemCatalogUpdates

Specifies whether allow catalog updates was enabled when the plan was compiled.

ExecutionMetrics

Execution costs are collected when the cached plan is used. These costs are measured in terms of values for logical I/O (LIO) and physical I/O (PIO), execution, and elapsed times.

The metrics that ExecutionMetrics reports are the same as those reported by QP metrics, however, the data reported in this table does not require that you enable QP metrics. monCachedStatement captures the metrics independently for the cached statements, regardless of Adaptive Server metrics capture settings.

MetricsCount

Number of times metrics were aggregated for this statement.

MaxElapsedTime

int

Maximum elapsed execution time for this statement.

MinElapsedTime

int

Minimum elapsed execution time for this statement.

AvgElapsedTime

int

Average elapsed execution time for this statement.

MaxLIO

int

Maximum logical I/Os that occurred during any one execution of this statement.

MinLIO

int

Minimum logical I/Os that occurred during any execution of this statement.

AvgLIO

int

Average number of logical I/Os that occurred during execution of this statement.

MaxPIO

int

Maximum physical I/Os that occurred during any execution of this statement.

MinPIO

int

Maximum physical I/Os that occurred during any execution of this statement.

AvgPIO

int

Average number of physical I/Os that occurred during execution of this statement.

NumRecompilesPlanFlushes

int

Counter

Number of times the cached statement was recompiled because a plan was not found in the cache.

NumRecompilesSchemaChanges

int

Counter

Number of times the statement was recompiled due to schema changes. Running update statistics on a table may result in changes to the best plan. This change is treated as a minor schema change.

Recompiling a statement many times indicates that it is not effective to cache this particular statement, and that you may want to delete the statement from the statement cache to make space for some other, more stable, statement.

MaxPlanSize

int

Size of the plan when it is in use, in kilobytes.

MinPlanSize

int

Size of the plan when it is not in use, in kilobytes.

LastRecompiledDate

datetime

Date when the statement was last recompiled, because of schema changes or because the statement was not found in the statement cache.

UseCount

int

Number of times the statement was accessed after it was cached.

HasAutoParams

boolean

“true” if the statement has any parameterized literals, “false” if it does not.

OptimizationGoal

varchar(30)

The optimization goal stored in the statement cache.

OptimizerLevel

varchar(30)

The optimizer level stored in the statement cache.