7. Provider Statistics
Last but not least, let's look
at the ADO.NET library's performance metrics to obtain the library's
point of view from a performance standpoint. The library doesn't return
CPU metrics or any other SQL Azure metric; however, it can provide
additional insights when you're tuning applications, such as giving you
the number of roundtrips performed to the database and the number of
packets transferred.
As previously mentioned, the
number of packets returned by a database call is becoming more
important because it can affect the overall response time of your
application. If you compare the number of packets returned by a SQL
statement against a regular SQL Server installation to the number of
packets returned when running the same statement against SQL Azure,
chances are that you see more packets returned against SQL Azure because
the data is encrypted using SSL. This may not be a big deal most of the
time, but it can seriously affect your application's performance if
you're returning large recordsets, or if each record contains large
columns (such as a varbinary column storing a PDF document).
Taking the performance metrics of an ADO.NET library is fairly simple, but it requires coding. The methods to use on the SqlConnection object are ResetStatistics()RetrieveStatistics(). Also, keep in mind that the EnableStatistics property needs to be set to true. Some of the most interesting metrics to look for are BuffersReceived and BytesReceived; they indicate how much network traffic has been generated. and
You can also download from
CodePlex an open source project called Enzo SQL Baseline that provides
both SQL Azure and provider statistics metrics (http://EnzoSQLBaseline.CodePlex.Com). This tool allows you to compare multiple executions side by side and review which run was the most efficient. Figure 10 shows that the latest execution returned 624 bytes over the network.
8. Application Design
Last, but certainly
not least, design choices can have a significant impact on application
response time. Certain coding techniques can negatively affect
performance, such as excessive roundtrips. Although this may not be
noticeable when you're running the application against a local database,
it may turn out to be unacceptable when you're running against SQL
Azure.
The following coding choices may impact your application's performance:
Chatty design.
As previously mentioned, a chatty application uses excessive roundtrips
to the database and creates a significant slowdown. An example of a
chatty design includes creating a programmatic loop that makes a call to
a database to execute a SQL statement over and over again.
Normalization.
It's widely accepted that although a highly normalized database reduces
data duplication, it also generally decreases performance due to the
number of JOINs that must be included. As a result, excessive normalization can become a performance issue.
Connection release.
Generally speaking, you should open a database connection as late as
possible and close it explicitly as soon as possible. Doing so improves
your chances of reusing a database connection from the pool.
Shared database account.
Because SQL Azure requires a database login, you need to use a shared
database account to retrieve data instead of using a per-user login.
Using a per-user login prohibits the use of connection pooling and can
degrade performance significantly, or even render your database unusable
due to throttling.
There are many other
application design considerations, but the ones listed here apply the
most to programming against SQL Azure.
