Database Reference
In-Depth Information
Method or Tool
Available?
Comments
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.
Coding Implications
Because you have no access to the server-side configuration settings of SQL Azure, such as disk
configuration, memory allocation, CPU affinitization, and so forth, you need to place more emphasis on
the quality of your SQL statements—and, now more than ever, your network traffic. Indeed, the number
of network roundtrips your code generates and the number of packets returned have an impact on
performance because the connection to SQL Azure is a far link and the communication is encrypted.
Your performance-tuning exercise should include the following areas:
•
Connection pooling
. Because establishing a new connection requires multiple
network roundtrips by itself and can affect your application's performance, you
should ensure that your connections are pooled properly. In addition, SQL Azure
will throttle you if you establish too many connections. This behavior is controlled
by the denial of service (DoS) feature briefly discussed in Chapter 4.
•
Packet count
. Because the time spent to return data is greater than you may be
used to, you need to pay attention to SQL code that generates too many packets.
For example,
Print
statements generate more network traffic than necessary and
should be removed from your T-SQL if at all possible.
•
Indexing
. You may remember from chapter 2 that SQL Azure may throttle your
connection if it detects that your statement is consuming too many resources. As a
result, proper indexing becomes critical when tuning for performance.
•
Database design
. Of course, certain database designs are better than others for
performance. A heavily normalized design improves data quality, but a loosely
normalized database typically improves performance. Understanding this tradeoff
is also important when you're developing in SQL Azure.
Tuning Techniques
Let's dive into the specifics of performance tuning, keeping in mind what you've learned so far. You start
by looking at database tuning capabilities and then move up the stack, all the way to the client library
making the actual SQL call.
