Databases Reference
In-Depth Information
Chapter 11
Performance Tuning
Designing for high performance becomes more important with a database that runs 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 Database. 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 Database. Along the way, you will 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 Database
Before diving into the specifics, let's review some of the things you need to remember when tuning your Azure
databases. Some of the techniques you may be using today are available, but others may not be.
Methods and Tools
Because a SQL Database runs on a hosted and shared infrastructure, it's important to understand which tuning methods
are available and which aren't. Table
11-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.
Table 11-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 Database Engine Tuning Advisor, aren't supported.
Execution plan
Yes
SQL Server Management Studio (SSMS) can display actual execution plans
against a SQL Database. You'll review this later in the chapter.
Perfmon
No
Any Windows monitoring tool that is typically used for performance tuning
is unavailable.
DMVs
Limited
A few dynamic management views (DMVs) are available and provide insightful
information about running sessions and statements previously executed.
Library metrics
Yes
ADO.NET provides library-level statistics that offer additional insight to
developers, such as the total processing time from the consumer standpoint
and bytes transferred.
