http://www.sqlservercentral.com/blogs/sqlmanofmystery/2009/04/11/you-thought-sql-server-ate-up-memory/

Printed 2014/09/19 10:43PM

You thought SQL Server ate up memory…

By Wesley Brown, 2009/04/11

Often I tell clients better to much memory than too little. This can be applied to any database engine essentially. If your data set is growing over time you will end up using any memory that is not consumed today.

I’m here to tell you I don’t think it is the biggest consumer of memory in the Microsoft software catalog, There is a new champion! Introducing in this corner SQL Server 2008 x64, and his opponent Exchange 2007 SP1!

It isn’t even a contest, Exchange by a knock out.

The Exchange team have made some major changes in the core engine to blow the lid of how much memory it will use.

One of the things I remind people of from time to time is that Exchange is also a database of sorts and shares some of the common performance bottlenecks that SQL Server has. As a matter of fact, I saw the magic of sector alignment for file systems flogged on the Exchange side of the house long before it became more common knowledge on SQL Server. Now they have leapfrogged us again.

I live and breath SQL Server but I also dabble in other Microsoft stuff time to time mostly out of need or curiosity. This particular instance, I was setting up exchange for my private business.

Basically what they have done is make Exchange a 64 bit only application and lifted any memory restrictions on it what so ever. So, if there is 8GB on the server it will start up and gobble up that memory like it was cotton candy and happily beg for more. This was kind of a shocker to me, a SQL Server administrator who is use to having boxes with 32GB of ram or more for SQL Server and not see it utilized until the database engine has determined that it needs a particular piece of data cached in memory.

Where Exchange differs it assumes, from what I can glean, that it will need EVERY piece of data (i.e. peoples mail) at some point so it might as well load as much as it can now.

Now I can hear you saying “So what? Exchange should run on it’s own server anyway.” and you would be right, kind of. I host my enterprise stack on a single machine running Hyper-V Server. Realize this machine only has to support about 10 users so I sized it at 2 quad core Xeons, and 16 GB of ram initially, knowing I could grow the memory as needed or even move virtual machines off onto a new server with very little pain. Microsoft and others are touting this as the way of the future, no better way to learn about it than by doing.

I assumed since I was only running a few mail boxes that Exchange wouldn’t need all that extra ram and I could give it to my SQL Server instance. Boy, was I wrong. So, I gave it the minimum 2GB it states in the requirements. I soon discovered that was like running Vista on 512MB of ram, sure you could do it if you didn’t mind going and getting a sandwich every time you clicked the mouse button.

I thought to myself “WOW, this thing is a real pig!” I looked at the size of all my data stores, a paltry 758MB, and wondered why 2GB wasn’t enough. As I type this I’m still not sure but I found a work around. Where in SQL Server you can easily tell it don’t dominate the memory by setting the min and the max available to it Exchange has no easy way (in the GUI) to do this. I eventually found a registry setting for how much memory the store (database engine) would take and set it to 1GB. And wouldn’t you know it, Exchange runs just fine that way. It doesn’t page to disk. It isn’t slow to send or receive mail. It didn’t cause the end of western civilization as we know it.

Finally, after years of telling people to get to be good friends with your Exchange admin.

They have more in common with you than you realize and may be able to help work with your SAN teams or your server admin teams on getting what you need for I/O or other hardware for your poor abused SQL Servers.

I can now tell the Exchange folks that they need to look at how SQL Server handles memory and data caching, you are running a mostly read only database with forward inserting rows(mail messages) and maybe, just maybe you shouldn’t cache every little thing right up front.

I’m not sure why the went with such an aggressive memory scheme, I am glad there was something I could do about it.

Who knew you needed a 64-way HP Superdome and 256GB of ram to really see Exchange shine?

-Wes


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.