So I have blasted through a lot of technical articles about Extended Events. There is a lot of groundwork that has been covered with layers of building blocks that have been stacked. Now it’s time to take a step back as I near the end of the original goal for this series.

I say original goal, but that really only means the goal pertaining to the length of the series. I had a goal for content that has yet to be reached. Due to that, I have pushed the series into overtime. So, really, that means this is merely a step back to a lighter topic today as I jump into half-time (or something like that) of the series.

Default Sessions

In the previous article I mentioned some of the default sessions for Azure SQL Database. I will not be going into any further detail about those sessions at this time. Discussion on those default sessions will have to be reserved for a later date.

The focus of this article will be to introduce the default sessions deployed with SQL Server. Some of these are internal and “private” while others are more “public” and like a user defined session.

system_health

The system_health default session is the most common of all of the default sessions. This session is akin to a true black box recorder in SQL Server. There is a ton of information that can be trapped via this event session. One caveat to this session is that if you are looking at the session in SQL Server 2008/R2 then the session really should be modified to include a file target.

sp_server_diagnostics session

The name of this session almost gives it away. This is an internal private session that executes a stored procedure by the same name: sp_server_diagnostics. Since it is a private internal session, I do not have all of the details on the internals. But since it is related to the sp_server_diagnostics stored procedure, it isn’t hard to make the leap at what it does. In addition, there are some events with a similar name which helps to better understand what is happening here.

The sp_server_diagnostics stored procedure captures diagnostic and health information about SQL Server to help alert to potential failures. This procedure runs continuously in repeat mode. Here are some of the results from my laptop for this procedure:

Notice there are five different domains of interest in this result set. If I look at the events by the same namesake, I will see the following possible data points:

Wait, there’s more! Diving in deeper into the event, I can see the following possible component_names / domains for the maps in this event:

In this last result set, there are multiple data points that map back directly to the stored procedure. In addition to the direct mapping, this result demonstrates the additional domains of “AGS” and “LAST”. I don’t have any AGS created on this machine so no stats would be recorded for that. I don’t know what “LAST” represents. Then the sp_server_diagnostics_state maps directly to the “state” seen in the stored procedure.

Having this same information divulged via extended events doesn’t end here. The system_health session collects the sp_server_diagnostics_component_result event by default. This all ties together back to the “sp_server_diagnostics session” which appears to be the manifestation of the “repeat mode” of the sp_server_diagnostics extended stored procedure. The proc is continuously running and gathering data for the event to trap the information and subsequently send it along to the event session targets.

hkenginexesession

The internals of this session are the hardest to track down. This is an internal private session. The name of the session does reveal a fair amount of information about the session that can be used to make decent educated guesses at what is being done here. First this session is used for monitoring In-memory OLTP / hekaton / hk (for short) tables and natively compiled stored procedures.

Taking the knowledge that this is used to monitor the “hekaton” engine, one can also deduce that the possible list of events comes from the following (probably others involved too):

A more complete list of events that seem to fit for this session can be retrieved via the following query:

SELECT name AS EventName
, description AS EventDescription
FROM sys.dm_xe_objects
WHERE object_type = 'event'
AND ( name LIKE '%xtp%'
OR name LIKE '%hekat%'
OR description LIKE '%hk%'
OR description LIKE '%hekaton%'
OR description LIKE '%native%'
OR description LIKE '%memory%oltp%'
)
ORDER BY name;

The final piece of interesting information is that this session appears to be tied to the following dll: hkengine.dll. This is a dll that is loaded in SQL Server and it does bear the same naming convention. Here is another query that will show if this dll is loaded:

SELECT REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('\',REVERSE(olm.name))-1)) AS DLLName
FROM sys.dm_os_loaded_modules olm
WHERE olm.name LIKE '%hke%';

This session is only available as of SQL Server 2014.

AlwaysOn_health

The AlwaysOn_health session is very much like the system_health default session. This is a default session that can be scripted from within SQL Server Management Studio. Think of this session as a black box recorder for your Availability Groups. This session will capture events related to the state of the AG replica, if there is a state change, any errors that occur, and failovers that occur.

To get a grasp of what this particular session does (try and figure out a query to accomplish this before proceeding), one can run the following query:

SELECT sese.name AS EventName, xo.description AS EventDescription
FROM sys.server_event_session_events sese
INNER JOIN sys.server_event_sessions ses
ON sese.event_session_id = ses.event_session_id
INNER JOIN sys.dm_xe_objects xo
ON sese.name = xo.name
WHERE ses.name = 'AlwaysOn_health'
AND xo.object_type = 'event';

And the results would be:

Bonus points if you recognized this could be done based on the foundations pieces laid out in previous articles.

This session became available as of SQL Server 2012.

The default sessions in SQL Server cover a lot of points of interest when looking to monitor a database environment. Between these sessions, this is quite a significant black box recorder for SQL Server. Despite the quantity of events that these sessions combine to monitor as your black box recorder, it doesn’t mean that you can’t add more events to monitor. That is the power of Extended Events. It is also important to remember that these sessions are default sessions. What do we like to do with most defaults? That’s right, we tweak it to be more specific to our own environments.

I hope you enjoyed the article, even without the dive into the default sessions of Azure SQL Database. Stay tuned because those sessions may be covered in some future article.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.