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.
End of Article
