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;





No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts