page contents

About the Post

Author Information

SQL Server Max Memory calculator from Microsoft is *not* intended for Database professionals…. really?

In an earlier article, I wrote about a few new tools for SQL Admins that were released by Microsoft.

As I have done in the past I will correct myself to ensure that I am passing along helpful information. This article is another example and I would like to explain my thoughts. When I wrote that article a few weeks ago, I did a few quick simple tests using that calculator and it seemed to be simple to use with good results. What I did not explore was testing for larger and more complex configurations, like Jonathan Kehayias (Blog | Twitter) did with his test. As you will see in his article, using the calculator made some really bad recommendations. I highly recommend three things moving forward here:

– Read Jonathan Kehayias‘ book. (Troubleshooting SQL Server: A Guide for the Accidental DBA)
– Read Jonathan Kehayias‘ blogs on SQL Server Memory. He has two excellent ones. (Here and more importantly this one as well)
– Do not use this calculator to configure your SQL Server’s memory configuration.

I agree with this blog (Wow… An online calculator to misconfigure your SQL Server memory!) in that this is an employee of Microsoft and he made a bad recommendation that can really hurt customers. We all make mistakes and I have made a few recently as well. However, the best thing to do to own up to your mistake and take corrective action. In my opinion I do not think Sakthivel Chidambaram is trying to correct the information with his tool here. He has to remember that he has identified himself as an employee of Microsoft and is also using a MSDN blog to present information. Most people, to include me, will take his word at face value and make a bad assumption that this tool has been thoroughly tested. The truth is that this information was not validated before being published. (Left out a lot of key points to consider, like installed drivers, like host bus adapters (HBAs) or FusionIO storage, monitoring agents that require memory, etc.)

Sure he made a disclaimer (later on after Brent Ozar and Jonathan Kehayias pointed out his errors on 24 May 2012) and changed his stance when more people found out about his tool (Excel spreadsheet) and pointed out the errors with his information. The problem is that it took me a few weeks to learn that information was incorrect and these disclaimers were not listed in his original article:

Few things to take care before you use this calculator:

IMPORTANT: This calculator is designed to help Novice SQL Database Administrators or someone who manages SQL Server but is really not an expert in SQL Server. So to put it simple, this calculator is meant for SQL Server professionals with Level 100 knowledge in SQL Server

Based on my support experience, lot of performance & memory related problems are because customer’s don’t set a Max. Server Memory and they don’t do that because they don’t know what to set. This is my personal interest to help those people who are in need but confused because there are so many recommendations around how to calculate & set this value.

I am sorry for my earlier recommendation and I stand corrected, plus this reminds of a trait that I have learned… test everything thoroughly.

Tags: , , , ,

Comments are closed.

Copy Protected by Chetan's WP-Copyprotect.