PowerShell – SQL Server Paging of Memory Identification

In one of my recent consultation visits to a customer, there was deep performance related problems. They were unclear to what was happening and what was the actual problem. But these are some of the challenges that I love to take head-on too. In this quest to learn what the problem would be, I used a number of tools and during that time I figured out it was a memory pressure that was creating the problem. Let us learn about SQL Server Paging of Memory Identification.

After the engagement got over, the DBA from the organization wrote to me to understand how this can be easily identified when working with a number of their servers in the infrastructure. He wanted something that can be run to understand if the SQL Server pages were being paged out and that could be a possible cause of memory pressure. He wanted some guidance or cheat sheet to play with.

This blog and powershell script was a fall out of that engagement.

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 would look like below:

Why is this important?

If there is excessive memory pressure on SQL Server’s memory allocations causing memory to get paged out to disk, that could be a potentially large performance impact as it invites I/O latency to memory access. It is best practice to ensure that there is enough physical memory on the machine, as well as a well-designed memory infrastructure from SQL Server so that there isn’t overcommitting of memory in order to ensure that paging is not excessive. It is recommended that reevaluation of memory allocations and/or available physical memory is taken into account in order to relieve memory pressure for the current SQL Server instance.

This shows how there has been some memory pressure to our SQL Server instance and this is available from our log records. Have you in ever used such simple scripts to figure out pressures of memory on your servers? How did you use them? Let me know.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.