Memory Utilisation

OK, let’s say this straight off the bat, SQL Server is a bully. It will sit on your server and use as much RAM as it can get its hands on if you let it. This is great for the performance of your SQL queries but if it’s fighting other processes for those resources then you may start running into issues.

 

But I’m running other processes on my SQL box, how do I throttle SQL?

Simple, this is a SQL box, not a crowded nightclub where everyone is knocking into each other and shouting for the bartender’s attention. You should be using dedicated hardware for your SQL server, move your other processors onto a separate server. SQL licences are expensive, why would you restrict its ability to do its job?

 

OK, I’ve got it on dedicated hardware, do I need to do anything?

Now this all depends on how your SQL instance is behaving. SQL will attempt to load all of its data into RAM where it can. Accessing data from here is much faster than having to go and retrieve it from disk (even if you’ve got it on lovely SSD’s). If you’ve got 5GB of data on a box with 12GB RAM then I doubt you’re seeing an issue, whereas if you’ve got 15GB of data on that same box then you’re likely to start running into problems (however, more data and you’re going to want to think about investing in more RAM, the cost of this pales in comparison to the cost of SQL licences).

 

OK, so I might have a RAM issue, what do I do about it?

SQL does have settings to enable you to set a maximum amount of memory that it can use, this can ease contention with things like the operating system or other background tasks. A good starting point is to leave 4GB available for the OS and limit SQL to using the rest. This does depend upon what else you have running on the box so may require tweaking. Try setting your max server memory to 4GB less than you have on the box and go from there. Monitor your memory usage and see if you can give SQL that extra bit more memory.

 

How to change SQL memory settings

I’ve restricted SQL to less memory, is this going to cause me a problem?

One of the main statistics that you can get out of your SQL instance is the Page Life Expectancy. This is quite a big topic so I’ll be following this up with its own post later.