Simple SQL Memory Management

The Problem:

SQL Server DBAs have three nagging requests: more disks, more memory, and faster disks.  I can help with the last two of those by managing your memory better.

First of all, SQL Server is a memory hog.  It grabs memory and doesn’t want to let it go.  Unfortunately, few DBAs can even tell you the details of what is in your memory. You may get answers such as most of it is used for storing the data, which is correct while also being too generic to be of any use.

Second, SQL Server tends to benefit from faster disks because it had to flush data out of memory to make room to pull other data in, then it needs to pull the data it just flushed out of memory right back in.  This isn’t the only reason for faster disks, but this excess I/O is a major problem on almost every system.

On the business side of things, that typically means you’re being told you have three choices: buy more memory, buy faster disks, or accept increasingly slower performance. Worst of all, you know the first two don’t scale very well.  Many managers are either worried about or already against the wall where they can’t live without more memory and faster disks, but they already bought what they could at a reasonable price.  The last option of accepting it is an even bigger threat than begging for a larger budget. Just because this is typical doesn’t mean it’s a good situation or that your boss understands.

The Solution

This is where me being a different type of DBA makes a huge difference.  I can tell you exactly where the bulk of your memory is being used, in detail.  Then I can find ways to eliminate the need for a lot of it to be there, lessening the contention for this vital resource.  Once that begins to clear up, there’s less data being purged from memory which also makes it so there’s less data that needs to be read from disk.  Even better, this drop in memory and I/O requirements is scalable.

How do I do this?  Once I know what’s in memory, I try to justify the largest parts of it.  What queries ran to cause the data to be required, can those queries be tuned, can they benefit from more efficient indexing, or is there anything else I can do to make it so less data is required to be in cache?  It’s important to look at the whole picture, including if you have read-only copies of the data elsewhere so some of the load could be moved, and many other factors.

The Process

I evaluate your system, determine changes that should be made, and document those changes along with implementation and rollback scripts where appropriate.  At that point I hand the changes over to your team to push through your change control process.  Once it’s in production, we do it again starting with an overview of the previous changes and ending with the next set of recommendations.

Only a select number of changes are made for each month to make the process safer. Changing too much at one time can add risk to the process. Taking the time to evaluate a reasonable set of changes before moving on is a best practice that this method is designed around.

All of this is done during off-hours remotely, keeping costs down and all advice given in writing along with the reasoning for each change. My goal may seem self-defeating, but I want you and your team to know everything I do to the point that you could continue the work yourselves if you wanted to. I’d rather live off of recommendations than pass up an opportunity to teach.

The Price

I can’t put the word “Simple” in there and charge an uncertain rate, so I start doing this for a flat $600 for one set of recommendations per month. This is typically a good pace as it takes that long to make it through many change control processes. 

Depending on your needs, some of the work may get into more advanced tuning that takes longer or you may want changes at a different pace.  If and when it gets to that point, which will be after I’m familiar with your environment and you’ve seen the quality of my work first-hand, we can discuss rates, time estimates, and expectations.  Until we have discussed that ahead of time, you will never be charged more than the flat rate.

The Guarantee

All I need is an email saying “We won’t be moving these changes to production” and you won’t be charged for that month.