SQL Performance Troubleshooting Mini-Series - Memory Utilization

For Memory Utilization section we will be only working with one counter. There are more counters that will let you know if there are memory pressure issues but they will be covered later in the SQL Performance section. This section will focus on if there is any issues present with the with memory contention issues between the OS and SQL Server.

The counter, Available MBytes, shows the amount of physical memory, in megabytes, available to processes running on the computer. Monitor this counter to ensure that the server maintains a level of at least 20 percent of the total available physical RAM. Consider other applications that may be running on the physical host as well. Available Memory should never be less than 1000 MB as per the threshold on this graph.

To have the above graph displayed we use the following query against the perfmon data:

SELECT 
    det.CounterName,
    d.CounterValue,
    SUBSTRING(d.CounterDateTime,1,19) AS CounterDateTime
FROM dbo.CounterData d 
    INNER JOIN dbo.CounterDetails det ON d.CounterID = det.CounterID
WHERE countername = 'Available MBytes'

UNION ALL

SELECT 
    'Threshold',
    1000,
    SUBSTRING(d.CounterDateTime,1,19) AS CounterDateTime
FROM dbo.CounterData d 
    INNER JOIN dbo.CounterDetails det on d.CounterID = det.CounterID
WHERE countername = 'Available MBytes'
ORDER BY det.CounterName, CounterDateTime

There isn't too much else to check, is there available memory or not? The memory check on the overview page pulls the minimum value for available memory and checks to make sure is in not below 1 GB and will give a warning if it is between 1 GB and 2 GB free. These numbers can be adjusted to numbers that might make better sense for your environment. I do check to make sure there are not memory contention issues within SQL Server itself but that will be covered in the SQL Performance topic.