Export (0) Print
Expand All
Expand Minimize

sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)

SQL Server 2014

Returns configuration information about the buffer pool extension in SQL Server. Returns one row for each buffer pool extension file.

Applies to: SQL Server (SQL Server 2014 through current version).

Column name

Data type

Description

path

nvarchar (256)

Path and file name of the buffer pool extension cache. Is not 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

1 = BUFFER POOL EXTENSION ENABLED

current_size_in_kb

bigint

Current size of the buffer pool extension file. Is not nullable.

Requires VIEW SERVER STATE permission on the server.

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
;

Community Additions

ADD
Show:
© 2014 Microsoft