The World According to Mitch

One of the benefits of virtualization is that you can segregate your SQL Servers from your other workloads.  Why?  If not then Microsoft SQL Server will hoard every last bit of resources on your machine, leaving scant crumbs for other workloads. 

Seriously… when you start the Microsoft SQL Server you will immediately see your memory usage jump through… or more accurately, to the roof.  That is because SQL Server is actually designed to take up all of your system’s memory.  Actually that is not entirely true… out of the box, Microsoft SQL Server is designed to take up 2TB of RAM, which means that in all likelihood a lot more memory than your computer actually has.

So assuming you have been listening to me for all of these years, you are not installing anything else on the same computer as your SQL Server.  You are also making sure that the virtual machine that your SQL Server is installed on (remember I told you to make sure to virtualize all of your workloads?) has its memory capped (Hyper-V sets the default Maximum RAM to 64GB).  You are doing everything right… so why is SQL performing slowly?

It’s simple really… Your computer does not have 2TB of RAM to give SQL Server… and if it did have 2TB of RAM, the operating system (remember Windows?) still needs some of that.  So the fact that SQL wants more than it can have can make it a little… grumpy.  Imagine a cranky child throwing a tantrum because he can’t have deserts or whatever.

Fortunately there is an easy fix to this one (unlike the cranky child).  What we are going to do is limit the amount of RAM that SQL actually thinks it wants… and when it has everything that it wants, it will stop misbehaving.

1) Determine how much RAM the server on which SQL Server is installed has.

2) Open Microsoft SQL Server Management Studio with administrative credentials.

3) Connect to the database (If you have multiple SQL databases on the same server see the note below)

4) In the navigation pane right-click on the actual SQL Server (the topmost item) and click Properties

5) In the Server Properties page navigate to Memory

6) Figure out how much 90% of your server’s RAM would be (in megabytes).  Use the following equation:

1GB = 1024*.90=921.6

8GB = 1024*8 (8192)*.90=7373

7) In the Maximum server memory (in MB) field type that number, then click OK.

That’s it!

**Note: The math we are using here allocates 90% of the total RAM to the SQL Server.  In the event that you have multiple SQL Server databases running on the same box you will have to do a bit of calculating to determine how much each database should use… and that can be a challenge.

If you only have the one database engine on your box, you should immediately notice marked improvements.  This breathing room does not mean that it is now time to pour more workloads onto the server… only that your SQL Server should be running a little better!