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:

histogram-data-gui-ssms

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:

tsql-xevents-histogram-target

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.

 

 

Previous Post
Administering COTS databases (ISVs / Third Party Vendors)
Next Post
Bug: Incorrect modification_counter for Column Stats on Tables with a Clustered Columnstore Index

Related Posts

No results found

5 Comments. Leave new

  • Gordon Feeney
    January 17, 2017 5:05 am

    Hi Kendra, why did it take histogram data FOREVER (well, several minutes anyway) to appear when testing this?

    Gordon.

    Reply
    • Were you using the sample Extended Events script I provided for sqlserver.file_read for the tempdb database and a specific session id?

      If so, it was just that long before SQL Server accessed a physical data file in tempdb for your session. (It’s pretty darn good at using pages that are already in memory. To test which files queries were using I had to keep dumping the memory cache on my test instance.)

      Reply
  • Great blog post. Kendra, do you know what the max bucket count is in SQL 2014 for histogram target?

    thanks

    Reply
    • I think it’d be easy to find out if you have a test 2014 instance available. I don’t have one handy, but on SQL 2016 I just tried to create one with a lot of buckets (slots), and got this message telling me that the max on 2016 is 16,384. To get the message details, you have to run TSQL to try to create the session, the error details didn’t bubble up into the GUI when I tried it there.

      Msg 25641, Level 16, State 0, Line 1
      For target, “package0.histogram”, the parameter “slots” passed is invalid. The slot count, 111269,
      exceeds the maximum slot count size of 16384. Reduce the slot count value and try your command again.

      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu