Database Reference
In-Depth Information
Chapter 6
Query Performance Metrics
A common cause of slow SQL Server performance is a heavy database application workload—the nature and quantity
of the queries themselves. Thus, to analyze the cause of a system bottleneck, it is important to examine the database
application workload and identify the SQL queries causing the most stress on system resources. To do this, you can
use Extended Events and other Management Studio tools.
In this chapter, I cover the following topics:
•
The basics of Extended Events
•
How to analyze SQL Server workload and identify costly SQL queries using Extended Events
•
How to track query performance through dynamic management objects
Extended Events
Extended Events was introduced in SQL Server 2008, but with no GUI in place and a reasonably complex set of code
to set it up, Extended Events wasn't used much to capture performance metrics. With SQL Server 2012, a GUI for
managing Extended Events was introduced, taking away the final issue preventing Extended Events from becoming
the preferred mechanism for gathering query performance metrics as well as other metrics and measures. SQL
Profiler, previously the best mechanism for gathering these metrics, is in deprecation and will, within a release or two,
be completely removed from the product. Trace events, also good, are still available but on their way out along with
Profiler. As a result, most examples in the topic will be using Extended Events.
Extended Events allows you to do the following:
•
Graphically monitor SQL Server queries
•
Collect query information in the background
•
Analyze performance
•
Diagnose problems such as deadlocks
•
Debug a Transact-SQL (T-SQL) statement
You can also use Extended Events to capture other sorts of activities performed on a SQL Server instance. You
can set up Extended Events from the graphical front end or through direct calls to the procedures. The most efficient
way to define an Extended Events session is through the T-SQL commands, but a good place to start learning about
sessions is through the GUI.
