Database Reference
In-Depth Information
Chapter 7
Analyzing Query Performance
The previous chapter showed how to gather query performance metrics. This chapter will show how to consume
those metrics to identify long-running or frequently called queries. Then I'll go over the tools built right into
Management Studio so you can understand how a given query is performing. I'll also spend a lot of time talking
about using execution plans, which are your best view into the decisions made by the query optimizer.
In this chapter, I cover the following topics:
•
How to analyze the processing strategy of a costly SQL query using Management Studio
•
How to analyze methods used by the query optimizer for a SQL query
•
How to measure the cost of a SQL query using SQL utilities
Costly Queries
Now that you have seen two different ways of collecting query performance metrics, let's look at what the data
represents: the costly queries themselves. When the performance of SQL Server goes bad, a few things are most likely
happening.
•
First, certain queries create high stress on system resources. These queries affect the
performance of the overall system because the server becomes incapable of serving other
SQL queries fast enough.
•
Additionally, the costly queries block all other queries requesting the same database
resources, further degrading the performance of those queries. Optimizing the costly queries
improves not only their own performance but also the performance of other queries by
reducing database blocking and pressure on SQL Server resources.
•
Finally, a query that by itself is not terribly costly could be called thousands of times a minute,
which, by the simple accumulation of less than optimal code, can lead to major resource
bottlenecks.
To begin to determine which queries you need to spend time working with, you're going to use the resources
that I've talked about so far. For example, assuming the queries are in cache, you will be able to use the DMOs to pull
together meaningful data to determine the most costly queries. Alternatively, because you've captured the queries
using Extended Events, you can access that data as a means to identify the costliest queries.
