Performance Tuning - Pro SQL Azure - page 244

displaying data, SQL Azure slows down to match the speed of the client; the slowdown is a function of
TCP (Transmission Control Protocol) programming and has nothing to do with SQL Azure. As a result,
total_elapsed_time shows the entire time it takes to execute the statement and the client time necessary
to finish fetching the data. The total_elapsed_time is the same than the Duration measure returned by
SQL Profiler.

Connection Pooling
Earlier, this chapter mentioned that connection pooling is an important consideration for performance.
Although this statement is generally accurate in the world of database programming, it becomes critical
for SQL Azure. A poorly designed application may create too many connection requests, which can end
up flooding SQL Azure. If too many connection requests are established, your connections will be
throttled , meaning that you can no longer connect for a period of time.

■

Note Tests confirm that a SQL Azure database becomes unavailable for more than a minute if you quickly issue
in excess of 50 distinct database connections. After the same test was run multiple times, the database became
unavailable for a few hours.

As you can see, you need to minimize the creation of database connections, which you can achieve
through proper connection pooling. First, you should know that connection pooling is affected if any
part of the connection string is changed (even slightly), such as the application name or the login id
(UID). A new connection pool is created even if you change the order of the parameters of a connection
string. For example, if you have an application that performs three database operations, and the
operations use the following connection strings, respectively, three pools are created, and hence three
distinct database connections in SQL Azure on the same database:
Server=XYZ;Initial Catalog=DB1;UID=hroggero;PWD=123456
Server=XYZ;Initial Catalog=DB1;PWD=123456;UID=hroggero
Server=XYZ;Initial Catalog=DB1;UID=hroggero;PWD=123456;Application Name=MyApp
To ensure that the same database connection is used, you must ensure that the three operations use
the exact same connection string:
Server=XYZ;Initial Catalog=DB1;UID=hroggero;PWD=123456
Server=XYZ;Initial Catalog=DB1;UID=hroggero;PWD=123456
Server=XYZ;Initial Catalog=DB1;UID=hroggero;PWD=123456
To measure the number of database connections open on a given database, you can use the
sys.dm_exec_connections management view. A row is returned for each distinct database connection;
the fewer rows, the better!

Execution Plans
Sometimes you need to dig deeper and understand how SQL Azure fetches data, and then use that
information to improve performance. In SQL Server, you can also use execution plans to observe the
impact of changes to the underlying hardware, such as changing memory configuration. Although you
have no control over configuration settings with SQL Azure, execution plans can still be very useful to
see the impact of your indexes and to view which physical operators are being used.