Extended Events: Histogram Output

The single most important thing to remember about Extended Events is that this functionality is not simply a replacement for Profiler/Trace, but a whole new tool with new functionality. My first example for functionality that you simply cannot get in Profiler/Trace is the ability to output to a Histogram.

Profiler/Trace can output to a table or to a file. Extended Events can have a target that is a file, same as Profiler. However, you can also have a target:

• etw_classic_sync_target
• event_counter
• histogram
• pair_matching
• ring_buffer

Read about each of the types in the Microsoft documentation here.

I’m going to focus for the moment on the histogram target because it lets you do some fun stuff and easily collect data that you simply can’t collect using Profiler/Trace without hopping through a bunch of flaming hoops.

Setting Up The Test

The histogram target behaves similarly to the event_counter target. The event_counter target counts the number of times that an event occurs. However, the histogram target lets you pick a grouping mechanism for the histogram. You can use either an action, or an event field.

For demonstration purposes, what I want to know is, per object in the database, how many times are the statistics automatically updated? To do this, I have a simple Extended Events session:

CREATE EVENT SESSION StatsUpdateGraph
ON SERVER
    ADD EVENT sqlserver.auto_stats
    (WHERE (sqlserver.database_name = N'AdventureWorks'))
    ADD TARGET package0.histogram
    (SET filtering_event_name = N'sqlserver.auto_stats', source = N'object_id', source_type = (0));
GO

I’m capturing all the auto_stats events for one database, AdventureWorks. I’m filtering the histogram by the one event I’m capturing, auto_stats and grouping by the event field, object_id. We can start the session with the following code:

ALTER EVENT SESSION StatsUpdateGraph ON SERVER STATE = START;
GO

Now, let’s create a procedure and do a blunt force statistics update:

USE AdventureWorks;
GO

CREATE OR ALTER PROCEDURE dbo.AddressIdByCity @City NVARCHAR(30)
AS
SELECT a.AddressID
FROM Person.Address AS a
WHERE City = @City;
GO

EXEC dbo.AddressIdByCity @City = N'Mentor' -- nvarchar(30)
GO

BEGIN TRAN

UPDATE Person.Address
SET City = 'Fornebu';

EXEC dbo.AddressIdByCity @City = N'Fornebu' -- nvarchar(30)

ROLLBACK TRAN
GO

Even though the output for the session is going to a histogram, we can actually watch the Live Data window for the session. The output from the above script looks like this:

• 

What we’re seeing are the statistics being loaded for generating an execution plan. Then, because of the data change, one set of statistics gets updated and other statistics get loaded to recompile the plan.

Nothing shocking at all. What about this histogram thing?

Histogram

Unseen, while the events are firing and you can observe them by monitoring the session, the session itself is keeping the count of executions in memory. In my case, the definition of the histogram is that I’m counting auto_stats events and grouping them by a selected event field, the object_id.

Because the histogram is in memory, you have a couple of ways to looking at it. If you use SSMS, you can look at the session in the Object Explorer:

You can double click this to open it up within SSMS and you get the following:

I’ve run the test a few times, but you can see that I’ve got a count and a value which is the object_id. If I was to cause another auto update statistics event to fire and looked at the histogram again, it might look like this:

You can also see that the values can be refreshed or, you can set up an automated refresh interval. Check out the menu choices:

• 

As you can see, you can set an interval so that your histogram is updating automatically. Once you pick a refresh rate, if you want you can pause and resume it. You can also refresh manually, or you can even export it.

Kendra Little has a great blog post on how to query the histogram. I’ve borrowed and modified a little bit of her code to show how to pull together different information:

WITH xedata
AS (SELECT CAST(dxst.target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets AS dxst
        JOIN sys.dm_xe_sessions AS dxs
            ON dxs.address = dxst.event_session_address
    WHERE dxs.name = 'StatsUpdateGraph'
          AND dxst.target_name = 'histogram'),

     gramdata
AS (SELECT xed.slot_data.value('(value)[1]', 'varchar(256)') AS objectid,
           xed.slot_data.value('(@count)[1]', 'varchar(256)') AS slotcount
    FROM xedata AS x
        CROSS APPLY x.target_data.nodes('//HistogramTarget/Slot') AS xed(slot_data) )

SELECT o.name,
       gd.slotcount
FROM gramdata AS gd
    JOIN sys.objects AS o
        ON o.object_id = gd.objectid;

Just know the added complexity here is just me trying to tie the object name to the output from the histogram. If you look at Kendra’s query, you can keep these pretty simple, despite the fact that you’re querying XML data.

You Can’t Do That In Profiler

As you can see, Extended Events are not simply about replacing Profiler so you can capture query metrics. We are talking about all new functionality that changes what we can capture and how we can capture it.

By using the histogram, I’m able to pull completely new information with very little labor. Simply applying the histogram target and picking the right grouping mechanisms, I can get counts for events in new and expanding ways. Think about it, deadlocks by object, plan guide use when you have more than one plan guide, all these types of things can be gathered now in ways we simply didn’t have before, or, they would be extremely process intensive to grab.

---

I hope this information was helpful. I’ll be posting lots more, but, if you prefer to learn other ways, I also can offer you in-person classes on this topic and others.

I’ll be teaching Extended Events as well as Query Store and Execution Plans and a little bit more at SQLIntersection this spring:

SQLIntersection: April 7-9

On the other hand, if you want to talk DevOps, I have an all day class at Bits:

SQLBits: April 1