Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Man of Mystery

Wes Brown is a PASS chapter leader and SQL Server MVP. He writes for SQL Server Central and maintains his blog at http://www.sqlserverio.com. Wes is Currently serving as a Senior Lead Consultant at Catapult Systems. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.

You thought SQL Server ate up memory…

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

Comments

Posted by J.Faehrmann on 15 April 2009

That was an interesting and fun read - thanks!

Posted by jac.putney on 15 April 2009

V entertaining and informative and as a SQL admin I am forwarding this article straight to my Exchange counterpart!

Posted by SQL Noob on 15 April 2009

check out the new HP Proliant G6 servers. IBM and Dell will have their own versions as well. On HP you can now run up to 144GB of RAM on a 1U server.

I think VM has a real future for SQL and Exchange, but you still have to manage IO by giving spindles to db and log files

Posted by rudy komacsar on 15 April 2009

It sounds as if they have given that overly customzed access backend engine a tanker truck full of steroids !!!

Posted by marco on 15 April 2009

When setting up a new server-rack for the (5-people) company I started to work for a year ago. I soon realised that Exchange was WWWWWAAAAAYYYYYYYYY too much overkill for such a small company and ditched it very quickly in favor for Google Mail Pro (hosts our own domain and works like a charm without *any* admin-overhead from my part).

Posted by Charles Kincaid on 21 April 2009

I remember 64 meg disk drives.  I was working with a hardware vendor that made caching controllers.  They brought me one with a 64 meg cache.  Defrag ran real fast and wrote the disk back in one go.  Those were the days.  Simple things brought great leap forward.  Today it's like trying to learn to play the Harpsichord.  The first thing you do is TUNE IT.

Leave a Comment

Please register or log in to leave a comment.