sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)
Applies to: SQL Server (all supported versions)
Returns configuration information about the buffer pool extension in SQL Server. Returns one row for each buffer pool extension file.
Column name | Data type | Description |
---|---|---|
path | nvarchar(256) | Path and file name of the buffer pool extension cache. Nullable. |
file_id | int | ID of the buffer pool extension file. Is not nullable. |
state | int | The state of the buffer pool extension feature. Is not nullable. 0 - Buffer pool extension disabled 1 - Buffer pool extension disabling 2 - Reserved for the future use 3 - Buffer pool extension enabling 4 - Reserved for the future use 5 - Buffer pool extension enabled |
state_description | nvarchar(60) | Describes the state of the buffer pool extension feature. Is nullable. 0 = BUFFER POOL EXTENSION DISABLED 5 = BUFFER POOL EXTENSION ENABLED |
current_size_in_kb | bigint | Current size of the buffer pool extension file. Is not nullable. |
Permissions
Requires VIEW SERVER STATE permission on the server.
Examples
A. Returning configuration buffer pool extension information
The following example returns all columns from the sys.dm_os_buffer_pool_extension_configruation DMV.
SELECT path, file_id, state, state_description, current_size_in_kb
FROM sys.dm_os_buffer_pool_extension_configuration;
B. Returning the number of cached pages in the buffer pool extension file
The following example returns the number of cached pages in each buffer pool extension file.
SELECT COUNT(*) AS cached_pages_count
FROM sys.dm_os_buffer_descriptors
WHERE is_in_bpool_extension <> 0
;
See Also
Buffer Pool Extension
sys.dm_os_buffer_descriptors (Transact-SQL)