How to Query Histogram Target XML in Extended Events

When I was recently testing tempdb file usage, I used an Extended Events session that used SQL Server’s histogram target to track sqlserver.file_read events in the tempdb database for a specific session.

I like using the histogram target because it’s relatively lightweight – you can “bucket” results by what you’re interested in. In my case, I was interested seeing the cumulative number of file_read events by file name.

But there’s one problem: the histogram target is stored in memory, not in a data file. If you want to query that data and store it off in a table, it’s not obvious how to do that.

My XEvents Session and a test query

Here’s the TSQL for the extended events session that I used for my research:

CREATE EVENT SESSION [tempdb-test] ON SERVER 
ADD EVENT sqlserver.file_read(SET collect_path=(1)
    ACTION(sqlserver.database_id,sqlserver.session_id)
    WHERE 
        ([package0].[equal_uint64]([database_id],(2)) 
        AND [sqlserver].[session_id]=(53)))
ADD TARGET package0.histogram
    (SET filtering_event_name=N'sqlserver.file_read',source=N'path',source_type=(0))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

(You’re probably using a totally different event, and that’s fine – I’m just including the definition of the trace I used as an example.)

Let’s say that I want to see which tempdb files have file_read events for the following query, which I run in a session with the SPID 53:

USE WideWorldImporters;
GO
SELECT *
INTO #test
FROM Sales.OrderLines;
GO

What the data looks like in the SSMS Gui

I can access the results easily in the GUI. I just have to click on the histogram target under the Extended Events session:

![histogram-target-extended-events]

This opens a tab in SSMS, which shows that my query mostly used one tempdb file… but there was one file_read event for another file:

Please don’t tell anyone that I put my tempdb files on my system drive on this test instance, it’s my dirty little secret.

The GUI is nice, but If I’m running a series of tests, it’s much easier to query this data with TSQL and record it to a table.

Querying the Histogram Target with TSQL

The data for this is stored in XML. You could store the results as XML, but personally, I’d rather go ahead and shred it so I can read the results in a table.

Here’s the query that I used to get this data via TSQL:

SELECT 
    xed.slot_data.value('(value)[1]', 'varchar(256)') AS filename,
    xed.slot_data.value('(@count)[1]', 'varchar(256)') AS slotcount
FROM (
    SELECT 
        CAST(xet.target_data AS xml)  as target_data
    FROM sys.dm_xe_session_targets AS xet  
    JOIN sys.dm_xe_sessions AS xe  
       ON (xe.address = xet.event_session_address)  
    WHERE xe.name = 'tempdb-test' 
        and target_name='histogram'
    ) as t
CROSS APPLY t.target_data.nodes('//HistogramTarget/Slot') AS xed (slot_data);
GO

And the results look like this:

Voila, the same data we saw in the GUI!

Want to learn more about the Histogram Target?

Check out this blog post by Jason Brimhall, where he gives an example of using the histogram target to see which databases are experiencing deadlocks. He uses slightly different queries to pull from the histogram target, so you can pick the queries you like the best.