System tables for buffer pool extension

Buffer Pool extension is new feature in SQL 2014
We have two system tables to get information for buffer pool extension

1. sys.dm_os_buffer_pool_extension_configuration

SELECT * FROM sys.dm_os_buffer_pool_extension_configuration

Here we can see buffer pool extension is enabled  or not

file_id = 0 means as of now no data is in buffer pool extension.
file_id is same column which is used to connect dm_os_buffer_descriptors to get which pages are assigned to buffer pool extension

Lets see example here

SELECT

   COUNT(*) AS cached_pages_count 

FROM

   sys.dm_os_buffer_descriptors AS bd 

JOIN

   sys.dm_os_buffer_pool_extension_configuration AS bpec 

      ON bd.file_id = bpec.file_id 

ORDER BY

   cached_pages_count DESC;

This will give number of pages which are stored in buffer pool extension





This shows 0 as we have no data in buffer pool extension as of now.

2.sys.dm_os_buffer_descriptors

SELECT * FROM sys.dm_os_buffer_descriptors

Each cached data page has one  row in buffer descriptor
It gives information for database, object, data_type , row_count and other information for catched page


Lets see some example here

To get number of pages group by database
Database_id 32767 is used for resource database.

SELECT

   COUNT(*)AS cached_pages_count      ,

   CASE database_id          

      WHEN 32767 THEN 'ResourceDb'          

      ELSE db_name(database_id)          

   END AS database_name 

FROM

   sys.dm_os_buffer_descriptors 

GROUP BY

   DB_NAME(database_id) ,

   database_id 

ORDER BY

   cached_pages_count DESC;

To get count of pages group by type of allocation unit.

select

   count(*) cached_page_count,

   type_desc   

from

   sys.dm_os_buffer_descriptors   bd   

join

   sys.allocation_units au    

      on bd.allocation_unit_id = au.allocation_unit_id    

group by

   type_desc

  
To get information object wise , here we will use objectproperty(<object_id>, 'isusertable')
to get idea which user tables are in cache

SELECT

   COUNT(*)AS cached_pages_count       ,

   name ,

   index_id  

FROM

   sys.dm_os_buffer_descriptors AS bd      

INNER JOIN

   (

      SELECT

         object_name(object_id) AS name               ,

         index_id ,

         allocation_unit_id,

         object_id          

      FROM

         sys.allocation_units AS au             

      INNER JOIN

         sys.partitions AS p                  

            ON au.container_id = p.hobt_id                      

            AND (

               au.type = 1

               OR au.type = 3

            )         

      UNION

      ALL          SELECT

         object_name(object_id) AS name                 ,

         index_id,

         allocation_unit_id,

         object_id         

      FROM

         sys.allocation_units AS au             

      INNER JOIN

         sys.partitions AS p                  

            ON au.container_id = p.partition_id                      

            AND au.type = 2     

      ) AS obj          

         ON bd.allocation_unit_id = obj.allocation_unit_id   

   WHERE

      database_id = DB_ID()

      and OBJECTPROPERTY(object_id,'isusertable') = 1 

   GROUP BY

      name,

      index_id  

   ORDER BY

      cached_pages_count DESC;