Memory is one among the resources forming the performance triangle—CPU and storage being the other two. If one is hit, the other two take the load to try to bring the performance to acceptable levels, but there’s always the trade-off. Whatever transactions cannot be committed to the memory they would be forwarded to the disk subsystem by SQL Server. This causes a performance bottleneck. Therefore, the wait statistics can help identify performance issues on a SQL Server.
In this article, the following topics are discussed:
- Understanding internals of SQL Server memory setting and configuration
- The SQL Server memory and its impact on the database and application performance
- Discuss various SQL Server components that contribute to the memory usage
- Best practices and recommendation for memory sizing
- Multi-server memory report
- And more…
Memory management internals
SQL Server has a Memory Management Unit that performs automated dynamic memory management based on the workload of the system. This memory is the volatile space that’s critical to today’s Business – Tech needs, the right-sizing of which is vital to the optimal performance of the applications.
However, we all know that when setting up the server, the sizing contains some default values. in some cases, we soon find out that SQL Server uses almost all of the memory on the server, even though there’s no visible activity on the databases, bringing in the questions: Are the default values incorrect? If so, what should be the right size?
Memory Management on SQL Server works on the Fill-and-Flush algorithm. The default values do not restrict the memory consumption from growing unless there’s a request from the Operating System.
The sizing depends on various components of the system—in many cases, setting it between 70% and 80% is a good starting point. Then, you should also monitor it to see what else you may be missing and if you should tweak the setting. If you have other services on the SQL Server (you really shouldn’t), you may need to leave behind more, especially if these services are memory hogs. Consider revisiting the memory setting of the SQL instance in any of the following scenarios:
- Unresponsiveness of the Operating System
- Application exhaustion
- Backup operations that require large memory buffers
- In-Memory Optimized objects
- Column store indexes, since they require large volumes of memory to perform index maintenances.
The memory setting on SQL Server is pretty straightforward. You can change the value using sp_configure or SSMS GUI. This is an online option but remember that setting or resetting these values may cause some of the internal cache objects to reshuffle, which will leave the system running slightly slower.
sp_configure ‘max server memory (MB)’,<Memory in MB>
In this case, the number “2147483647” means that SQL Server has no upper limit and will use all the memory on the server.
Min server memory: min server memory as a floor value; SQL Server will commit memory for its own use until it reaches the min server memory setting. After that, it will maintain at least this amount of usable memory.
Max server memory: In the same way that min server memory provides a floor, max server memory provides a ceiling.
The min and max memory levels are the lower and upper limit of the amount of memory allowed for use by the buffer pool. The buffer pool is the largest chunk of memory consumed by SQL Server. The following are the SQL Server components within the SQL instance that use memory from the buffer pool
- Database Page Cache
- Internal log caches
- Procedure cache or query plan cache
- Query Workload space
- Locks (Memory grants)
- Connection context
- Optimizing queries
- System-level data structures
The values of the important metrics such as Available Mbytes, Pages/Sec, Buffer Cache Hit Ratio, PLE, etc. determine the SQL Server performance.
Buffer Cache Hit Ratio is specific to each application. 90% is usually considered desirable. It means that over 90% of the requests were served by the cache, which is a good thing. If the value is lower, add more memory until it is consistently higher than 90%.
Available Bytes is nothing but an indication of how much memory is available for use. The Pages/sec counter shows how many pages were retrieved from the disk, or written to the disk, both due to hard page faults.
PLE stands for Page Life Expectancy, which is an indication of how many seconds the page will stay in the pool for.
For example,
$server = 'hqdbt01'
$counters = @("\Memory\Available MBytes",
"\Memory\Pages/sec",
"\SQLServer:Buffer Manager\Buffer cache hit ratio",
"\SQLServer:Buffer Manager\Lazy writes/sec",
"\SQLServer:Buffer Manager\Page life expectancy"
)
$collections = Get-Counter -ComputerName $server -Counter $counters -SampleInterval 10 -MaxSamples 1
Write-Output $collections
foreach ($collection in $collections)
{$sampling = $collection.CounterSamples | Select-Object -Property TimeStamp, Path, Cookedvalue
$sampling | Format-Table -AutoSize
}
Recommendations and best practices
Let us now briefly look at the techniques to size the memory.
- 1 GB of memory reserved for Operating System
- 1 GB each for every 4 GB of RAM after the initial 4 GB, up to 16 GB of RAM
- 1 GB each for every 8 GB in more than 16 GB of RAM
For example, if you have a 32 GB RAM Database Server, then memory to be given to Operating System would be
- 1 GB, the minimum allocation
- + 3 GB, since 16 GB – 4 GB = 12 GB; 12 GB divided by 4 GB (each 4 GB gets 1 GB) is 3GB.
- + 2 GB, as 32 GB – 16 GB = 16 GB; 16 divided by 8 (each 8 GB after 16 GB gets 1 GB) is 2 GB
So, in total, for a server with 32 GB of RAM, 7 GB will be reserved for the Operating System. This is the maximum memory allocated to SQL Server should be 25 GB. Similarly, for a 64 GB Server, 10 GB should be reserved for the Operating System & 54 GB should be allocated for SQL Server.
We’ve all, at some point or the other, heard of or used Windows Management Instrumentation (WMI). There are several classes in WMI, which allow us to extract information about the hardware, installed software, the operating system, or even the registry. We can even modify settings and perform actions on these aspects.
The win32_OperatingSystem class is a WMI class that has all the necessary information about the active operating system (in case you are, say, dual booting). This class can also be used to get the amount of memory allocated to the operating system. Here are some of the objects the class can return, which could be of help to us (memory is measured in kilobytes by this class):
- TotalVisibleMemorySize: This field shows the total physical memory that is accessible to the operating system. Inaccessible chunks of memory may cause a smaller-than-installed number to be displayed here.
- FreePhysicalMemory: This tells us what amount of physical memory is free.
- TotalVirtualMemorySize: This is the total virtual memory available for the OS to use. This comprises the physical memory installed on the computer, along with the size of the pagefile.
- FreeVirtualMemory: Similar to FreePhysicalMemory, but includes the free space in the paging memory as well.
$server='hqdbt01'
Get-WmiObject -Class Win32_OperatingSystem -ComputerName $server | select CSName,
@{name="TotalVirtualMemorySize";expression={($_.TotalVirtualMemorySize/1024).tostring("N0")}},
@{name="TotalVisibleMemorySize";expression={($_.TotalVisibleMemorySize/1024).tostring("N0")}},
@{name="FreePhysicalMemory";expression={($_.FreePhysicalMemory/1024).tostring("N0")}},
@{name="FreeVirtualMemory";expression={($_.FreeVirtualMemory/1024).tostring("N0")}},
@{name="FreeSpaceInPagingFiles";expression={($_.FreeSpaceInPagingFiles/1024).tostring("N0")}},
NumberofProcesses,
NumberOfUsers 
We can fetch the page file information using the Win32_PageFileSetting WMI class.
$server='hqdbt01'
Get-WMIObject Win32_PageFileSetting -Computer $server| select @{name="ServerName";expression={$_.__Server}}, Name, InitialSize, MaximumSize
The following query gives the high-level memory usage details of the SQL instance.
SELECT
physical_memory_in_use_kb/1024 Physical_memory_in_use_MB,
large_page_allocations_kb/1024 Large_page_allocations_MB,
locked_page_allocations_kb/1024 Locked_page_allocations_MB,
virtual_address_space_reserved_kb/1024 VAS_reserved_MB,
virtual_address_space_committed_kb/1024 VAS_committed_MB,
virtual_address_space_available_kb/1024 VAS_available_MB,
page_fault_count Page_fault_count,
memory_utilization_percentage Memory_utilization_percentage,
process_physical_memory_low Process_physical_memory_low,
process_virtual_memory_low Process_virtual_memory_low
FROM sys.dm_os_process_memory;
Prepare the script
Let’s integrate the aforementioned three outputs into a single memory output:
- SQL Internal Memory structures using Counter
- Available virtual and physical memory using WMI object
- Page file setting using WMI
The HTML content preparation is all about filling in the value fed from the different section of the script, between the right tags.
The script can build valid HTML tags. The following are the functions used in the script.
- writeHTMLHeader: this function is used to generate the Header and define the style for the HTML file.
- writetableFooter: this defines the closing HTML tags.
- writeTableHeader: this defines the thirteen-columned output heading for the HTML file
- writeMemoryInfo: this is the function that carries out the merging of the two WMI class outputs. The output of Win32_PageFileSetting, Win32_OperatingSystem and SMO SQL is passed as arguments for this function. The values can also be further transformed or manipulated in this section.
- Email section
[expand title=”Code”]
# First, let’s create a text file, where we will later save memory details
$MailServer='mail01.example.com'
$MemoryFileName = "f:\PowerSQL\Memory.htm"
New-Item -ItemType file $MemoryFileName -Force
# Function to write the HTML Header to the file
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString('yyyy/MM/dd')
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title>SQLShack Memory Usage Report </title>'
add-content $fileName '<STYLE TYPE="text/css">'
add-content $fileName "<!--"
add-content $fileName "td {"
add-content $fileName "font-family: Tahoma;"
add-content $fileName "font-size: 11px;"
add-content $fileName "border-top: 1px solid #999999;"
add-content $fileName "border-right: 1px solid #999999;"
add-content $fileName "border-bottom: 1px solid #999999;"
add-content $fileName "border-left: 1px solid #999999;"
add-content $fileName "padding-top: 0px;"
add-content $fileName "padding-right: 0px;"
add-content $fileName "padding-bottom: 0px;"
add-content $fileName "padding-left: 0px;"
add-content $fileName "}"
add-content $fileName "body {"
add-content $fileName "margin-left: 5px;"
add-content $fileName "margin-top: 5px;"
add-content $fileName "margin-right: 0px;"
add-content $fileName "margin-bottom: 10px;"
add-content $fileName ""
add-content $fileName "table {"
add-content $fileName "border: thin solid #000000;"
add-content $fileName "}"
add-content $fileName "-->"
add-content $fileName "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"
add-content $fileName "<table width='100%'>"
add-content $fileName "<tr bgcolor='#CCCCCC'>"
add-content $fileName "<td colspan='13' height='25' align='center'>"
add-content $fileName "<font face='tahoma' color='#003399' size='4'><strong>SQLShack Memory Usage Report - $date</strong></font>"
add-content $fileName "</td>"
add-content $fileName "</tr>"
add-content $fileName "</table>"
}
# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)
Add-Content $fileName "<tr bgcolor=#CCCCCC>"
Add-Content $fileName "<td width='10%' align='center'>ServerName</td>"
Add-Content $fileName "<td width='10%' align='center'>TotalVirtualMemorySize</td>"
Add-Content $fileName "<td width='10%' align='center'>TotalVisibleMemorySize</td>"
Add-Content $fileName "<td width='10%' align='center'>FreePhysicalMemory</td>"
Add-Content $fileName "<td width='10%' align='center'>FreeVirtualMemory</td>"
Add-Content $fileName "<td width='10%' align='center'>FreeSpaceInPagingFiles</td>"
Add-Content $fileName "<td width='10%' align='center'>NumberofProcesses</td>"
Add-Content $fileName "<td width='10%' align='center'>NumberOfUsers</td>"
Add-Content $fileName "<td width='10%' align='center'>PageFile</td>"
Add-Content $fileName "<td width='10%' align='center'>Page-InitialSize</td>"
Add-Content $fileName "<td width='10%' align='center'>Page-MaxSize</td>"
Add-Content $fileName "<td width='10%' align='center'>SQLMaxMemory</td>"
Add-Content $fileName "<td width='10%' align='center'>SQLMinMemory</td>"
Add-Content $fileName "<td width='10%' align='center'>Memory Available MBytes</td>"
Add-Content $fileName "<td width='10%' align='center'>Buffer Cache Hit Ratio</td>"
Add-Content $fileName "<td width='10%' align='center'>PLE</td>"
Add-Content $fileName "</tr>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function writeMemoryInfo
{
param($filename,$csname,$TotalVirtualMemorySize,$TotalVisibleMemorySize,$FreePhysicalMemory,$FreeVirtualMemory,$FreeSpaceInPagingFiles,$NumberofProcesses,$NumberOfUsers,$PageFile,$initialSize,$MaxSize,$SQLMaxMemory, $SQLMinMemory ,$mAvailableMBytes, $Buffercachehitratio, $PLE )
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$csname </td>"
Add-Content $fileName "<td>$TotalVirtualMemorySize </td>"
Add-Content $fileName "<td>$TotalVisibleMemorySize</td>"
Add-Content $fileName "<td>$FreePhysicalMemory </td>"
Add-Content $fileName "<td>$FreeVirtualMemory </td>"
Add-Content $fileName "<td>$FreeSpaceInPagingFiles </td>"
Add-Content $fileName "<td>$NumberofProcesses </td>"
Add-Content $fileName "<td>$NumberOfUsers</td>"
Add-Content $fileName "<td>$PageFile</td>"
Add-Content $fileName "<td>$initialSize</td>"
Add-Content $fileName "<td>$MaxSize</td>"
Add-Content $fileName "<td>$SQLMaxMemory</td>"
Add-Content $fileName "<td>$SQLMinMemory</td>"
Add-Content $fileName "<td>$mAvailableMBytes</td>"
Add-Content $fileName "<td>$Buffercachehitratio</td>"
Add-Content $fileName "<td>$PLE</td>"
Add-Content $fileName "</tr>"
}
Function sendEmail
{
param($from,$to,$subject,$smtphost,$htmlFileName)
$body = Get-Content $htmlFileName
$body = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)
}
writeHtmlHeader $MemoryFileName
Add-Content $MemoryFileName "<table width='100%'><tbody>"
Add-Content $MemoryFileName "<tr bgcolor='#CCCCCC'>"
Add-Content $MemoryFileName "<td width='100%' align='center' colSpan=16><font face='tahoma' color='#003399' size='2'><strong> Memory Usage Details</strong></font></td>"
Add-Content $MemoryFileName "</tr>"
writeTableHeader $MemoryFileName
foreach ($svr in get-content "\\hqdbsp18\f$\PowerSQL\Server.txt"){
$page=Get-WMIObject Win32_PageFileSetting -Computer $svr| select __Server, Name, InitialSize, MaximumSize
$dp = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $svr | select CSName,
@{name="TotalVirtualMemorySize";expression={($_.TotalVirtualMemorySize/1024).tostring("N0")}},
@{name="TotalVisibleMemorySize";expression={($_.TotalVisibleMemorySize/1024).tostring("N0")}},
@{name="FreePhysicalMemory";expression={($_.FreePhysicalMemory/1024).tostring("N0")}},
@{name="FreeVirtualMemory";expression={($_.FreeVirtualMemory/1024).tostring("N0")}},
@{name="FreeSpaceInPagingFiles";expression={($_.FreeSpaceInPagingFiles/1024).tostring("N0")}},
NumberofProcesses,
NumberOfUsers
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') ($svr)
write-host $srv.Configuration.MaxServerMemory.RunValue
write-host $srv.Configuration.MinServerMemory.RunValue
$counters = @("\Memory\Available MBytes",
"\Memory\Pages/sec",
"\SQLServer:Buffer Manager\Buffer cache hit ratio",
"\SQLServer:Buffer Manager\Lazy writes/sec",
"\SQLServer:Buffer Manager\Page life expectancy"
)
$collections = Get-Counter -ComputerName $svr -Counter $counters -SampleInterval 5 -MaxSamples 1
Write-Output $collections
foreach ($collection in $collections)
{
$sampling = $collection.CounterSamples | Select-Object -Property TimeStamp, Path, Cookedvalue
foreach($sam in $sampling)
{
if ($sam.Path -like "*\Memory\Available MBytes*") {
$mAvailableMBytes=$sam.CookedValue
}
elseif ($sam.Path -like "*Buffer Manager\Buffer cache hit ratio*") {
$Buffercachehitratio=$sam.CookedValue
}
elseif ($sam.Path -like "*Page life expectancy*") {
$PLE=$sam.CookedValue}
}
}
write-host $mAvailableMBytes $Buffercachehitratio $PLE
Write-Host $dp.csname $dp.TotalVirtualMemorySize $dp.TotalVisibleMemorySize $dp.FreePhysicalMemory $dp.FreeVirtualMemory $dp.FreeSpaceInPagingFiles $dp.NumberofProcesses $dp.NumberOfUsers $page.InitialSize $page.Name $page.MaximumSize $srv.Configuration.MaxServerMemory.RunValue $srv.Configuration.MinServerMemory.RunValue $mAvailableMBytes $Buffercachehitratio $PLE
writeMemoryInfo $MemoryFileName $dp.csname $dp.TotalVirtualMemorySize $dp.TotalVisibleMemorySize $dp.FreePhysicalMemory $dp.FreeVirtualMemory $dp.FreeSpaceInPagingFiles $dp.NumberofProcesses $dp.NumberOfUsers $page.Name $page.InitialSize $page.MaximumSize $srv.Configuration.MaxServerMemory.RunValue $srv.Configuration.MinServerMemory.RunValue $mAvailableMBytes $Buffercachehitratio $PLE
}
Add-Content $MemoryFileName "</table>"
writeHtmlFooter $MemoryFileName
$date = ( get-date ).ToString('yyyy/MM/dd')
sendEmail [email protected] [email protected] "Memory Usage Report - $Date" $MailServer $MemoryFileName
[/expand]
Output
Wrapping Up
Now that you’ve learned some new things about SQL Server memory management, you’ll better understand SQL Server resources.
If there’s adequate RAM on the server, the data pages can have a longer life in the buffer pool which consequently results in a drastic reduction in the I/O needs.
While in most cases Database Administrators rely on default memory settings, we need to understand that the internals of memory requirements depend on the workload of the instance.
This article is a high-level walkthrough of SQL Server memory and its internals. Also, it covers the various reasons behind the performance bottlenecks caused by not setting the max memory.
I’ve included step-by-step instructions to set up and configure a memory report. The steps on how to set the SQL memory are also included. Further, we discussed various SQL components that contribute to the use of the available memory on the SQL Server environment.
One point to remember is that allocation and de-allocation of memory slow down startup. Therefore, if you have several applications stopping and starting on the same server, it may affect the performance. Similarly, if there are several other applications running on the same server, setting the min server memory and max server memory becomes more important to ensure optimal performance.
That’s all for now…
References
- Monitoring Memory Usage
- Importance of setting Max Server Memory in SQL Server and How to Set it
- Server Memory Server Configuration Options
