Mr or Mrs. 500
Comments posted to this topic are about the item SQL Server Memory
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/
Thanks for the good educational article.
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?
Thank you. Very simple and clear!
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?
Mr or Mrs. 500
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!
Thank you. Nice and simple.
Glad my boss did not know this either when he approved more RAM 🙂
The advice for RAM requirements is spot on for my AMD Opteron based server.
Ginger, thank you for the short and simple rules to follow. Very much appreciated!
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
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:.
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?
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.
Hall of Fame
I'd like to add this solution for making the relevant set of counters "stick" in perf mon
Viewing 15 posts - 1 through 15 (of 16 total)