Buffer cache used per database in MB

| 0 comments | 10,256 views
Categories: ,

VN:F [1.9.17_1161]
Rating: 4.9/5 (7 votes cast)

Data pages read from disk are placed in the buffer pool with the intention that they will be reused, and accessing them from RAM is faster than from disk. Knowing how much of your RAM is committed to each database can help you provision the right amount of RAM to SQL Server, and also to identify rogue queries that draw too much data into RAM and force data from other databases out of the cache.

For more information about this metric, see: http://www.simple-talk.com/community/blogs/jonathanallen/archive/2012/06/12/110696.aspx.

Buffer cache used per database in MB, 4.9 out of 5 based on 7 ratings

Metric definition

Metric Name:
Buffer cache used per database
Description:

MB of buffer cache dedicated to each database. This will rise and fall as SQL Server manages the memory pages relative to workload in each database. Large, rapid changes in one database that appear to affect other databases (one trace rises while another falls at the same collection time) are worth investigating to ensure your systems are not fighting for physical resources.

Check the results of this and resolve the issues:

SELECT  [sp].[name] AS [server principle] ,
        [sp].[default_database_name] ,
        [sp].[principal_id] ,
        [sp].[sid] ,
        [sp].[type] ,
        [sp].[type_desc] ,
        [sp].[is_disabled]
FROM    [sys].[server_principals] AS sp
        INNER JOIN sys.databases AS d ON [sp].[default_database_name] = d.[name]
WHERE   [d].[state_desc] <> 'online'
ORDER BY [sp].[default_database_name] ,
        [sp].[name];
Enter the T-SQL query that will collect data:
SELECT (COUNT(*) * 8.0) / 1024 AS MB
  FROM sys.dm_os_buffer_descriptors AS dobd
  WHERE [dobd].[database_id] = DB_ID();
Select instances to collect from:
Select all
Choose databases to collect from:
All databases
Set collection frequency:
Collect data every 5 minutes
Every 5 minutes, unless you want to investigate in more detail.
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

An alert is not required

VN:F [1.9.17_1161]
Rating: 4.9/5 (7 votes cast)