sys-dm-os-buffer-pool-extension-configuration-transact-sql.md

title	sys.dm_os_buffer_pool_extension_configuration (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	09/09/2017
ms.prod	sql
ms.reviewer
ms.technology	system-objects
ms.topic	language-reference
f1_keywords	dm_os_buffer_pool_extension_configuration sys.dm_os_buffer_pool_extension_configuration_TSQL dm_os_buffer_pool_extension_configuration_TSQL sys.dm_os_buffer_pool_extension_configuration
dev_langs	TSQL
helpviewer_keywords	sys.dm_os_buffer_pool_extension_configuration dynamic management view
ms.assetid	d52cc481-4d29-4f33-b63d-231ec35d092f
author	stevestein
ms.author	sstein

sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2014-xxxx-xxxx-xxx-md]

Returns configuration information about the buffer pool extension in [!INCLUDEssNoVersion]. 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)