The monCachedStatement table

Table 12-2 provides information about the name, the SQL datatype, attributes and description for each column of monCachedStatement. The columns in monCachedStatement allow two attributes, “counter” if the column has a counter value, and “reset” if the column can be reset using mechanisms like sp_sysmon.

Use the contents of monCachedStatement to find detailed information about specific cached statements, 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, number of concurrent uses of a statement and so on. This information is helpful when troubleshooting, and when deciding which statements are to be retained in the cache.

**Table 12-2: monCachedStatement**
Name		Datatype	Attributes	Description
SSQLID		int		SSQLID contains a unique identifier for each cached statement. This value is treated as a primary key for monCachedStatement, and is used in the built-in functions. For more information about the built-in functions, see “Displaying the SQL plan for cached statements”. show_cached_plan and show_cached_text both use SSQLID to refer to individual statements in the cache.
HashKey		int		This column contains the 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 catalogs.
UserID		int		Contains the user ID of the user who initiated the statement that has been cached. Cached plans cannot be re-used if the user ID does not match.
SUserID		int		Contains the server ID of the user who initiated the cached statement. Cached plans cannot be reused if the server ID does not match.
DBID		int		Contains the database ID of the database from which the statement was cached. Cached plans cannot be reused if the database ID does not match.
CachedDate		datetime		Contains the timestamp of the date and time when the statement was first cached.
LastUsedDate		datetime		Contains the timestamp of the date and time when the cached statement was last used. Use this information in conjunction with the CachedDate to determine how frequently this statement is used, and whether it is helpful to have it cached.
CurrentUsageCount		int	Counter	Displays the number of concurrent users of the cached statement.
StatementSize		int		Displays the size of the cached statement in bytes.
MaxUsageCount		int	Counter	Displays the maximum number of times the cached statement’s text was simultaneously accessed.
SessionSettings				Along with the text, parameters, DBID, UserID and so on, the following session settings must also match when the cache is searched.
	ParallelDegree	byte
	QuotedIdentifier	boolean
	TableCount	byte
	TransactionIsolationLevel	byte
	TransactionMode	byte
	SAAuthorization	boolean
	SystemCatalogUpdates	boolean
ExecutionMetrics				Execution costs are incurred when the cached plan is used. These costs are measured in terms of the LIO(logical IO) and PIO(physical IO) values, execution, and elapsed times. This information can also be obtained from the captured query processing metrics, but this requires metrics capture to be explicitly enabled. In this table, the metrics are independently captured for the cached statements regardless of Adaptive Server’s metrics capture settings.
	MetricsCount	int
	MaxExecTime	int
	MinExecTime	int
	AvgExecTime	int
	MaxElapsedTime	int
	MinElapsedTime	int
	AvgElapsedTime	int
	MaxLIO	int
	MinLIO	int
	AvgLIO	int
	MaxPIO	int
	MinPIO	int
	AvgPIO	int
NumRecompilesPlanFlushes		int	Counter	Contains the number of times the cached statement was recompiled because a plan was not found in the cache.
NumRecompilesSchemaChanges		int	Counter	Contains the 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		Contains the size of the plan when it is in use, in KB.
MinPlanSize		int		Contains the size of the plan when it is not in use, in KB.
LastRecompiledDate		datetime		Contains the date when the statement was last recompiled, either due of schema changes or because the statement was not found in the statement cache.
UseCount		int		Contains the number of times the statement was accessed after it was cached.
HasAutoParams		boolean		This column contains either true or false. It is true if the statement has any parameterized literals.