Database Reference
In-Depth Information
C H A P T E R 12
Performance Tuning
Designing for high performance is becoming more important when using a database running in the
cloud because development objectives tend to minimize roundtrips and the amount of data being
returned. This chapter provides an overview of some of the most common tuning and troubleshooting
techniques available in SQL Azure. Keep in mind that performance tuning is a very complex topic; as a
result, this chapter can introduce only selected techniques.
The techniques presented are similar to the ones available in SQL Server, although some of the tools
aren't supported in SQL Azure. Along the way, you walk through a few examples of how to improve the
performance of a SQL statement and the steps required to tune it.
What's Different with SQL Azure
Before diving into the specifics, let's review some of the things you need to remember when tuning your
SQL Azure databases. Some of the techniques you may be using today are available, but others may not
be.
Methods and Tools
Because SQL Azure is a hosted and shared infrastructure, it's important to understand which tuning
methods are available and which aren't. Table 12-1 outlines some of the key methods traditionally used
by developers and DBAs in tuning a database system. The term
system
is appropriate here because at
times you need to tune the database server, and in other instances you need to tune the actual database
code or even address database design issues.
The table lists some tuning methods and tools that you may be familiar with. It also indicates which
are available for use with SQL Azure. The unsupported tools listed in the table (such as SQL Profiler and
Perfmon) are typically those that are—say, in a SQL Server environment—used by DBAs and system
administrators.
Table 12-1.
Typical tuning methods and tools
Method or Tool
Available?
Comments
SQL Profiler
No
Tools using server-side traces, such as most auditing tools, SQL
Profiler, and the Index Tuning Wizard, aren't supported.
Execution plan
Yes
SQL Server Management Studio can display actual execution plans
against a SQL Azure database. You review this later in the chapter.
