| title | sys.dm_os_memory_cache_clock_hands (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | sys.dm_os_memory_cache_clock_hands (Transact-SQL) | ||||
| author | rwestMSFT | ||||
| ms.author | randolphwest | ||||
| ms.date | 02/27/2023 | ||||
| ms.service | sql | ||||
| ms.subservice | system-objects | ||||
| ms.topic | reference | ||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
||||
| monikerRange | >=sql-server-2016||>=sql-server-linux-2017||>=aps-pdw-2016||=azure-sqldw-latest |
[!INCLUDE sql-asa-pdw]
Returns the status of each hand for a specific cache clock.
Note
To call this from [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_memory_cache_clock_hands. [!INCLUDEsynapse-analytics-od-unsupported-syntax]
| Column name | Data type | Description |
|---|---|---|
| cache_address | varbinary(8) | Address of the cache associated with the clock. Is not nullable. |
| name | nvarchar(256) | Name of the cache. Is not nullable. |
| type | nvarchar(60) | Type of cache store. There can be several caches of the same type. Is not nullable. |
| clock_hand | nvarchar(60) | Type of hand. Value is one of the following: External Internal Is not nullable. |
| clock_status | nvarchar(60) | Status of the clock. Value is one of the following: Suspended Running Is not nullable. |
| rounds_count | bigint | Number of sweeps made through the cache to remove entries. Is not nullable. |
| removed_all_rounds_count | bigint | Number of entries removed by all sweeps. Is not nullable. |
| updated_last_round_count | bigint | Number of entries updated during the last sweep. Is not nullable. |
| removed_last_round_count | bigint | Number of entries removed during the last sweep. Is not nullable. |
| last_tick_time | bigint | Last time, in milliseconds, that the clock hand moved. Is not nullable. |
| round_start_time | bigint | Time, in milliseconds, of the previous sweep. Is not nullable. |
| last_round_start_time | bigint | Total time, in milliseconds, taken by the clock to complete the previous round. Is not nullable. |
| pdw_node_id | int | Applies to: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW] The identifier for the node that this distribution is on. |
On [!INCLUDEssNoVersion_md] and SQL Managed Instance, requires VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
[!INCLUDEssNoVersion] stores information in memory in a structure called a memory cache. The information in the cache can be data, index entries, compiled procedure plans, and various other types of [!INCLUDEssNoVersion] information. To avoid re-creating the information, it is retained the memory cache as long as possible and is ordinarily removed from the cache when it is too old to be useful, or when the memory space is needed for new information. The process that removes old information is called a memory sweep. The memory sweep is a frequent activity, but is not continuous. A clock algorithm controls the sweep of the memory cache. Each clock can control several memory sweeps, which are called hands. The memory-cache clock hand is the current location of one of the hands of a memory sweep.
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
sys.dm_os_memory_cache_counters (Transact-SQL)