Identifying cached tables in SQL Server buffer

When a query is submitted it goes through a sequence of phases in SQL Server. Starting from parse, optimization, plan preparation until the execution of the plan. During the execution process, SQL Server reads the database files and store the relevant pages (IAM, Index, and data) into memory. The pages then stay in memory during the query execution. The pages will be cleared from memory and written to disk by the internal process called lazy-writer.

This post covers how to find the pages in memory, memory used by the tables and databases.

How to find the pages in memory

SQL Server has a dynamic management view “dm_os_buffer_descriptors” which provides information about the pages in memory. This view returns pages currently in the memory for all the databases in an instance along with the allocation unit ID of the page. It also shows whether or not the page in memory is modified and shows the time taken to read the page from disk into memory.

The following code is used to extract the in-memory page details and the associated table name from the current database. The query uses the allocation unit Id returned from the view dm_os_buffer_descriptors and joins with sys.partitions, sys.allocation_units and sys.indexes to find the corresponding table/index.

select
   b.page_id,
   b.page_level,
   b.page_type,
   b.row_count,
   b.free_space_in_bytes,
   b.is_modified,
   b.read_microsec / 1000000.0 as read_sec,
   a.allocation_unit_id,
   object_name(p.object_id) as tablename,
   i.name as indexname,
   p.index_id,
   p.partition_number,
   a.type_desc,
   a.total_pages,
   a.used_pages,
   a.data_pages 
from
	sys.partitions p 
	join
	sys.allocation_units a 
	on a.container_id = 
	case
		when
		a.type in (1,3)
		then
		p.hobt_id 
		else
		p.partition_id 
	end
	join
		sys.indexes i 
		on p.object_id = i.object_id 
		and p.index_id = i.index_id 
	join
		sys.dm_os_buffer_descriptors b 
		on a.allocation_unit_id = b.allocation_unit_id 
		and b.database_id = db_id() 
where
	OBJECTPROPERTY(p.object_id, 'isusertable') = 1 
order by
	tablename, index_id, partition_number, page_level

Memory used by the tables

Using the same dynamic management view the memory utilization for each table can be calculated. The following code provide number of in-memory pages for each table. The server’s total memory is used for the percentage calculation.

declare @physical_memory bigint 
select
   @physical_memory = total_physical_memory_kb 
from
   sys.dm_os_sys_memory 

select
	object_name(p.object_id) as tablename,
	i.name as indexname,
	count(b.page_id) total_pages,
	count(b.page_id)*8.0 total_memory_used_kb,
	@physical_memory as server_physical_memory,
	(count(b.page_id)*8.0) / @physical_memory as Percentage_used 
from
    sys.partitions p 
    join
        sys.allocation_units a 
        on a.container_id = 
        case
        when
            a.type in (1,3)
        then
            p.hobt_id 
        else
            p.partition_id 
        end
    join
        sys.indexes i 
        on p.object_id = i.object_id 
        and p.index_id = i.index_id 
    join
        sys.dm_os_buffer_descriptors b 
        on a.allocation_unit_id = b.allocation_unit_id 
        and b.database_id = db_id() 
	where
		OBJECTPROPERTY(p.object_id, 'isusertable') = 1 
	group by
		object_name(p.object_id), i.name 
	order by 
		tablename

Memory used by the databases

The following code returns the memory usage by each database.

declare @physical_memory bigint 
select
   @physical_memory = total_physical_memory_kb 
from
   sys.dm_os_sys_memory 
select
      db_name(b.database_id) as databasename,
      count(b.page_id) total_pages,
      count(b.page_id)*8.0 total_memory_used_kb,
      @physical_memory as server_physical_memory_kb,
      (count(b.page_id)*8.0)/ @physical_memory as Percentage_used 
from
     sys.dm_os_buffer_descriptors b 
group by
     b.database_id 
order by
     total_memory_used_kb desc

Demo

When the below code is executed, it returned 1212 records. All the 1212 records are stored in four data pages. SQL Server fetched six pages (Logical Reads 6) from disk to memory as shown in the image.

set statistics io on
select * from SalesOrder_nonpartition_ci where Orderid < 1212

The table has clustered index so SQL Server has to first go to the root page and navigate to the data page. Since the table is small in size there are no intermediate pages so it just scanned the IAM page, root page, and the data page which equates to 6 pages (1 IAM Page + 1 Index Page + 4 Data Pages).

The following results are returned when the query is executed from the “How to find the pages in memory” section which contains Page Id and its level in the index structure and the page type etc.,

The page Id returned from the above query is then compared with the ChildPageId in DBCC IND() result of the Index page (as shown in the below image). It shows that all the four pages where “OrderId” less than 1212 match with page Id in memory.

dbcc page (10,1,1192,3) with tableresults

Leave a Reply

Your email address will not be published.