DB Perf Is Like Cake: The Logical Layer

25 March 2011

“Tune it, and perf will come”

Layer 2 of The Layer Model is the logical layer.

You could also use the OPTION (LUDICROUS SPEED) query hint

The logical layer represents the configuration of your indexes, statistics, and the queries that are run on your system. If you tune your indexes and your worst queries, you will get better performance. The best way I know of to do this is to use DMVs and logging.

Before we start, however, it’s important to remember that you’ll never run out of ways to tune performance. It is important to have goals before doing performance tuning. Know when to stop and declare victory. The 80/20 rule works really well here.

Index Tuning

How do you do index tuning? You ESCape! Eliminate. Swap. Create.

  1. Eliminate. Eliminate indexes that are never used. All they do is suck up space and slow down your database’s insert/update/delete/merge operations.
  2. Swap. Are the remaining indexes the best configuration for what you have? Probably not. Changing their included column configuration, index column order, or adding an additional column could give you better performance without much extra overhead.
  3. Create. Create new indexes that are needed. This should only be done a while after you’ve swapped indexes around. Then it’s time to go looking in the index DMVs again to figure out what additional indexes might be useful.

There have been many, many blog posts on index tuning. My favorites are below. These links also have pointers to a lot of additional content. Brent Ozar’s video on index tuning SQLServerPedia’s best practices on creating tuning. Kimberly Tripp’s posts on spring cleaning for your indexes. SQLServerPedia’s list of index-related DMV queries.

Query Tuning

Query tuning can tricky. I have found that having the right data before you start makes everything easier. So, first, collect all of the query information you can get. Get lists of queries that have run, with details on CPU time, execution counts, and missing indexes or missing stats mentioned in the query plan. The sys.dm_exec_query_stats DMV is great for this.

From there, I would suggest you:

Now you have a big list of queries, along with details of their usage. The next step is to prioritize what to work on. I like to prioritize query optimization based on user pain, error counts, and then system stability.

  1. User Pain. If a user is complaining about a particularly slow sproc or query, I’ll see if it can be optimized. If nothing else, doing this gives me some breathing room to work on other issues. It also makes my IT organization look responsive, which is always a plus.
  2. Error counts. If you have a query that is causing lots of deadlocks or blocking, work on it next. It’s probably creating cascading performance problems throughout your system.
  3. System stability. Look at the rest of your queries. Rank them by CPU time and I/O usage, starting with the worst offenders. Tune those first. Tuning a modest number of queries often results in dramatic performance gains for the entire system.

Summary

The Good: It’s easy to find & eliminate bottlenecks one by one, using fairly simple query tuning tricks and the DMVs built into SQL Server.

The Bad: Logical-layer tuning requires modest development time, and it can be a little risky. Changing indexes can slow your system down further. Query tuning can return incorrect results to users, or slow down the queries even more.

The Ugly: That web application that runs 10 million SELECT queries per minute against your database? It’s still there. That reporting sproc that does a 25-way join with GROUP BY? It can only be tuned so much. And no amount of tuning will make that 50TB database run fast on your Pentium Pro-based server with 2GB of RAM.