Database Reference
In-Depth Information
If you're running inside of some hosted environment, you might be sharing a server with a number of other
virtual machines or databases. In some cases, you can work with the vendor or your local administrators to adjust the
settings of these virtual environments to help your SQL Server instance perform better. But, in many circumstance
you'll have little to no control over the behavior of the systems at all. You'll need to work with the individual platform
to determine when you're hitting limits on that platform that could also be causing performance issues.
Poor connectivity between SQL Server and the database application can hurt application performance. One
of the questions you should ask yourself is, how good is the database connection? For example, the query executed
by the application may be highly optimized, but the database connection used to submit this query may add
considerable overhead to the query performance. Ensuring that you have an optimal network configuration with
appropriate bandwidth will be a fundamental part of your system setup. This is especially true if you're hosting your
environments on the cloud.
The design of the database should also be analyzed while troubleshooting performance. This helps you
understand not only the entity-relationship model of the database but also why a query may be written in a certain
way. Although it may not always be possible to modify an in-use database design because of wider implications
on the database application, a good understanding of the database design helps you focus in the right direction
and understand the impact of a resolution. This is especially true of the primary and foreign keys and the clustered
indexes used in the tables.
The application may be slow because of poorly built queries, the queries might not be able to use the indexes,
or perhaps even the indexes themselves are inefficient or missing. If any of the queries are not optimized sufficiently,
they can seriously impact other queries' performance. I cover index optimization in depth in Chapters 8, 9, 11, 12
and 13. The next question at this stage should be, is a query slow because of its resource intensiveness or because of
concurrency issues with other queries? You can find in-depth information on blocking analysis in Chapter 20.
When processes run on a server, even one with multiple processors, at times one process will be waiting on
another to complete. You can get a fundamental understanding of the root cause of slowdowns by identifying what is
waiting and what is causing it to wait. You can realize this through operating system counters that you access through
dynamic management views within SQL Server and through Performance Monitor. I cover this information in
Chapters 2-4 and in Chapter 20.
The challenge is to find out which factor is causing the performance bottleneck. For example, with slow-running
SQL queries and high pressure on the hardware resources, you may find that both poor database design and a
nonoptimized query workload are to blame. In such a case, you must diagnose the symptoms further and correlate
the findings with possible causes. Because performance tuning can be time-consuming and costly, you should ideally
take a preventive approach by designing the system for optimum performance from the outset.
To strengthen the preventive approach, every lesson that you learn during the optimization of poor performance
should be considered an optimization guideline when implementing new database applications. There are also proven
best practices that you should consider while implementing database applications. I present these best practices in detail
throughout the topic, and Chapter 26 is dedicated to outlining many of the optimization best practices.
Please ensure that you take the performance optimization techniques into consideration at the early stages of your
database application development. Doing so will help you roll out your database projects without big surprises later.
Unfortunately, we rarely live up to this ideal and often find database applications needing performance tuning.
Therefore, it is important to understand not only how to improve the performance of a SQL Server-based application
but also how to diagnose the causes of poor performance.
