Handy Query to look at amount of data in Buffer Cache

Here’s a handy query which I’ve written which I’ve used to see how much of each table is loaded into the SQL Server Buffer Cache.  It shows you how much data is in memory, how much space is used on disk, and the percentage of data in memory compared to the amount of data on disk.

SELECT sys.tables.name TableName,
 sum(a.page_id)*8 AS MemorySpaceKB,
 SUM(sys.allocation_units.data_pages)*8 AS StorageSpaceKB,
 CASE WHEN SUM(sys.allocation_units.data_pages) <> 0 THEN SUM(a.page_id)/CAST(SUM(sys.allocation_units.data_pages) AS NUMERIC(18,2)) END AS 'Percentage Of Object In Memory'
FROM (SELECT database_id, allocation_unit_id, COUNT(page_id) page_id FROM sys.dm_os_buffer_descriptors GROUP BY database_id, allocation_unit_id) a
JOIN sys.allocation_units ON a.allocation_unit_id = sys.allocation_units.allocation_unit_id
JOIN sys.partitions ON (sys.allocation_units.type IN (1,3)
  AND sys.allocation_units.container_id = sys.partitions.hobt_id)
 OR (sys.allocation_units.type = 2 AND sys.allocation_units.container_id = sys.partitions.partition_id)
JOIN sys.tables ON sys.partitions.object_id = sys.tables.object_id
 AND sys.tables.is_ms_shipped = 0
WHERE a.database_id = DB_ID()
GROUP BY sys.tables.name

When you run this code, some of the smaller objects will show more pages in memory than on disk.  It appears that this is normal.  My guess is that SQL Server is preallocating data pages so that if data is added to these pages there is space allocated to them already in order to save time when adding data to the table.

 I’ve removed the system objects from this query, as you can’t do anything to optimize them.

Denny

Share

2 Responses

  1. Hi Denny,

    After run this query i have not received any data from consult, only appears the column’s heads.
    Could i have anything wrong ???

    Regards.

  2. I would assume that you either ran this in the master database, or you don’t have the rights to view the information. This script is database specific, so you will need to run it from the database you want to view the data for.

    Denny

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?