What Sessions are Deployed?

As a DBA, it is not uncommon to feel like you are marooned off on some tiny little island trying to figure things out for yourself. It may even be very common to feel the icy pain of the development cold shoulder (intentional or not) as you go about doing your duties to protect the data and secure high octane performance out of the database.

Not only may it be very commonplace to experience this due to the nature of the job, but it can also get to feel that way when a new product is released. Suddenly, that icy island is further north into the Arctic Ocean and you are struggling to gasp for a bit of air in the frigid cold.

I am certain this may be some of what you feel as you try to take a look at Extended Events. It is a fancy, new technology and there is not a ton of time to get up to speed while trying to warm yourself against the development chill. As you stew about the situation, you begin to ponder, “How am I even supposed to know what an XE is, let alone what XEs are running on my server?” Never mind the confusion on what an XE is and that it is actually running or deployed sessions on the server. Don’t worry about the terminology just yet. What can we do to figure out what might be out there?

Well, that is where the trusty Google has come to help you. Thanks to a better than adequate ability to search the Internet, you landed on this page that has a script to help you explore what might be running as far as Extended Events on your server. You may be surprised to learn that you have more Extended Events Sessions running than you fathomed. Yes, SQL Server comes with some default sessions. Yes, some of these default sessions are more public than others. And yes, some of these

Come on in out of the cold. Get off that arctic island and warm up with a little XE fun. This script is quite simple but will show you what you may have out there on your servers. Once you see what is there, hopefully your curiosity will get a little piqued and you will want to learn a little more (there is more of that here on this site too).

And now, for that script:

Source code

DECLARE @SessionName VARCHAR(128) = NULL --'sp_server_diagnostics session' --NULL for all
;
 
SELECT ISNULL(ses.name,xse.name) AS SessionName
		, CASE
			WHEN ISNULL(ses.name,'') = ''
			THEN 'Private'
			ELSE 'Public'
			END AS SessionVisibility
		, CASE
			WHEN ISNULL(xse.name,'') = ''
			THEN 'NO'
			ELSE 'YES'
			END AS SessionRunning
		, CASE
			WHEN ISNULL(xse.name,'') = ''
				AND ISNULL(ses.name,'') = ''
			THEN 'NO'
			ELSE 'YES'
			END AS IsDeployed
	FROM sys.server_event_sessions ses
	FULL OUTER JOIN sys.dm_xe_sessions xse
		ON xse.name =ses.name
	WHERE COALESCE(@SessionName, ses.name, xse.name) = ISNULL(ses.name, xse.name)
	ORDER BY ses.event_session_id;
 
GO

Quick and simple, and full of that warming XE sensation. This script just shows you what is a private session versus public. What is a private session, you ask? Well, that happens to be all of those database audits you have deployed to your server. It also includes those little things like the “sp_server_diagnostics session” session (yes it sounds redundant but that is because session is also part of the name for that session).

This script will also show you what is deployed to the server versus what is running on the server (not all sessions have to be running). And to top it all off, I set it up so you can query for a specific session or for all sessions.

Try it out on one or more of your servers and let me know how it goes.