¿Qué tenemos en el buffer pool?

Una vista muy útil a la hora de analizar el comportamiento de nuestra base de datos es sys.dm_os_buffer_descriptors.

Esta vista nos da la información de todas las páginas de datos que se encuentran en el buffer pool de SQL Server.

La función del buffer pool es mantener en memoria aquellas páginas de datos que han sido leídas del disco, para que puedan ser leídas más rápido la siguiente ves, sin necesidad de ir al disco duro, por lo que el tiempo de respuesta mejora.

Consultando esta vista, nos puede ayudar a ver como se esta comportando la base de datos de SQL Server, por ejemplo saber que tablas están teniendo más cambios, cuales de ellas requieren posiblemente un índice para que no se tengan tantas paginas de la misma en memoria, etc.

Algunos ejemplos de las consultas que podemos hacer son las siguientes:

1. Ver cuantas páginas de cada una de las bases de datos están en memoria.

SELECT count(*)AS cached_pages_count
    ,CASE database_id 
        WHEN 32767 THEN 'ResourceDb' 
        ELSE db_name(database_id) 
        END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;

2. Ver las páginas de datos de cada objeto en memoria

SELECT count(*)AS cached_pages_count 
    ,name ,index_id 
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_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
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id 
ORDER BY cached_pages_count DESC;

3. Ver cuantas de ellas son páginas sucias y cuales no, por base de datos.

SELECT   
  (CASE WHEN ([is_modified] = 1) THEN 'Sucia'
ELSE 'Limpia' END) AS 'Page State',
  (CASE WHEN ([database_id] = 32767) THEN 'Resource Database'
ELSE DB_NAME (database_id) END) AS 'Database Name',
   COUNT (*) AS 'Page Count'FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified]
   ORDER BY [database_id], [is_modified];