Posts Tagged ‘ sqlserver.exe ’

SQL The Memory Hog

I’ve worked with Microsoft SQL Server for a long time. In the context of people who study SQL to a degree I won’t ever find possible (or palatable), I would not refer to myself as an expert. I have had my fair share of issues I’ve had to find resolution for and this one comes up frequently since it’s how SQL installs by default.

You just get your new SQL Server up and running and within a short while of using it, your machine is running unfathomably slow. You manage to get Task Manager open and find that sqlservr.exe is pretty much chewing up all the RAM. “That makes no sense,” you lament. “I don’t even have that much running on the SQL Server yet.”

The reason: By default, SQL Server sets the “Maximum¬†server memory” option to just a shade over 2 billion megabytes (or the largest possible value a 32 bit integer can hold:¬†2,147,483,647). Likely, your server doesn’t have 2 billion gigs of RAM so SQL Server just creeps northward, holding all your RAM hostage, whether you’re using it or not.

Fortunately, this is an easy problem to fix.

Resolution:

  1. Login to SQL Server Management Studio and connect to your server (with high privileges, obviously).
  2. Right click on the server name itself.
  3. Select Properties.
  4. In the Server Dialog Properties window, click Memory.
  5. Change the “Maximum server memory (in MB)” value to an appropriate portion of the RAM on your server.

Before changes:

SQL before

After changes (in a server with 32GB of RAM):

SQL after

Now bear in mind, setting this value too low will naturally cause performance issues for your server. You want to set it lower than the full value of the RAM on the server, but not so low that you’re severely restricting SQL Server’s ability to perform.

This is a first line solution. There may be issues with how your SQL code is written, OS-level performance misconfiguration or more advanced SQL Server configuration issues with paging and so on. But 99 times out of 100, on an initial, default installation of SQL Server, this solved the problem. You may need to test max memory values for what works on your particular setup in order to find the sweet spot. Enjoy.

Advertisements
Advertisements