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.