Brent Ozar - SQL Server DBA Rotating Header Image

SQL Server Performance Tuning

Performance tuning with SQL Server boils down to three steps:

  1. Capture hardware, OS and SQL metrics and analyze them for bottlenecks
  2. Capture queries if necessary to determine what queries are causing the bottlenecks
  3. Write up your findings and suggest fixes for the problem

Step 1: SQL Server Perfmon Tutorials

To gather the metrics, you can start by using Performance Monitor, a free performance monitoring tool that comes with Windows.  In these tutorials, I explain how it works, and I’d suggest going through ‘em in this order:

  1. Performance Tuning with Perfmon - how to set up Perfmon, what SQL Server Perfmon counters to track what the indicators mean.
  2. Data Mining Your SQL Server Perfmon Counters - want to take your Performance Monitor statistics to a new level?  I wrote an article on SQLServerPedia explaining how to use Microsoft’s free Table Analysis Tools for the Cloud plugins to dive deeply into your data.
  3. Estimating Performance Tuning Improvements - when we tell our managers that we want to make a few changes to the database or to the server, they’ll ask how much of a difference it’s going to make.  In this article, I show you how to explain it in terms that won’t get you in trouble by overpromising and underdelivering.

Step 2: Using Profiler to Trace Queries

I don’t have a good set of tutorials here explaining how to use Profiler, but if you know how to use it, I’ll give a few tips here about what to look out for when you’re interpreting the results:

  1. Example 1: Interpreting Load Test Results - I worked with a load test group and found a problem when they started their tests simultaneously. The more interesting part for you is near the end of the post when I analyze several of the metrics to find the underlying problematic queries.
  2. Example 2: Gather Traces Before You Upgrade to SQL Server 2008 - I got the chance in late 2008 to work with Jeff Atwood and friends at StackOverflow.com to troubleshoot some performance problems.

Before you go throwing money at a problem, though, take a look at some common SQL Server performance fixes:

  • Primary Keys and Indexes - I explain the concepts behind keys and indexes using phone books as an example.  Indexes have huge impacts on performance, and if you master these you can make your server go a whole lot faster without spending more money.
  • SQL Server Partitioning - it isn’t the answer to everything, and I explain when to use it.
  • Steel Cage Blogmatch: How to Configure a SAN - I argue with Jason Massie of StatisticsIO.com about the right way to configure a SAN for performance.
  • SQL Server 2005 Setup Checklist - some simple configuration tweaks can get you 20-30% performance increases right from the start without spending any extra money.

Step 3: Write a SQL Server Performance Report

After you’ve found the problem queries that are causing bad metrics, you need to document your findings in a way that will convince management to execute on your plan.  I’ve got a separate article just on that called How to Write a SQL Server Performance Report.