Database Reference
In-Depth Information
An
event
represents various activities performed in SQL Server and, in some cases, the underlying operating
system. There's an entire architecture around event targets, event packages, and event sessions, but the use of the
GUI means you don't have to worry about all those details. I will cover some of the architecture when showing how to
script a session later in this chapter.
For performance analysis, you are mainly interested in the events that help you judge levels of resource stress for
various activities performed on SQL Server. By
resource stress,
I mean things such as the following:
•
What kind of CPU utilization was involved for the SQL activity?
•
How much memory was used?
•
How much I/O was involved?
•
How long did the SQL activity take to execute?
•
How frequently was a particular query executed?
•
What kind of errors and warnings were faced by the queries?
You can calculate the resource stress of a SQL activity after the completion of an event, so the main events
you use for performance analysis are those that represent the completion of a SQL activity. Table
6-1
describes
these events.
Table 6-1.
Events to Monitor Query Completion
Event Category
Event
Description
Execution
rpc_completed
A remote procedure call completion event
sp_statement_completed
A SQL statement completion event within a stored procedure
sql_batch_completed
A T-SQL batch completion event
sql_statement_completed
A T-SQL statement completion event
An RPC event indicates that the stored procedure was executed using the Remote Procedure Call (RPC)
mechanism through an OLEDB command. If a database application executes a stored procedure using the T-SQL
EXECUTE
statement, then that stored procedure is resolved as a SQL batch rather than as an RPC.
A
T-SQL batch
is a set of SQL queries that are submitted together to SQL Server. A T-SQL batch is usually
terminated by a
GO
command. The
GO
command is not a T-SQL statement. Instead, the
GO
command is recognized by
the
sqlcmd
utility, as well as by Management Studio, and it signals the end of a batch. Each SQL query in the batch is
considered a T-SQL statement. Thus, a T-SQL batch consists of one or more T-SQL statements. Statements or T-SQL
statements are also the individual, discrete commands within a stored procedure. Capturing individual statements
with the sp_statement_completed or sql_statement_completed event can be a more expensive operation, depending
on the number of individual statements within your queries. Assume for a moment that each stored procedure within
your system contains one, and only one, T-SQL statement. In this case, the cost of collecting completed statements is
very low. Now assume you have multiple statements within your procedures and that some of those procedures are
calls to other procedures with other statements. Collecting all this extra data now becomes a more noticeable load
on the system. My own testing suggested that you won't see much impact until you're hitting upward of ten distinct
statements per procedure. Statement completion events should be collected judiciously, especially on a production
system. You should apply filters to limit the returns from these events. Filters are covered later in this chapter.
To add an event to the session, find the event in the Event library. This is simple; you just type the name. In
Figure
6-2
you can see
rpc_co
typed into the search box and that part of the event name highlighted. Once you have
an event, use the arrow buttons to move the event from the library to the Selected Events list. To remove events not
required, click the arrow to move it back out of the list and into the library.
