Chapter 4. Improving poor query performance
This chapter covers
- Query execution
- Identifying different aspects of poorly performing queries
- How to improve poorly performing queries
Slow-performing queries can have a costly impact on systems. When queries run slowly, transactions and locks are held longer, leading to an increased possibility of blocking, which can cause other queries in turn to run more slowly. All of this can lead to more client application timeouts.
Identifying the slowest queries will allow you to make targeted improvements, confident in the knowledge that any improvement to these queries will have the biggest overall impact on performance.
As well as looking at slow-running queries, we’ll look at other aspects of queries that could be indicative of an underlying problem or could be used to target improvements. Various aspects of queries that make use of the CLR are discussed separately in chapter 7, “Common Language Runtime DMVs.”
So that you’ll get the most from the scripts described in this chapter, first I’ll explain how and why SQL queries are cached.