What Data is in SQL Server's Memory?
SQL Server memory is primarily used to store data (buffer) and query plans (cache). In this article I'll show how much memory is allocated to the data buffer (or RAM). I'll explain how to determine what tables and indexes are in the buffer memory of your server.
SQL Server stores its data in 8KB data pages. As these pages are read off disk they are stored in memory. This is referred to as buffer memory. A list of all the data pages in memory is stored in the dynamic management view sys.dm_os_buffer_descriptors. A simple SELECT from this DMV returns this result set:
database_id file_id page_id page_level allocation_unit_id page_type row_count free_space_in_bytes is_modified
----------- ------- ------- ---------- ------------------ --------- --------- ------------------- -----------
2 1 8716 0 71942940205187072 IAM_PAGE 2 6 1
2 1 15178 0 440359678902272 DATA_PAGE 42 1086 1
1 1 331 0 281474980642816 IAM_PAGE 2 6 0
1 1 239 0 281474980642816 DATA_PAGE 1 6790 0
5 1 45652 0 72057594089766912 DATA_PAGE 80 696 0
2 1 9860 0 169048845058048 INDEX_PAGE 0 8096 1
5 1 35979 0 71798504602664960 TEXT_MIX_PAGE 35 338 0
1 1 376 1 458752 INDEX_PAGE 2 8062 0
. . . .
I'm running these queries on the server that hosts SQLTeam.com. It's running SQL Server 2005 Express Edition so the memory is capped at 1GB. We can see what database this data page came from using the database_id column. If this page belongs to an index we can see the index level of the page in the page_level column. We can also see what type of page this is by looking at the page_type column. My result set included Index Allocation Map (AIM) pages, data pages, index pages and text pages. You can see a full list of page types in Books Online under Pages and Extents. There is also a flag (is_modified) that tells us whether this page has been changed since it was read from disk (i.e. the page is "dirty").
We can do a little grouping and summing on this table like so:
select count(*) AS Buffered_Page_Count
,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Buffered_Page_Count Buffer_Pool_MB
------------------- --------------
59405 464
This tells us that there are 59,405 data pages in memory that take 464MB of RAM. If I turn on Performance Monitor (PerfMon) and watch the Database Pages counter in the SQLServer:Buffer Manager object I'll see the exact same 59,405 listed. This number fluctuates a little minute to minute but shouldn't be changing dramatically on a production server.
The next thing I typically want to see is how much memory is going to each database. On my server I run this query:
SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Database_Name Buffered_Page_Count Buffer_Pool_MB
-------------------- ------------------- --------------
sqlteam 28145 219
SqlTeamBeta 15854 123
tempdb 8887 69
SQLTeamBlogsNew 5167 40
clearpass_isc 1144 8
eNewsletterPro 367 2
BillGraziano 250 1
ResourceDb 64 0
ClearPass_MIS 58 0
master 35 0
ClearTrace 16 0
msdb 1 0
It looks like most of the data buffer is being allocated to SQLTeam. The next level of drill-down is the object level inside the database. That query looks like this:
SELECT TOP 25
obj.[name],
i.[name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
-- ,obj.name ,obj.index_id, i.[name]
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.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
name name type_desc Buffered_Pages Buffer_MB
------------------------------ ----------------------------------- ----------------- -------------- ---------
FORUM_REPLY IX_TOPIC_DATE CLUSTERED 13287 103
FORUM_TOPICS PK_FORUM_TOPICS CLUSTERED 6028 47
FORUM_MEMBERS PK_FORUM_MEMBERS CLUSTERED 2276 17
FORUM_REPLY IX_FORUM_REPLY NONCLUSTERED 1201 9
fulltext_index_map_341576255 i1 CLUSTERED 925 7
FORUM_MEMBERS_PENDING PK__FORUM_MEMBERS_PE__46E78A0C CLUSTERED 491 3
sqlteam_Article PK_sqlteam_Article CLUSTERED 445 3
FORUM_TOPICS IX_FORUM_TOPICS_FORUM_ID_LAST_POST NONCLUSTERED 431 3
fulltext_index_map_373576369 i1 CLUSTERED 304 2
sysobjvalues clst CLUSTERED 249 1
FORUM_MEMBERS IX_LAST_POST NONCLUSTERED 222 1
FORUM_TOPICS IX_FORUM_TOPICS_TOPIC_ID NONCLUSTERED 173 1
FORUM_REPLY IX_REPLY_FORUM_R_STATUS NONCLUSTERED 153 1
FORUM_MEMBERS IX_FORUM_MEMBERS NONCLUSTERED 123 0
. . . . .
This query only returns the results for a specific database. This is the result from the SQLTeam.com database. Remember that keeping a clustered index in the buffer (or memory) is the same as keeping the table in memory.
The forum tables are the largest in the database and typically get the most traffic. I'm not surprised to see them at the top of the list. I was a little surprised at how quickly the memory usage dropped off. I expected it to be spread out more evenly. The "full text" entries in the list are for the full text indexes that are on the forum tables.
