Database Reference
In-Depth Information
from
TargetData cross apply
TargetData.Data.nodes('/HistogramTarget/Slot') as t(e)
)
select e.dbid, d.name, e.[Count]
from
sys.databases d left outer join EventInfo e on
e.DBID = d.database_id
where
d.database_id > 4
order by
e.Count
Finally, it is worth noting that this approach can result in
false positives
by counting the locks acquired by
various maintenance tasks, such as
CHECKDB
, backups, and other maintenance tasks, as well as by the SQL Server
Management Studio.
Working with the pair_matching Target
The
pair_matching
target maintains information about unmatched events when a
begin
event does not have a
corresponding
end
event, dropping out events from the target when they match. Think of orphaned transactions
where
database_transaction_begin
events do not have corresponding
database_transaction_end
events as an
example. Another case is a query timeout when the
sql_statement_starting
event does not have a corresponding
sql_statement_completed
event.
Let's look at the latter example and create an event session, as shown in Listing 28-23. The
pair_matching
target
requires you to specify matching criteria based on the event data column and/or actions.
Listing 28-23.
Creating an event session with a
pair_matching
target
create event session [Timeouts]
on server
add event
sqlserver.sql_statement_starting
(
action (sqlserver.session_id)
),
add event
sqlserver.sql_statement_completed
(
action (sqlserver.session_id)
)
add target
package0.pair_matching
(
set
begin_event = 'sqlserver.sql_statement_starting'
,begin_matching_columns = 'statement'
,begin_matching_actions = 'sqlserver.session_id'
,end_event = 'sqlserver.sql_statement_completed'
,end_matching_columns = 'statement'
,end_matching_actions = 'sqlserver.session_id'
