SQLTeam.com Logo

Return to What Data is in SQL Server's Memory?

What Data is in SQL Server's Memory?

Written by Bill Graziano on 23 July 2007

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.