In a recent consultation visit to a customer, I encountered deep performance-related problems. The customer was unsure about the cause of these issues and sought my assistance. As someone who enjoys tackling challenges head-on, I was determined to identify the problem. After using various tools, I discovered that the root cause was memory pressure. In this blog post, we will explore the concept of SQL Server Paging of Memory Identification.

After the engagement, the organization’s DBA reached out to me for guidance on identifying memory pressure across multiple servers in their infrastructure. They wanted a simple script that could be run to determine if SQL Server pages were being paged out, potentially causing memory pressure. This blog post and accompanying PowerShell script were created as a result of that request.

Below is an example of the PowerShell script:

param (
    [string]$SqlServerName = "localhost"
)
Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)
foreach ($LogArchiveNo in ($SqlServer.EnumErrorLogs() | Select-Object -ExpandProperty ArchiveNo)) {
    $SqlServer.ReadErrorLog($LogArchiveNo) |
        Where-Object {$_.Text -like "*process memory has been paged out*"}
}

The output of this script will provide information about memory pressure in the SQL Server instance.

Why is identifying memory pressure important? Excessive memory pressure can lead to SQL Server’s memory allocations being paged out to disk, resulting in increased I/O latency for memory access. To mitigate this performance impact, it is crucial to ensure that the machine has sufficient physical memory and a well-designed memory infrastructure for SQL Server. Overcommitting memory should be avoided to prevent excessive paging. Regular reevaluation of memory allocations and available physical memory is recommended to alleviate memory pressure in the SQL Server instance.

Have you ever used simple scripts like this to identify memory pressure on your servers? How did you utilize them? Let me know in the comments below!