Eksportuj (0) Drukuj
Rozwiń wszystko
EN
Ta zawartość nie jest dostępna w wymaganym języku. Wersja w języku angielskim znajduje się tutaj.

sys.dm_exec_cached_plans

Updated: April 2, 2011

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see sys.dm_exec_cached_plans.

Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

In Microsoft Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn’t belong to the connected tenant is filtered out. In addition, the values in the following columns are filtered; the column value is set to NULL.

  • memory_object_address

  • pool_id

 

Column name Data type Description

bucketid

int

ID of the hash bucket in which the entry is cached. The value indicates a range from 0 through the hash table size for the type of cache.

For the SQL Plans and Object Plans caches, the hash table size can be up to 10007 on 32-bit systems and up to 40009 on 64-bit systems. For the Bound Trees cache, the hash table size can be up to 1009 on 32-bit systems and up to 4001 on 64-bit systems. For the Extended Stored Procedures cache the hash table size can be up to 127 on 32-bit and 64-bit systems.

refcounts

int

Number of cache objects that are referencing this cache object. Refcounts must be at least 1 for an entry to be in the cache.

usecounts

int

Number of times the cache object has been looked up. Not incremented when parameterized queries find a plan in the cache. Can be incremented multiple times when using showplan.

size_in_bytes

int

Number of bytes consumed by the cache object.

memory_object_address

varbinary(8)

Memory address of the cached entry. This value can be used with sys.dm_os_memory_objects to get the memory breakdown of the cached plan and with sys.dm_os_memory_cache_entries_entries to obtain the cost of caching the entry.

This column value is filtered. The value is set to NULL.

cacheobjtype

nvarchar(34)

Type of object in the cache. The value can be one of the following:

  • Compiled Plan

  • Compiled Plan Stub

  • Parse Tree

  • Extended Proc

  • CLR Compiled Func

  • CLR Compiled Proc

objtype

nvarchar(16)

Type of object. The value can be one of the following:

 

Value Description

Proc

Stored procedure

Prepared

Prepared statement

Adhoc

Ad hoc query1

ReplProc

Replication-filter-procedure

Trigger

Trigger

View

View

Default

Default

UsrTab

User table

SysTab

System table

Check

CHECK constraint

Rule

Rule

plan_handle

varbinary(64)

Identifier for the in-memory plan. This identifier is transient and remains constant only while the plan remains in the cache. This value may be used with the following dynamic management functions:

sys.dm_exec_sql_text

sys.dm_exec_query_plan

sys.dm_exec_plan_attributes

pool_id

int

The ID of the resource pool against which this plan memory usage is accounted for.

This column value is filtered. The value is set to NULL.

1  Refers to Transact-SQL submitted as language events by using osql or sqlcmd instead of as remote procedure calls.

Requires VIEW DATABASE STATE permission on server. Cannot be queried from the master database. For more information, see Dynamic Management Objects.

Zawartość społeczności

Dodaj
Pokaż:
© 2014 Microsoft