Sakthivel Chidambaram recently created a calculator which can find out the max server memory value based on the input.
http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/19/cool-now-we-have-a-calculator-for-finding-out-a-max-server-memory-value.aspx
you can try the tool from http://blogs.msdn.com/b/sqlsakthi/p/max-server-memory-calculator.aspx
Then Jonathan Kehayias made a post to clarify why the calculator doesn't work
http://www.sqlskills.com/blogs/jonathan/2012/05/default.aspx Jonathan has another great post relative the max server memory http://www.sqlskills.com/blogs/jonathan/post/How-much-memory-does-my-SQL-Server-actually-need.aspx
This reminded me another max server memory formula which I follow up for many years posted by other MVP Glenn Berry
Its wonderful piano 7 GB in size. Grand piano esx logic sample pack. While GB will only use these from Garageband, Logic will load samples from both folders.Logic X or Garageband also have Yamaha Grand which is 700 MB and it sounds really nice.Tip: Buy Mainstage 3 for 29 USD and you get ALL samples from Logic X available in Garageband, including the full Steinway and Yamaha. In total it is about 60 GB of content!Tip 2: Buy it goes into sale often on plugin site down to 19 USD.
a little script which I used to run from central management server and configured the max server memory for all sql servers
DECLARE @curMem int DECLARE @maxMem int DECLARE @sql varchar(max) select @curMem=physical_memory_in_bytes/1024/1024 from sys.dm_os_sys_info SET @maxMem = CASE WHEN @curMem < = 1024*2 THEN 1500 WHEN @curMem < = 1024*4 THEN 3200 WHEN @curMem < = 1024*6 THEN 4800 WHEN @curMem < = 1024*8 THEN 6400 WHEN @curMem < = 1024*12 THEN 10000 WHEN @curMem < = 1024*16 THEN 13500 WHEN @curMem < = 1024*24 THEN 21500 WHEN @curMem < = 1024*32 THEN 29000 WHEN @curMem < = 1024*64 THEN 60000 WHEN @curMem < = 1024*72 THEN 68000 WHEN @curMem < = 1024*96 THEN 92000 WHEN @curMem < = 1024*128 THEN 124000 END SET @sql=' EXEC sp_configure 'Show Advanced Options',1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'max server memory','+CONVERT(VARCHAR(6), @maxMem)+'; RECONFIGURE WITH OVERRIDE;'
EXEC(@sql)
However, with Jonathan's post, that formula might not correct, and especially have on the big memory systems. There are 2 options which are mentioned in Jonathan's post
1. 'reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. This has typically worked out well for servers that are dedicated to SQL Server. '
2. '((Total system memory) – (memory for thread stack) – (OS memory requirements ~ 2-4GB) – (memory for other applications) - (memory for multipage allocations; SQLCLR, linked servers, etc)), where the memory for thread stack = ((max worker threads) *(stack size)) and the stack size is 512KB for x86 systems, 2MB for x64 systems and 4MB for IA64 systems. The value for 'max worker threads' can be found in the max_worker_count column of sys.dm_os_sys_info '
I think I will use the first option as an initial setup, then follow up Jonathan's post to monitor the system memory status, and adjust it as needed. here is the script for the option 1
--reserve 1 GB of RAM for the OS,
--1 GB for each 4 GB of RAM installed from 4–16 GB, --and then 1 GB for every 8 GB RAM installed above 16 GB RAM DECLARE @curMem int DECLARE @maxMem int DECLARE @sql varchar(max) select @curMem=physical_memory_in_bytes/1024/1024 from sys.dm_os_sys_info SET @maxMem = CASE WHEN @curMem < = 1024*2 THEN @curMem - 512 WHEN @curMem < = 1024*4 THEN @curMem - 1024 WHEN @curMem < = 1024*16 THEN @curMem - 1024 - Ceiling((@curMem-4096) / (4.0*1024))*1024 WHEN @curMem > 1024*16 THEN @curMem - 4096 - Ceiling((@curMem-1024*16) / (8.0*1024))*1024 END SET @sql=' EXEC sp_configure 'Show Advanced Options',1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'max server memory','+CONVERT(VARCHAR(6), @maxMem)+'; RECONFIGURE WITH OVERRIDE;'
EXEC(@sql)
except the max server memory, here are some other post regarding the sql memory setting:
1. Fun with Locked Pages, AWE, Task Manager, and the Working Set… http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx
2. Be Aware: Using AWE, locked pages in memory, on 64 bit
http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx
3. Q & A: Does SQL Server always respond to memory pressure?
http://blogs.msdn.com/b/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx
4. Importance of setting Max Server Memory in SQL Server and How to Set it
http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/12/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it.aspx
UPDATE: This blog is applicable for SQL Server versions till SQL 2008 R2. Memory Manager has changed in SQL 2012 and above
Of late, I'm observing that some of the customers are not setting up Max. Server Memory Properly or they never set Max. Server Memory at all in 64-bit SQL Server installations.
What will happen if I don't set Max. Server Memory?
Jul 07, 2018 A complete reskin and retexture of the Multiplayer module Napoleonic Wars for Mount & Blade: Warband to fit the campaign on the Napoleonic Role Play server. Embed Buttons To promote NRP Campaign and grow its popularity ( top mods ), use the embed code provided on your homepage, blog, forums and elsewhere you desire.
Working Set trimming, Operating System unresponsiveness, Performance Problems in other applications running on the same server, Downgraded Backup Buffers etc..
How should I set Max. Server Memory?
sp_configure 'max server memory (MB)',<Memory in MB>
Is Max. Server Memory the upper limit to which SQL Server will consume Memory in the box?
No. Max. Server Memory just limits the memory consumed by SQL Server Buffer Pool. Memory consumed by Non-buffer pool portion of SQL Server and memory consumed by external dll's loaded into SQL Server memory space is not controlled using Max. Server Memory
How can I calculate the value for Max. Server Memory?
In general, you should calculate Max. Server Memory using the formula:
Max. Server Memory for a SQL Server Instance =
(Total RAM available to the OS) -
{
(Memory needed by Operating System which gets allocated to memory pool, filesystem cache, PTE, desktop heap, Driver Images etc..) +
(Memory needed by Non-buffer Pool region of SQL Server which gets allocated to Multi Page Allocators, Worker Threads, COM, Extended SPs, Backup Buffers, CLR, Linked Server..) +
Memory required for SQL Server Agent, Replication Agents, Bulk Copy, SSRS, SSAS, SSIS, and Full Text +
Memory required for Log shipping file copy depending on the size of log backups (if LS is configured) +
Memory required for other SQL Server instances running in the box +
Memory required for other applications running in the box (Antivirus, Monitoring Softwares, Compression softwares etc..)
}
I'm sure this is not a tough formula. Please note that stack size of SQL Server in x64 is 2 MB so depending on the amount of worker threads calculated by SQL Server (SELECT max_workers_count FROMsys.dm_os_sys_info) you may need to deduct memory in the above formula. Also if you have enabled -g startup parameter then you need to deduct the memory in the above formula accordingly. Make sure that you collect these memory requirements during the peak load.
I'm giving some scenarios explaining how to set Max. Server Memory here:
Scenario #1:
I have Active-Active SQL Server instances running on Node A and Node B. I have 65 GB of RAM in each Node A and Node B. I have set Max. Server Memory for both the instances InstA and InstB to 60 GB leaving 5 GB to operating systems. Is it a right configuration?
NO.
Why?
Imagine a situation where both the instances are running on same node due to some issues on the other node. So if both the instances are memory hungry then you will have the worst performance possible. So for a perfect configuration, you should have 125 GB RAM on each node so that at any point, there will be no memory bottleneck immaterial on which node both the instances are running. Atleast you should have physical memory more than sum of Min. Server Memory of both the instances.
Scenario #2:
Activate 4k video downloader key. 4K Video Downloader Key downloads the videos in a quick manner with slowing your personal computer work speed. It offers you the service to download sound, video recording, and captions from the YouTube with the highly visible design. May 25, 2019 4K Video Downloader 4.7.3.2742 Crack Lifetime Serial Key. 4K Video Downloader Crack is a Powerfull and all in one application for anybody who would like to download videos from youtube. Its a great tool for easy download any type o video in one click. It is exclusive in its capacity to download not only specific videos but also whole playlists, all videos publicized on the picked account. 4K Video Downloader 4.7.3.2742 Crack Get Free. 4K Video Downloader Crack is a very powerful and fast speed great software for downloading videos from YouTube, Vimeo, SoundCloud, Flickr, Facebook, and DailyMotion. Using its help, we help you to save your preferred videos on the other disk by means of data files of the chosen format and quality. May 24, 2019 It supports downloading of all formats including MP4, MKV, FLV, 3GP and also Ultra HD video quality like 1080p, 720p and 2K, 4K videos. The greatest factor about 4K Video Downloader License Key is that it allows you to freely choose the specifically what quality and format of the video you desire to download.
I have 3 SQL Server instances running on box SQLSRVR. I have 65 GB of RAM in SQLSRVR. I have set Max. Server Memory on each of the three instances to 60 GB leaving 5 GB to operating systems. Is it a right configuration?
NO.
Why?
This setting means that all the three instances can grab upto 60 GB of memory and if that happens then all the three instances will be performing poorly. Refer to the formula above and set Max. Server Memory accordingly to all the three instances. If that is something not possible, atleast you should have physical memory on the box which is more than sum of Min. Server Memory of all the instances.
Here is snippet out of a doc I prepared for a customer:
What components within SQL Server use Memory from BPool?
Database Page Cache
Log caches Procedure/Plan cache Query Workspace Locks Connection context Optimizing queries System-level data structures etc.,
More info:
http://blogs.msdn.com/b/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx http://support.microsoft.com/kb/918483
Further Reads:
Server Memory Options - http://msdn.microsoft.com/en-us/library/ms178067.aspx awe enabled Option - http://msdn.microsoft.com/en-us/library/ms190731.aspx Enabling Memory Support for Over 4 GB of Physical Memory - http://msdn.microsoft.com/en-us/library/ms179301.aspx Using AWE and limitation of Memory which SQL Server 32-bit instance can access using AWE - http://msdn.microsoft.com/en-us/library/ms175581.aspx Lock Pages in Memory .. do you really need it? - http://blogs.technet.com/b/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx Windows: Physical Memory - http://blogs.technet.com/b/markrussinovich/archive/2008/07/21/3092070.aspx Server
Sakthivel Chidambaram, SQL Server Support
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |