Databases Reference
In-Depth Information
•
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 quickly
reusing a database connection from the pool when you have a large number of connections
being made. Indeed, connections are released in the pool when they are closed. If you do not
release your connections quickly enough, you could create a condition in which connection
requests are queued until connections become available in the pool, or connection requests
time out if the wait is too long.
•
Shared database account.
Because SQL Database 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 Database. For more information, read the following chapter from Microsoft's Patterns and Practices:
Summary
This chapter provided an overview of some of the most important tuning techniques that are available to help you
address SQL Database performance issues. As you've seen, troubleshooting and tuning statements can be complex
and require various tools and methods to obtain the desired outcome. You saw a few dynamic management views and
execution plans, took a quick tour of indexing, and briefly touched on the statistics provided by the ADO.NET library.
You also learned about some design considerations that can affect application performance.
You can discover additional techniques and concepts on Microsoft's web site at
