Sysadmins: Say No to Compulsive Tuning Disorder

As a new database administrator (DBA), I wanted to tune all the things. I made it my mission to optimize as much as possible; I didn’t want any query to take one second longer than necessary. Over time, I found myself collecting all the metrics, too, because I had no idea if they would prove helpful or not later on.

Then I came to understand that I suffered from what I call Compulsive Tuning Disorder (CTD).

At first, having CTD wasn’t an issue. My end users were happy when their applications and reports ran fast. My manager was happy that the end users were happy. Performance was so good that we would spend hours in meetings discussing proper naming conventions, if XML should be stored in the database or as files, and the use of tabs versus spaces (the answer, by the way, is tabs). Life was good.

It was about that time that I found myself being nominated as the default administrator for anything with the letters SQL in the title. Microsoft SQL Server Reporting Services? Sure thing. SQL Server Analysis Services? Yep, that was mine, too. SQL Server Integration Services? Of course. And who else would be the Microsoft SharePoint administrator? After all, SharePoint uses SQL Server, so naturally I would be in charge of SharePoint, too.

As any system administrator will attest, you will be asked to take on more responsibilities over time. Many of these systems will be outside your silo of expertise. The number and frequency of such requests increases when the industry enters a period where lots of new technology emerges at the same time.

This is where we are today with the cloud. There is a burst of new technologies being introduced at an increasing rate. It’s impossible to know all of them to any deep level. Trying to decide on one -- say, containers, or serverless computing -- is a gamble, as you have no idea which technology will have longevity. As such, with emerging technology, breadth is the new depth. It’s better to know a little bit about a lot.

For administrators stricken with CTD as I was, this can feel like a weight tied around your neck. It becomes more difficult to tune all the things, especially when so many things are new.

That’s why I now preach a minimalist approach to performance monitoring and tuning basics. By focusing on the core necessities, you can keep pace with all the tasks and systems you are assigned. With a minimalist approach, you can almost always have a quick answer for these two important questions:

1. Is this a problem?
2. What can we do about it?

For any new, or accidental, systems administrator, here are the key things you should keep in mind every day:

• Alerts require action: If you’re setting up rules in Microsoft Outlook for all your monitoring alerts, then you’re doing it wrong. If you configure an alert to send you an email, then it must require you to take action. If no action is needed, then just log the details to a file for review later.  
• Ask “why” five times: One of my favorite techniques in troubleshooting the root cause of an issue is to ask “why” five times in a row. By the time you get to the fifth “why,” you are close enough to the root cause of the issue. It’s there that you should focus your efforts.
• Tuning needs a clear goal: Suffering from CTD means that you could spend a lot of time performance tuning and never solve anything. Instead of turning knobs all day long trying to make your monitoring dashboard green, take the time to ask yourself, “What problem are we trying to solve?” Once you identify the problem, you can identify an acceptable resolution goal. This will also make your monitoring much more efficient.
• Solving resource consumption: When it comes to query resource consumption, there are only two possible choices for solving the issue: Use less, or purchase more. That's it.

As DBAs and, more broadly, any form of IT administrators, we already know how to track capacity for things like memory, disk, CPU, and network. But when we apply a minimalist approach to tuning, we’re able to decide quickly if the issue is worth the time to tune or to allocate more resources (i.e., throw hardware at the problem).

This approach helps will help you avoid the issues that arise after contracting a severe case of CTD.