Today I want to announce a tiny web application for calculating max memory in the SQL Server to improve the overall performance in you SharePoint environment:
One of the important actions to improve the performance in SharePoint is fine tuning of the SQL Databases. Regarding the databases one of the crucial settings is the Max Memory Setting. You can read more about database optimizations for SharePoint in a whitepaper written by SharePoint MVP Vlad Catrinescu. The document is available on the SharePoint Community Web Site:
Vlad Catrinescu also created a windows application for calculating the max memory for SQL which can be downloaded from codeplex (GPL v2):
This windows application has inspired me and my colleagues to create the web based application SQL Max. The goal of the SQL Max web app is to take Vlad’s idea a step further and make it available directly in your browser, on desktop at work, or on the go in your mobile. This mobile web app will also be available for offline access. Simply put, why should you dowload a zip file, extract and run the .exe file? Perhaps you cannot run executable files due restrictions, or perhaps you are not running Windows at all.
How it works
The most important part of the solution is the formula. The original formula (C#) is provided by Vlad Catrinescu is as follows:
SQL Max Memory = TotalPhyMem – (NumOfSQLThreads * ThreadStackSize) – (1GB * CEILING(NumOfCores/4)) – OS Reserved
NumOfSQLThreads = 256 + (NumOfProcessors*- 4) * 8 (* If NumOfProcessors > 4, else 0)
ThreadStackSize = 2MB on x64 or 4 MB on 64-bit (IA64)
OS Reserved = 20% of total ram for under if system has 15GB. 12.5% for over 20GB
When you’ve filled in the values in the form, you’ll see the max memory in the blue box above:
How to apply this setting in the SQL Server
To apply the max memory follow these steps, the instructions come originally from technet:
- In Object Explorer, right-click a server and selectProperties.
- Click the Memory node.
- Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB). The minimum amount of memory you can specify for max server memory is 16 MB.
- Stina Qvarnström, @stinaq (Queen of Graphics)
- Johannes Milling, @johesmil (King of CSS)
- Erik Kronberg, @eakron (King of grunt)
- Björn Roberg (King of GetThingsDone)
- Vlad Catrinescu, @vladcatrinescu (King of the formula)
- Anatoly Mironov, @mirontoli (King of the Idea)