Database Reference
In-Depth Information
One small note on the Extended Events data: if it's going to be collected to a file, you'll then need to load the data
into a table or just query it directly. You can read directly from the Extended Events fileby querying it using this system
function:
SELECT *
FROM sys.fn_xe_file_target_read_file('C:\Sessions\QueryPerformanceMetrics*.xel',
NULL, NULL, NULL);
The query returns each event as a single row. The data about the event is stored in an XML column,
event_data
.
You'll need to use XQuery to read the data directly, but once you do, you can search, sort, and aggregate the data
captured. I'll walk you through a full example of this mechanism in the next section.
Identifying Costly Queries
The goal of SQL Server is to return result sets to the user in the shortest time. To do this, SQL Server has a built-in,
cost-based optimizer called the
query optimizer,
which generates a cost-effective strategy called a
query execution
plan.
The query optimizer weighs many factors, including (but not limited to) the usage of CPU, memory, and disk
I/O required to execute a query, all derived from the various sources such as statistics about the data maintained
by indexes or generated on the fly, constraints on the data, and some knowledge of the system the queries are
running such as the number of CPUs and the amount of memory. From all that the optimizer creates a cost-effective
execution plan.
In the data returned from a session, the
cpu_time
and
logical_reads
or
physical_reads
fields also show
where a query costs you. The
cpu_time
field represents the CPU time used to execute the query. The two
reads
fields
represent the number of pages (8KB in size) a query operated on and thereby indicates the amount of memory or I/O
stress caused by the query. It also indicates disk stress since memory pages have to be backed up in the case of action
queries, populated during first-time data access, and displaced to disk during memory bottlenecks. The higher the
number of logical reads for a query, the higher the possible stress on the disk could be. An excessive number of logical
pages also increases load on the CPU in managing those pages. This is not an automatic correlation. You can't always
count on the query with the highest number of reads being the poorest performer. But it is a general metric and a good
starting point. Although minimizing the number of I/Os is not a requirement for a cost-effective plan, you will often
find that the least costly plan generally has the fewest I/Os because I/O operations are expensive.
The queries that cause a large number of logical reads usually acquire locks on a correspondingly large set of
data. Even reading (as opposed to writing) may require shared locks on all the data, depending on the isolation level.
These queries block all other queries requesting this data (or part of the data) for the purposes of modifying it, not for
reading it. Since these queries are inherently costly and require a long time to execute, they block other queries for an
extended period of time. The blocked queries then cause blocks on further queries, introducing a chain of blocking in
the database. (Chapter 13 covers lock modes.)
As a result, it makes sense to identify the costly queries and optimize them first, thereby doing the following:
•
Improving the performance of the costly queries themselves
•
Reducing the overall stress on system resources
•
Reducing database blocking
The costly queries can be categorized into the following two types:
•
Single execution
: An individual execution of the query is costly.
•
Multiple executions
: A query itself may not be costly, but the repeated execution of the query
causes pressure on the system resources.
You can identify these two types of costly queries using different approaches, as explained in the following sections.
