DatabaseTuning Up SQL Server 2005 Databases

Tuning Up SQL Server 2005 Databases

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

It’s a familiar problem for most developers. The application is
just about finished, the near-final bits are rolled out to the test
servers…and things aren’t quite fast enough to suit everyone. As you
scramble around looking for places to squeeze out another few percentage
points of performance, the database looks like a likely suspect. The
problem is that no one on the team is a real expert in the esoterica of
SQL Server indexing and physical architecture design. So what can you do
to make sure you’ve got things set up for optimal database performance?

Fortunately, SQL Server 2005 comes with a built-in answer to this
problem: the Database Engine Tuning Advisor. Combining a simple user
interface with a deep knowledge of SQL Server, this utility can help you
tune your databases for peak performance. In this article I’ll walk you
through using the Tuning Advisor and show you what it can do for you.

You Can’t Tune in a Vacuum

The first thing you need to understand when you’re attacking a database
tuning problem is that there is seldom (if ever) a single best way to set
up a database. To understand this principle, consider the very simple case
of a table holding customer information: should you create an index on the
LastName column or not? The answer is that it depends on whether and how
often you search by last name, sort by last name, or join the Customer
table to other tables by the LastName column. If you don’t do any of those
things, than an index on this column is pure overhead. On the other hand,
if every other database operation involves looking up customers by last
name, it would be extremely inefficient to not index that column.

The Tuning Advisor handles these issues by introducing the concept of a
workload. A workload is simply a mix of SQL statements that
indicates the “typical” uses of your database; it gives the Tuning Advisor
something to consider when deciding what recommendations to make. You can
supply a workload in several ways. If your database isn’t in use at all
yet, you may have to deliver the workload as a simple file of SQL
statements, typed directly into SQL Server Management Studio and saved to
disk. In this case, the workload is your best guess as to how you think
the database will be used.

But if the database is in active use, you can do better than that. The
other way to generate a workload is to use the SQL Server Profiler utility
to capture a trace file, using the tuning template. A trace file records
the actual activity in your database over a period of time. If you record
a substantial trace file – say, five megabytes or more, captured over a
period of days – then the Tuning Advisor can tell you what changes would
have made your database more efficient with the use that the database
really got. I recommend that you follow this path to tune with real-world
data, rather than guesswork, whenever possible.

The Tuning Process

To launch the Database Engine Tuning Advisor, select Microsoft SQL
Server 2005, Performance Tools, Database Engine Tuning Advisor from your
All Programs menu. When the utility launches, you’ll need to connect to
the server where the database that you want to tune resides. The Tuning
Advisor will then retrieve a list of all databases on the server and wait
for you to tell it what to do.

Tuning Advisor is capable of evaluating workloads that cross database
boundaries (it looks for USE DATABASE statements within the
workload). After choosing your workload, you can select both the database
where the tuning will be conducted (that is, the database that the Tuning
Advisor connects to when it starts running SQL statements) and the
databases to actually tune. As you can see in Figure 1, you can also
choose to limit your tuning to individual tables within your target
databases. This is a useful feature when you’re trying to tune only parts
of a very large database.

Selecting What to Tune

Figure 2 shows the available options for customizing the work of Tuning
Advisor. If you’re expecting a heavy load on your server, you will
probably want to cut off Tuning Advisor’s time before that load hits,
although constraining the time available to Tuning Advisor can limit the
effectiveness of its recommendations. You can also choose whether to limit
recommendations to indexing, or whether to look at partitioning strategies
as well. The latter are only likely to be effective if you can distribute
the database over multiple physical devices.

Selecting Options for Tuning Advisor

When you’re done setting things up, click the Start Analysis button and
stand back. Well, actually, you may want to go get a cup of coffee (or a
three-course meal) depending on the complexity of your database and the
size of your workload. Tuning Advisor will start cranking through the
information you’ve fed it, updating the user interface with progress
reports so that you know it’s not stalled. It compares the current
performance of SQL Server on each of your saved queries with its internal
knowledge of SQL Server’s workings, analyzing things to determine where
adding an index or creating a partition could result in improved
performance.

Looking at the Results

When Tuning Advisor finishes its work, it adds two new tabs to the user
interface: Recommendations and Reports. The Recommendations tab, shown in
Figure 3, cuts right to the chase. If you don’t know much about SQL
Server, and you just want to accept the tool’s expertise, you probably
won’t need to look any further than this.

Tuning Advisor Recommendations

Right at the top of the Recommendations tab, you’ll see Tuning
Advisor’s overall estimate of the improvement that it can make to your
database’s performance (in this case, a respectable 15%). Then comes its
list of how to make this improvement: specific indexes, indexed views, or
partitions to create. If you agree with the recommendations, just select
Apply Recommendations from the Actions menu, and you’re done! Note that
you can also select or deselect individual recommendations if you want to
fine-tune the list.

The Reports tab goes on to provide more details, in the form of 15
drill-down reports. These include:

  • The Statement Cost report, showing the estimated improvement for
    individual SQL statements within your workload.
  • The Index Usage report, showing how many statements use each
    index, now and after implementing recommendations.
  • The Index Detail report, with size and configuration information
    on every index.
  • The Database Access report, showing which databases your workload
    uses.

If you’re working within tight constraints (for example, if your disk
space is severely limited), you can use these reports to judge which
recommendations will get you the most “bang for the buck”. This helps you
if you’re only going to implement a subset of the overall recommendations
of the Tuning Advisor.

A Tool You can Grow With

Tuning a single database through the user interface will cover what 90%
of developers ever need to do with Tuning Advisor, but it’s worth knowing
that there are more options lurking for advanced use. For starters,
there’s a command-line version of the utility, dta, which is suitable for
automation if you have to tune a great many databases (or perform tuning
as part of an automated process, perhaps a continuous build process). Even
better, if you use the command-line version, there’s an XML input
specification that lets you supply “what-if” scenarios of your own,
letting Tuning Advisor judge the efficiency of changes you might be
thinking of making for other reasons that performance. This is an
excellent way to be forewarned of potential problems before they arise.

But even if you never get to such advanced scenarios, Tuning Advisor
should be in your bag of tools if you’re responsible for delivering SQL
Server 2005 databases. Unless you design databases full time (and maybe
not even then), you’re unlikely to deliver the perfectly optimal most
efficient design yourself the first time. Shouldn’t you take advantage of
such an easy way to get a performance boost at no cost beyond a few
minutes of your time?

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the Senior Technology Partner for Adaptive Strategy, a
Washington State consulting firm. When
he’s not writing code, Mike putters in the garden on his farm in eastern
Washington state.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories