Installing SQL is a well-documented, well thought through process. There are hundreds of articles, blogs and technical pages on how to properly configure your SQL instance during install. What is less well covered is what you need / should do once the install wizard is complete.
In pretty much every environment I have ever worked in, there has been (at least) 1 system installed by a sysadmin, developer or accidental DBA that I have had to clean up. These are not complicated fixes; these are the ‘icing on the cake’ for a SQL server install. Most of these fixes may not even currently be causing a problem on the server in question, but, for completeness sake, and in the long run these fixes will allow the server to function more efficiently and allow you to manage your time more effectively.
I am not going to delve too deeply into the causes of the problem described in this blog, as these are well documented elsewhere, but I will give you the information required to validate my reasoning (I hope).
Configure SQL Memory
This is one of the biggest causes of problems on over used SQL servers I have come across. So many times I have had a call from business saying their server is running slowly, applications are timing out and users are at the gates with their pitchforks.
Now, this could be caused by all sorts of things, but a quick look at the Performance Monitor counter ‘Memory: Available Mbytes’ often shows me that the server OS is starved for memory, with only a couple of hundred Mbytes or less showing in our performance counter. More quick investigations (using Performance Counter ‘SQLServer: Memory Manager – Total Server Memory (KB)’) shows us that SQL Server is taking most of the available server memory.
These symptoms are usually a sign that the Maximum server memory option in SQL has not been set, which means that SQL is using the default value of 2147483647 MB under the Server setting ‘Maximum server memory’. This value is the maximum amount of memory that SQL will assign to its internal Buffer Pool for storing cached execution plans and other data pages. Under most conditions this will not be a problem, SQL will happily share its memory with the OS, swapping out data to and from the buffer pool to accommodate other server activity.
Sometimes, however, SQL may be under a heavy load, other applications may be firing and a battle ensues between the OS and SQL as to where the available memory goes.
This issue however, is easily remedied. By setting the Maximum server memory, you are limiting the SQL buffer pool to a pre-defined amount of memory, ensuring that the OS will have enough to efficiently do its job.
So how much memory is enough for SQL? There is no easy answer to this question, unfortunately. Every server will be different and only time and a thorough investigation will allow you to find the correct answer. Setting the Max memory may not solve your issue at all, you may need to install more memory to deal with the possible resource bottlenecks on your server. The important thing after the initial installation is to set the value to something lower that the amount of available RAM on the server, so you don’t experience a slowdown as described above.
This begs the question, “how do I know what to set Max memory to after installation?” Generally I work with fairly beefy enterprise applications which have at least 12GB of memory available. My rule of thumb is to leave 4GB for the OS (Maximum server memory = 8GB) as a starter, but once the system is up and running and experiencing a general day to day load, you can revisit and tweak where necessary by visiting the Available Memory counters in Performance Monitor.
Another way to measure what you should set your server max memory to is by using the same performance counter that let us know there was a problem in the first place, ‘Memory: Available Mbytes’. Again, each server is different, but Slava Ok, who works at Microsoft and knows how memory works through SQL server, put the following table together as a recommended starting point:
|Amount of RAM Installed||Available MBytes|
|< 4 GB||512 – 1024 MB|
|4-32 GB||1024 – 2048 MB|
|32-128 GB||2048 – 4096 MB|
|128+ GB||4096 MB|
How do I change the Max Memory? This can be accomplished in the usual 2 ways, through SSMS or through a T-SQL script.
Changing Maximum server memory in SSMS
On your server go into SSMS and Right Click your server instance, select Properties and Memory, enter your new value and click OK.
Changing Maximum server memory using T-SQL
EXEC sys.sp_configure N’max server memory (MB)’, N’8192′
RECONFIGURE WITH OVERRIDE
This should help you avoid any potential problems with the SQL Buffer Pool being too greedy.