Todo sobre SQL Server: DMV para optimización en SQL Server Tip 07

Esta serie de blogs incluye T-SQL y DMV que pueden ser utilizadas para detectar problemas de rendimiento de SQL Server y su posible solución.

Otro de los componentes que se pueden revisar para la optimización de SQL Server es el Buffer Pool, que es el componente que almacena y administra el caché de datos de SQL Server. La información detallada sobre el Buffer Pool la podemos obtener mediante la DMV sys.dm_os_buffer_descriptors, esta DMV devuelve información sobre todas las páginas que están siendo utilizadas por el Buffer Pool, con base en estos resultados podemos ver la distribución del Buffer Pool de acuerdo al uso que se le está dando, y devuelve el pagetype que puede ser un Data Page, Index Page o un TEXT_MIX_PAGE (más información sobre los tipos páginas disponible en http://msdn.microsoft.com/en-us/library/ms190969.aspx).

Si ejecutamos el siguiente estatuto

select * from sys.dm_os_buffer_descriptors

Se obtiene información de la distribución del Buffer Pool. En este resultado vemos todas las páginas que están siendo utilizadas por el buffer pool, recordemos que cada página de SQL Server es de 8KB, en este caso cada página de datos del caché tiene un descriptor del buffer. Los descriptores del buffer representan de forma única cada página que está actualmente siendo utilizada en caché de una instancia de SQL Server.

Si queremos una vista resumida de esta información podemos ejecutar el siguiente T-SQL.

SELECT count(database_id) * 8 / 1024 AS 'Cache Usado (MB)' --multiplicamos la cantidad paginas para convertilas en MB
    ,CASE database_id
        WHEN 32767 THEN 'BD Recursos'
        ELSE db_name(database_id)
        END AS 'NombreBaseDatos'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id

Este T-SQL da como resultado la cantidad de caché utilizado por cada base de datos de la instancia actual de SQL Server.