Database Reference
In-Depth Information
Capturing the Workload
As a part of the diagnostic-data collection step, you must define an Extended Events session to capture the workload
on the database server. You can use the tools and methods recommended in Chapter 6 to do this. Table
25-1
lists the
specific events you should use to measure how resource intensive the queries are.
Table 25-1.
Events to Capture Information About Costly Queries
Category
Event
rpc_completed
sql_batch_completed
Execution
As explained in Chapter 6, for production databases it is recommended that you capture the output of the
Extended Events session to a file. Here are a couple significant advantages to capturing output to a file:
•
Since you intend to analyze the SQL queries once the workload is captured, you do not need to
display the SQL queries while capturing them.
•
Running the session through SSMS doesn't provide a flexible timing control over the tracing
process.
Let's look at the timing control more closely. Assume you want to start capturing events at 11 p.m. and record the
SQL workload for 24 hours. You can define an Extended Events session using the GUI or T-SQL. However, you don't
have to start the process until you're ready. This means you can create commands in SQL Agent or with some other
scheduling tool to start and stop the process with the
ALTER EVENT SESSION
command.
ALTER EVENT SESSION <sessionname>
ON SERVER
STATE = <start/stop>;
For this example, I've put a filter on the session to capture events only from the
AdventureWorks2012
database.
The file will capture queries against only that database, reducing the amount of information I need to deal with. This
may be a good choice for your systems, too. While extended events can be very low cost, especially when compared to
the older trace events, they are not free. Good filtering should always be applied to ensure minimum impact.
Analyzing the Workload
Once the workload is captured in a file, you can analyze the workload either by browsing through the data using SSMS
or by importing the content of the output file into a database table.
SSMS provides the following two methods for analyzing the content of the file, both of which are relatively
straightforward:
•
Sort the output on a data column by right-clicking to select a sort order or to group by a
particular column
: You may want to select columns from the Details tab and use the “Show
column in table” command to move them up. Once there, you can issue grouping and sorting
commands on that column.
•
Rearrange the output to a selective list of columns and events
: You can change the output
displayed through SSMS by right-clicking the table and selecting Pick Columns from the
context menu. This lets you do more than simply pick and choose columns; it also lets you
combine them into new columns.
