SQL Server Architecture - SQL Server 2012 Internals and Troubleshooting - page 13

it already has the page cached in memory. If the page is already cached, then the results are passed
back to the Access Methods.
If the page isn't already in cache, then the Buffer Manager gets the page from the database on disk,
puts it in the data cache, and passes the results to the Access Methods.
NOTE The PAGEIOLATCH wait type represents the time it takes to read a data
page from disk into memory. Wait types are covered later in this chapter.
The key point to take away from this is that you only ever work with data in memory. Every new
data read that you request is i rst read from disk and then written to memory (the data cache) before
being returned as a result set.
This is why SQL Server needs to maintain a minimum level of free pages in memory; you wouldn't
be able to read any new data if there were no space in cache to put it i rst.
The Access Methods code determined that the SELECT query needed a single page, so it asked the
Buffer Manager to get it. The Buffer Manager checked whether it already had it in the data cache,
and then loaded it from disk into the cache when it couldn't i nd it.
Data Cache
The data cache is usually the largest part of the buffer pool; therefore, it's the largest memory con-
sumer within SQL Server. It is here that every data page that is read from disk is written to before
being used.
The sys . dm _ os _ buffer _ descriptors DMV contains one row for every data page currently held in
cache. You can use this script to see how much space each database is using in the data cache:
SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id),database_id
ORDER BY 'Cached Size (MB)' DESC
The output will look something like this (with your own databases, obviously):
Cached Size (MB) Database
3287 People
34 tempdb
12 ResourceDb
4 msdb
In this example, the People database has 3,287MB of data pages in the data cache.
The amount of time that pages stay in cache is determined by a least recently used (LRU) policy.