I've been a database professional for 17 years, and for most of the past 13, I've focused on tuning SQL Server systems. I can sum up most of my performance-tuning philosophy in three principles. Take them to heart and you'll vastly improve your ability to tune databases.
1. Performance Problems Are Always Caused by the Application
When I speak about performance tuning I often provocatively exclaim at the beginning of my talk, "Applications cause all the problems." DBAs cheer and developers jeer. But you'll note that I don't say "Developers cause performance problems"—I say applications cause the problem. Here's my point: Imagine a database server that has no applications running on it or against it—does it have a performance problem? Silly question? Not really. Database performance problems happen when a workload is run against the database server. No workload, no problems. Pretty simple. What causes the workload? The application of course.
2. You Don't Have Time to Fix Everything, So Fix What Hurts the Most
You've got two queries. Query1 takes 10 minutes to run and Query2 takes 100 milliseconds. Which one do you fix? Most of the clever and crafty readers out there have probably sensed my trap. Of course the answer to this or any performance-tuning question is "It depends." Here's some more data. Query1 is run once a week, while Query2 is run 10 times per second. Hmm. Ten minutes of time over a week, or more than 1000 minutes of time a week? The answer is probably clearer now, unless we get more data, such as it's the president of the company who really cares about Query1. Best practices are great. Adhere to them as much as you can. But, I've never met an application that wasn't rife with broken best practices, and I've never seen an IT shop that had so much time that they could tune everything. I'm not suggesting that you shouldn't focus on the little things, because the little things really do add up over time. But in general, if you have to choose between fixing a big thing or a little thing, fixing the big thing is what matters most. That sounds obvious, but this advice can get lost in the real world when you can't always do the math to see which query is costing you more. I'll revisit this topic in an upcoming article and give you some great tips on how to do the math.
3. Fix Core, Root Problems—Not Symptoms
A DBA walks into a doctor's office and says, "Doctor, I have an emergency, my application is slow when my users press here." The doctor says, "Tell your users not to press there." Bada bing. Yeah, I know; I won't give up my day job.
Symptom: High disk utilization. Solution: Add more disk capacity. Hmm. What if we were just missing an index on a big table that was causing table scans?
Symptom: High CPU utilization. Solution: Add more CPU's. Hmm. what if we had a query that was doing a Cartesian product, but the answer came back OK (maybe because of a GroupBy or Distinct that was sucking up all of our CPU resources)?
Symptom: Overused network. Solution: Add more switches, or what ever the heck network engineers do to speed up those pesky networks. Hmm. What if all of our devs were fond of writing SELECT * From BigGiantTable clauses, when they only needed new columns and rows that were filtered by the client?
These examples might seem obvious, but they're not obvious in the real world, trust me. Most of my career has been based on the fact that most customers spend a lot of time focused on symptoms without focusing on the root cause of a problem. Inevitably, this approach simply shuffles performance pains from one place to another.
Alas, in the real world, it's not always easy to find the core problem. Unfortunately, if you don't know why something (i.e., the performance symptom you're looking at) is happening, then any remediation action is just a guess. You might get lucky, or you might not.
3 Ideas, 1 Core Principle
Here's a secret: I lied—there's only one core principle of performance tuning and the three statements above are simply different ways of thinking about the same subject. I'll expand more on that idea next month. In addition, I'll discuss practical ways you can apply these principles to your own database environments.
