Extended Events - Pro SQL Server Internals - page 561

Working with histogram, synchronous_bucketizer, and
asynchronous_bucketizer Targets
Histogram or bucketizer targets group occurrences of specific event types based on event data. Let's consider the
scenario where you have a SQL Server instance with a large number of databases, and you want to find out what
databases are not in use. You can analyze the index usage statistics, however that method is not bullet-proof and it can
provide incorrect results for rarely used databases if the statistics were unloaded due to a SQL Server restart, index
rebuild, or for other reasons.
Extended Events can help you in this scenario. There are two simple ways to achieve the goal. You can analyze
the activity against different databases by capturing the sql_statement_starting and rpc_starting events.
Alternatively, you can look at database-level shared (S) locks, which are acquired by any sessions accessing a
database. With either approach, histogram or bucketizer targets allow you to count occurrences of events, grouping
them by database_id.
Let's look at the second approach and implement an event session that tracks database-level locks. As a first step,
let's analyze the data columns of the lock_acquired event with the query shown in Listing 28-19. Figure 28-17 shows
results of the query.
Listing 28-19. Examining lock_acquired event data columns
select column_id, name, type_name
from sys.dm_xe_object_columns
where column_type = 'data' and object_name = 'lock_acquired'
Figure 28-17. Lock_acquired event data columns
As you can see, the resource_type and owner_type columns data types are maps. You can examine all possible
values with the queries shown in Listing 28-20. Figure 28-18 shows results of the queries.