Buffer pressure

| 5 Comments | 10,059 views
Categories: , ,

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

Trying to determine if you have pressure in your buffer allocations can be difficult. Buffer cache hit ratio is an almost useless metric, so you need a mechanism to let you know if there’s a problem in that area. You can use the memory dump from DBCC MEMORYSTATUS() and then compare the Target Committed to the Current Committed allocations. When the target is below the current committed, you’re looking at a buffer issue. This query simply compares the two with subtraction so that you’ll know when you’re looking at negative numbers you’ve hit a buffer problem.

Buffer pressure, 4.0 out of 5 based on 5 ratings

Metric definition

Metric Name:
Buffer pressure
Description:
Uses the memory dump from DBCC MEMORYSTATUS to look for buffer memory allocation issues.
Enter the T-SQL query that will collect data:
DECLARE @MemStat TABLE
  (
   ValueName SYSNAME,
   Val BIGINT
  );
INSERT INTO @MemStat
    EXEC ('DBCC memorystatus() WITH tableresults');
WITH  Measures
        AS (SELECT TOP 2 CurrentValue,
                ROW_NUMBER() OVER (ORDER BY OrderColumn) AS RowOrder
              FROM (SELECT CASE WHEN (ms.ValueName = 'Target Committed')
                                THEN ms.Val
                                WHEN (ms.ValueName = 'Current Committed')
                                THEN ms.Val
                           END AS 'CurrentValue',
                        0 AS 'OrderColumn'
                      FROM @MemStat AS ms
                   ) AS MemStatus
              WHERE CurrentValue IS NOT NULL
           )
  SELECT TargetMem.CurrentValue - CurrentMem.CurrentValue
    FROM Measures AS TargetMem 
    JOIN 
      Measures AS CurrentMem
    ON
      TargetMem.RowOrder + 1 = CurrentMem.RowOrder;
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
master
Set collection frequency:
Collect data every minute
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

Alert name:
Buffer pressure detected
Description:
Any time the target value is greater than the allocated value, you have a buffer memory allocation issue.
Raise an alert when the metric value goes:
Below the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:0
Medium:Turn off check box
Low:Turn off check box
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 4.0/5 (5 votes cast)
  • afroz ahmed

    I get zero results when I run the query. There is no ValueName of ‘Target Committed’ or ‘Current Committed’. Do you know why?
    Microsoft SQL Server 2008 (SP3) – 10.0.5500.0 (X64)
    Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Nic

    I too had an issue with no data coming back as Afroz did, could you not also achieve this using the sys.dm_os_performance_counters DMV and looking at the buffer memory total and target pages?

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
    • Wilfred van Dijk

      use this:
      select @total_server_memory = cntr_value from sys.dm_os_performance_counters
      where object_name = ‘SQLServer:Memory Manager’ and counter_name = ‘Total Server Memory (KB)’

      select @target_server_memory = cntr_value from sys.dm_os_performance_counters
      where object_name = ‘SQLServer:Memory Manager’ and counter_name = ‘Target Server Memory (KB)’

      VA:F [1.9.17_1161]
      Rating: 0 (from 0 votes)
  • Nic

    Although saying that the target and total may not match up, i.e if I allocate up to 8GB (target) for my server, when it starts up and if no one really uses it then the numbers wont match as SQL will only take what it needs.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Grant Fritchey

    Different versions of SQL Server have slightly different structures in the Buffer. This may only work well with 2008R2 and 2012

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)