If you are facing issues with SQL server memory, here are a few tips from the trenches that may help you keep up with what could be happening.
1. SQL server WILL consume all free memory
SQL server is a server class of application and it makes the assumption that it is one of the most important piece of application which is running on the machine. To this end, it simply grows to occupy all available memory in the system. By not releasing memory which is used by its various caches, SQL server is trying to ensure a higher level of performance whereby there are good chances that those cache objects and extra memory would be required again and again. However this assumption generally holds good only for the most well written applications, in which case it is best to let SQL server grow and increase the available physical memory. For all other cases, read on.
2.SQL server claims to free memory to applications as and when they require it
In fact it does do this. All you have to do is write a program, that claims HUGE amount of memory in one shot. SQL server will notice the relative lack of memory availability in the system, and can cut its flab to come back to normal.
HOWEVER, in normal scenarios, what i have found is that, the OS is much nimble than SQL server in managing its memory. So in a typically memory creep situation, where all the applications (including SQL server) are eating up more and more memory in a slow pace, the OS, ie windows will simply increase its page file size to accomodate more memory requirements. Now everyone knows what paging implies – a slower system. This situation is the bug bear of the SQL server memory management scenario.
3. What can you do
- Know about SQL server procedure cacheAlmost all memory that sql server caches or uses up is more useful and genuine than this cache, unless of-course your system is extremely well written such that only genuine entries remain here.My next blog entry would be about what this cache does and how it can bring down your system, along with commands to inspect this cache and its contents.
- Restrict SQL server memory requirementsOnce the procedure cache growth is under control, use the EXEC sp_configure command to to restrict the SQL server memory to a maximum beyond which it will not grow. Our installation includes scripts that restrict the SQL server memory to different limits based on the system configuration on which it is installed.However this is not recommended for applications where SQL server plays an extremely central role like data center applications and such, where the entire application revolves around SQL server.
This step is only advisable for those class of applications which might need to deploy SQL server along with other applications on the same server.
- How do you calculate the maximum size of memory that might be requiredYou could profile your SQL server memory growth for a typical work profile and then extrapolate it over the period the system needs to be running. Periodic backups and maintenance activities would have to be factored in depending on your application profile.
Explaining all this to customers is hard
When your application is set to run on a non dedicated box, along with other equally important applications, frequent queries could ensue about why SQL server is consuming so much memory etc. Most often for such classes of applications, SQL server might not really require as much memory as all that. In such cases, these steps are more relevant and the money and time wasted in support calls could be much better used elsewhere. In such cases restricting the SQL server memory growth could be a wise decision.