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!

Windows 8 Client OS

• Windows 8: 128GB RAM
• Windows 8 Professional, Enterprise: 512GB RAM

Windows Server 2012:

• Server Standard, Datacenter, Server Storage Standard, MultiPoint Premium, Server HyperCore: 4TB RAM
• Server Storage Workgroup, Server MultiPoint Standard, Server Win Foundation: 32GB RAM
• Windows 8 Essentials Server Soultion: 64GB RAM

It has been a while since I felt my laptop just didn’t have as much RAM as it could…. but the reality is that I don’t know what I would do with more than 8GB of RAM.  Ok, I really do… Hyper-V lab environment! However I do have a couple of servers with 128GB of RAM, and I don’t think I will soon come close to maxing that out for what I do!

Oh, by the way: VMware announced that its limit for ESXi 5.1 (launching September 11) will be 2 TB of RAM, the same as ESXi 5.0.

Related articles

• Client-Side Hyper-V: How Microsoft is changing the game (garvis.ca)
• Windows Server 2012: Release Dates (garvis.ca)