SQL Server Memory

Question

SQL Server Memory

Ginger Daniel

Mr or Mrs. 500

Points: 525
More actions
December 13, 2015 at 10:45 pm

#303284

Comments posted to this topic are about the item SQL Server Memory

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Theo Ekelmans SSCarpal Tunnel Points: 4592 More actions · Answer 1

Good article,

But i have read several articles by i.e. Paul Randal en Jonathan Keyhayas that just taking 300 sec for PLE is no longer a good rule of thumb. This is because that rule stems from the olden times that servers were much slower and had much less memory.

Jonathan gives a better one: (DataCacheSizeInGB/4GB *300).

I have used this rule of thumb as a value where i start to investigate for a few years now, and it works for me 🙂

The full article is here: http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

December 14, 2015 at 6:43 am

#1845794

Thanks for the good educational article.

blviscuso Valued Member Points: 52 More actions · Answer 3

Good information and detail. In your article you state "... and it is highly recommended to set the limit in the Max Memory Setting as a best practice. That way Windows OS is always guaranteed to have a certain amount." In the past I have read the minimum RAM reserved for the OS should be 4GB or 10% of total RAM, whichever is greater. Are there specific recommendations regarding how much RAM should be reserved for the OS as general rules of thumb?

Mary Kay Malensek Valued Member Points: 62 More actions · Answer 4

Mary Kay Malensek

Valued Member

Points: 62

December 14, 2015 at 9:28 am

#1845852

Thank you. Very simple and clear!

ryan.price SSC Journeyman Points: 82 More actions · Answer 5

Good article. Gets straight to the point.

The batches/sec and compilations/sec part was immediately useful.

On my server, the batches/sec and compilations/sec are both over 29 million. The ratio makes sense.

Could the counter be reporting an absolute count?

Ginger Daniel Mr or Mrs. 500 Points: 525 More actions · Answer 6

Thanks for your comment, blviscuso. Regarding your question about the correct amount of RAM to reserve for the OS... a good rule of thumb is to allocate:

1 GB to the OS,

plus 1 GB for every 4 GB of RAM on your machine up to 16 GB,

plus 1 GB for every 8 GB of RAM on your machine above 16 GB.

Hope this helps!

sidj6 SSC Rookie Points: 36 More actions · Answer 7

sidj6

SSC Rookie

Points: 36

December 14, 2015 at 6:18 pm

#1845972

Thank you. Nice and simple.

Karl Marx SSC Rookie Points: 49 More actions · Answer 8

Glad my boss did not know this either when he approved more RAM 🙂

ryan.price SSC Journeyman Points: 82 More actions · Answer 9

The advice for RAM requirements is spot on for my AMD Opteron based server.

blviscuso Valued Member Points: 52 More actions · Answer 10

Ginger, thank you for the short and simple rules to follow. Very much appreciated!

richlion2 SSCrazy Points: 2484 More actions · Answer 11

Hello,

I can see people are still praising the article and yes, it's an excellent piece. I have actually wrote the hints into a health-check script, it's in one piece of code and well organized.

It is important to stress that although the official MS documentation says SQL-Server defaults are handled automatically, I recenlty stumbled into a huge bottleneck and disovered that actually some heavy duty SQL have basically caused the Win Server to be starved from memory - it was stalling and people complained the SQL database is slow. When I tried to analyze the situation using the Perfmon it was actually crashing. When I managed to get it running the message was something in the sort "out of memory". Conclusion?

Myth No. 1. SQL-Server and Windows Server cannot actually make out what should be done. Recently my manager was quoting outside sources that SQL-Server does not need any maintenance and looks after itself. There is nothing more false.

Myth No. 2. SQL-server does not need a DBA

Myth No. 3. It is easy to install an SQL-Server, because it does not require tuning

Myth No. 4. A database server needs all the memory the server has. This only means the application is poorly designed and/or has rougue users abusing the system

I made a change then based on recommendations in a book:

SQL Server 2012 - Internals and Troubleshooting by C.Bolton, J. Langford, G. Berry, G.Payne, A. Banerjee, R. Farley ( I am not advertising 😛 )

Determining the Maximum Potential for Requirements Outside SQL Server
This option is the most popular, as the aim is to calculate the worst-case scenario for memory
requirements other than SQL Server’s. You should allow for the following:
? 2GB for Windows
? xGB for SQL Server worker threads. You can find your max workers count by querying
sys.dm_os_sys_info. Each thread will use 0.5MB on x86, and 2MB on x64.
? 512MB, if you use linked servers, extended stored procedure dlls, or objects created using
Automation procedures (sp_OA calls)
? 1–3GB, for other applications that might be running on the system, such as backup
programs or anti-virus software
For example, on a server with eight CPU cores and 64GB of RAM running SQL Server 2012, a third-party backup utility, and virus checker, you would allow for the following:
? 2GB for Windows
? 1GB for worker threads (576 3 2MB rounded down)
? 512MB for linked servers, etc.
? 1GB for the backup program and virus checker
For a total of 4.5GB, you would configure Max Server Memory to 59.5GB.
Chapter 3 - Understanding Memory
Min and Max Server Memory
Here are some example scenarios to illustrate this point:
? Max Server Memory is 30GB on a server with 32GB RAM. PLE averages 10,000 and
Available MBytes is 90MB. Solution: Lower Max Server Memory by at least 500MB.
? Max Server Memory is 46GB on a server with 50GB RAM. PLE averages 10 and Available
MBytes is 1500MB. Solution: Increase Max Server Memory by 500MB to 1000MB.
? Max Server Memory is 60GB on a server with 64GB RAM. PLE averages 50 and Available
MBytes is 20MB. Solution: Lower Max Server Memory by 100MB and buy more RAM
(quickly).

I reduced the Max Server Memory parameter and this basically cured the majority of the initial problems. The default setting was set to 2TB :hehe:.

Ryszard

Clive Strong SSChampion Points: 11222 More actions · Answer 12

Nice post. My only grumble would he the reference of 300s as a threshold for the PLE counter. It's a really old piece of information which may not be relevant.

Assume you have 1TB allocated to your host. Would keeping 1TB of pages in the buffer pool for 300s be acceptable performance?

chudman SSCrazy Points: 2452 More actions · Answer 13

This is a really nice article, Ginger. Having shuffled SQL memory around for the past two decades, I appreciate your efforts. I want to mention the SQL memory calculator originally created by Vlad Catrinescu that can be downloaded from here: http://sqlmem.codeplex.com/ It takes into account number of threads, stack size, and operating system needs.

Thanks again
Jeff Bennett

Lisa Slater Nicholls Hall of Fame Points: 3453 More actions · Answer 14

I'd like to add this solution for making the relevant set of counters "stick" in perf mon
https://serverfault.com/questions/339282/how-can-i-save-the-counters-setup-in-windows-performance-monitor

>L<