Database Reference
In-Depth Information
(
set
slots = 32 -- Based on # of DB
,filtering_event_name = 'sqlserver.lock_acquired'
,source_type = 0 -- event data column
,source = 'database_id' -- grouping column
)
with
(
event_retention_mode=allow_single_event_loss
,max_dispatch_latency=30 seconds
);
Histogram
and/or
bucketizer
targets have four different parameters:
slots
indicate the maximum number of different values (groups) to retain. SQL Server
ignores all new values (groups) as soon as that number is reached. You should be careful
and always reserve enough slots to keep information for all groups that can be present in
the data. In our example, you should have a slot value that exceeds the number of databases
in the instance. SQL Server rounds the provided value to the next power of two in order to
improve performance.
source
contains the name of the event column or action, which provides data for grouping.
source_type
is the type of the object by which you group, and it can be either 0 or 1, which
indicates a grouping by event data column, or action, respectively. The default value is 1,
which is action.
filtering_event_name
is the optional value that specifies the event from an event session,
which you are using as the data source for grouping. It should be specified if you group by
event data column, and it could be omitted when grouping by action. In the latter case,
grouping can be done based on the actions from multiple events.
You can access
histogram
or
bucketizers'
event data through the
event_data
column in the
sys.dm_xe_session_targets
view. Listing 28-22 shows the code that analyzes the results of the
DBUsage
event session.
Listing 28-22.
Examining histogram data
;with TargetData(Data)
as
(
select convert(xml,st.target_data) as Data
from sys.dm_xe_sessions s join sys.dm_xe_session_targets st on
s.address = st.event_session_address
where s.name = 'DBUsage' and st.target_name = 'histogram'
)
,EventInfo([Count],[DBID])
as
(
select
t.e.value('@count','int')
,t.e.value('((./value)/text())[1]','smallint')
