Tuning SQL Server 2005 Databases

							Tuning SQL Server 2005 Databases
WDM
WDM


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 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.




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.




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.

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.
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?