One of the SQL Server settings with critical impact that is often misconfigured is the maximum memory setting. SQL Server will attempt to take as much memory as possible and is not always eager to release it. This results in the operating system being forced to start actively using the page file. While SQL Server might seem perfectly happy and show good page life and buffer hit ratios, the operating system might in fact be suffering.
One of the signs that this is happening are entries in the SQL Server logs that look like this:
[Something] is marked for unload due to memory pressure.
This doesn’t necessarily mean that your machine doesn’t have enough memory, but it does mean that your operating system isn’t getting its fair share. Ironically, the more resources you put into your server, the more resources your operating system needs to work well. There are some older rules residing on the internet telling you to reserve 4 GB for the operating system and to allocate the rest to the SQL Server. This worked like a charm up to approximately 16 to 20 GB, but with current machines having way over 512 GB this rule has been overruled. The same happened with the page file: if your SQL needs a page file bigger than 16 GB, you’re already in trouble.
First of all, paging out means writing to the page file, which dramatically increases access latencies and makes both your server and response times unreliable. Secondly, when your operating system starts to page itself out, the system becomes sluggish and this could cause your server to become unstable. SQL Server still needs your operating system for all the I/O and network operations, which means that if the operating system becomes slower, so do your transactions.
Things get really problematic when your host bus adapters don’t have enough memory to do their job, causing the I/O to freeze at intervals. As always, this problem only gets bigger if you’re virtualizing with memory overcommitment or running multiple instances on one machine. Especially if you’ve enabled the ‘lock pages in memory’ policy for your SQL Server. Of course, this changes if you use SSD or memory-based disks for your page file, but why would you use such expensive resources when adding memory is much cheaper and more reliable/durable? What people tend to forget is that SSDs require a lot of memory themselves to function properly.
The only good answer is: by iteration. But unfortunately this isn’t applicable in large environments where we have tens or even hundreds of SQL instances running. With a stable server you should always try to get stable RAM management, with the usage of the page file limited and the RING_BUFFER_RESOURCE_MONITOR showing few changes in memory distribution.
Using a modified version of Jonathan Kehayias’s formula described in his free ebook “Troubleshooting SQL Server: A Guide for the Accidental DBA“, we can calculate a good starting point. This book was written in 2011, when servers with terabytes of RAM were still fairly uncommon, so I’ve adjusted it a bit to take the bigger machines into consideration.
My script takes into account multiple instances, but doesn’t take into account data warehouses or SSAS/SSRS/SSIS. For these types of machines a different kind of calculation is needed. And as always, this isn’t a one-size-fits-all solution. But it should give you a starting point for checking if your currently defined setting is in the range of the expected value and a better value than the defaults. If you’re already tuning this setting, you’ll probably want to look at your ring buffers and then use the query.
DECLARE @NumberOfInStances tinyint=0 -- 0 is the default, taking all discoverable instances
DECLARE @EvenlyDevided bit=1
DECLARE @Instance01Percentage tinyint=50
DECLARE @MaxPercentCompliance tinyint=85
DECLARE @MaxCalculated float
DECLARE @CurrentlyAllocated float
@NumberofInstances: If you have several instances installed but some are disabled or only run in disaster scenarios, you may want to manually adjust this figure to the number of active instances.
@EvenlyDivided: If you set this to 0, not all instances will be treated equally. For example, you might have an SQL Express installed. You will probably also want to change this setting if you’ve changed the @NumberOfInstances parameter.
@Instance01Percentage: Gives the percentage of memory allocated to the current instance, not taking into account the settings of the other instances.
@MaxPercentCompliance: Indicates the compliance between your own setting and the calculated value.
Define or get the number of installed instances:
IF @NumberOfInStances=0
BEGIN
DECLARE @GetInstances TABLE
( Value nvarchar(100),
InstanceNames nvarchar(100),
Data nvarchar(100))
Insert into @GetInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'
Select @NumberOfInStances=COUNT(*) from @GetInstances
END
Get the currently allocated amount of memory:
SET @CurrentlyAllocated=(SELECT CAST(value_in_use as float) FROM sys.configurations
WHERE name like 'max server memory%')
Check the parameters:
--Check Params
IF (@EvenlyDevided = 0 AND NOT @Instance01Percentage between 1 AND 100)
BEGIN
PRINT 'Input not supported, switching to EvenlyDevided'
SET @EvenlyDevided = 1
END
Calculate the max server memory for these conditions:
SET @MaxCalculated = (SELECT CASE WHEN @NumberOfInStances=1 THEN
FLOOR(CEILING(CEILING(physical_memory_in_bytes/1073741824.0)-(1+CASE
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 16 THEN FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/4.0)
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 32 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-16)/8.0)+4
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 64 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-32)/8.0)+6
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 128 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-64)/16.0)+8
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 256 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-128)/32.0)+12
ELSE FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/64.0)+16
END))*1024.0)
WHEN @EvenlyDevided=1 THEN
FLOOR(CEILING(CEILING(physical_memory_in_bytes/1073741824.0)-(1+CASE
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 16 THEN FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/4.0)
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 32 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-16)/8.0)+4
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 64 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-32)/8.0)+6
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 128 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-64)/16.0)+8
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 256 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-128)/32.0)+12
ELSE FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/64.0)+16
END))/@NumberOfInStances*1024.0)
WHEN @EvenlyDevided=0 THEN
FLOOR(CEILING(CEILING(physical_memory_in_bytes/1073741824.0)-(1+CASE
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 16 THEN FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/4.0)
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 32 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-16)/8.0)+4
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 64 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-32)/8.0)+6
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 128 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-64)/16.0)+8
WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 256 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-128)/32.0)+12
ELSE FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/64.0)+16
END))*@Instance01Percentage*10.240)
ELSE 0
END
FROM sys.dm_os_sys_info )
Plot the results:
PRINT 'Starting calculation with the following parameters: '
PRINT '@Evenlydevided : '+CAST(@Evenlydevided as varchar(4))
PRINT '@Instance01Percentage : '+CAST(@Instance01Percentage as varchar(4))
PRINT '@MaxPercentDerivation : '+CAST(@MaxPercentDerivation as varchar(4))
PRINT '@NumberOfInStances: '+CAST(@NumberOfInStances as varchar(4))
PRINT '@CurrentlyAllocated: '+CAST(@CurrentlyAllocated as varchar(20))
PRINT 'Calculated Max Memory to be: '+CAST(@MaxCalculated as varchar(20))
PRINT 'Start checking existing parameters'
Report if there is an issue (and also generate a configure script):
SELECT
CEILING(physical_memory_in_bytes/1048576.0) as [Physical Memory_MB],
CEILING(physical_memory_in_bytes/1073741824.0) as [Physical Memory_GB],
CEILING(virtual_memory_in_bytes/1073741824.0) as [Virtual Memory GB] ,
@CurrentlyAllocated as CurrentlyAllocated,
@MaxCalculated as Max_SQL_Memory,
'EXEC sys.sp_configure N''max server memory (MB)'', N'''+
CAST(@MaxCalculated as varchar(20)) +'''
GO
RECONFIGURE WITH OVERRIDE
GO' as command,
CASE WHEN (@CurrentlyAllocated between (@MaxCalculated * (@MaxPercentCompliance/100.0)) AND (@MaxCalculated * 1+((100-@MaxPercentCompliance)/100.0))) THEN 0
ELSE 1 END as warninglevel
FROM sys.dm_os_sys_info
Example of a result set:
Starting calculation with the following parameters:
@EvenlyDivided: 1
@Instance01Percentage: 50
@MaxPercentDerivation: 85
@NumberOfInstances: 3
@CurrentlyAllocated: 19968
Calculated Max Memory to be: 13312
Start checking existing parameters
© 2023 Kohera
Crafted by
© 2022 Kohera
Crafted by
Cookie | Duration | Description |
---|---|---|
ARRAffinity | session | ARRAffinity cookie is set by Azure app service, and allows the service to choose the right instance established by a user to deliver subsequent requests made by that user. |
ARRAffinitySameSite | session | This cookie is set by Windows Azure cloud, and is used for load balancing to make sure the visitor page requests are routed to the same server in any browsing session. |
cookielawinfo-checkbox-advertisement | 1 year | Set by the GDPR Cookie Consent plugin, this cookie records the user consent for the cookies in the "Advertisement" category. |
cookielawinfo-checkbox-analytics | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics". |
cookielawinfo-checkbox-functional | 11 months | The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional". |
cookielawinfo-checkbox-necessary | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary". |
cookielawinfo-checkbox-others | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other. |
cookielawinfo-checkbox-performance | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance". |
CookieLawInfoConsent | 1 year | CookieYes sets this cookie to record the default button state of the corresponding category and the status of CCPA. It works only in coordination with the primary cookie. |
elementor | never | The website's WordPress theme uses this cookie. It allows the website owner to implement or change the website's content in real-time. |
viewed_cookie_policy | 11 months | The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data. |
Cookie | Duration | Description |
---|---|---|
__cf_bm | 30 minutes | Cloudflare set the cookie to support Cloudflare Bot Management. |
pll_language | 1 year | Polylang sets this cookie to remember the language the user selects when returning to the website and get the language information when unavailable in another way. |
Cookie | Duration | Description |
---|---|---|
_ga | 1 year 1 month 4 days | Google Analytics sets this cookie to calculate visitor, session and campaign data and track site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognise unique visitors. |
_ga_* | 1 year 1 month 4 days | Google Analytics sets this cookie to store and count page views. |
_gat_gtag_UA_* | 1 minute | Google Analytics sets this cookie to store a unique user ID. |
_gid | 1 day | Google Analytics sets this cookie to store information on how visitors use a website while also creating an analytics report of the website's performance. Some of the collected data includes the number of visitors, their source, and the pages they visit anonymously. |
ai_session | 30 minutes | This is a unique anonymous session identifier cookie set by Microsoft Application Insights software to gather statistical usage and telemetry data for apps built on the Azure cloud platform. |
CONSENT | 2 years | YouTube sets this cookie via embedded YouTube videos and registers anonymous statistical data. |
vuid | 1 year 1 month 4 days | Vimeo installs this cookie to collect tracking information by setting a unique ID to embed videos on the website. |
Cookie | Duration | Description |
---|---|---|
ai_user | 1 year | Microsoft Azure sets this cookie as a unique user identifier cookie, enabling counting of the number of users accessing the application over time. |
VISITOR_INFO1_LIVE | 5 months 27 days | YouTube sets this cookie to measure bandwidth, determining whether the user gets the new or old player interface. |
YSC | session | Youtube sets this cookie to track the views of embedded videos on Youtube pages. |
yt-remote-connected-devices | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt-remote-device-id | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt.innertube::nextId | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
yt.innertube::requests | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
Cookie | Duration | Description |
---|---|---|
WFESessionId | session | No description available. |