SQL Server with Mr. Denny

Oct 16 2008   11:00AM GMT

Handy Query to look at amount of data in Buffer Cache

Denny Cherry Denny Cherry Profile: Denny Cherry

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

2  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Lesanch
    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.
    0 pointsBadges:
    report
  • Denny Cherry
    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
    66,070 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: