For me, some of the most fun stuff with SQL Server is diving deep into the internals. Diving into the internals is not always an easy adventure. Sometimes, it requires a lot of trial and error sometimes. There are times where a dive into the internals can get me “into the weeds”, “down a rabbit hole” on a long windy path with no end in sight. Granted the end usually comes, but you get the idea.

That said, today I am not taking you on one of those journeys. The premise of the article today is to get a little exposure to some of the internals that may cause a bit of curiosity for you. I will share a means to expose some of the settings/stages/steps of various internal processes. These are the sorts of things you may have heard about, but without Extended Events, you might have to dig harder and further to find them.

Exposure to Internals

There are numerous events within the Extended Event engine that expose internals and internal operations. I will not be exploring the internals via actual events. That would be too easy! I want to share the internals available to you not via events, but rather other objects from the engine. Today, all of the internals will be exposed via the “custom” data types. More in-line with the official terminology is that these will be exposed via “Maps”.

The beauty of the maps is that these are free-floating maps. None of them are currently tied to an event. So trying to find an event that will help track these internals is just not going to happen. /* Disclaimer: This is only relevant to current versions. This is something that could change in future versions. */

Knowing those pieces of information, the dive into the internals becomes far easier now. I know (based on prior articles in the series) that maps are exposed via sys.dm_xe_map_values. Since they are not tied to events, I also know that there are currently no fields using the maps within sys.dm_xe_object_columns. Now I want to look at all the possible internals that are tied to maps. This can be done via the following query:

SELECT DISTINCT xmv.name
FROM sys.dm_xe_map_values xmv
LEFT OUTER JOIN sys.dm_xe_object_columns xoc
ON xmv.name = xoc.type_name
AND xmv.object_package_guid = xoc.object_package_guid
WHERE xoc.name IS NULL
ORDER BY xmv.name;

This will produce the following sample of results:

That is just a sample of the internals maps available through the XEvent engine. That is pretty cool. Looking closer at the results, I have highlighted (in green) some really interesting and cool maps. Wow! I can look at the different components of the query_optimizer_tree_id. That seems like a worthwhile look! Let’s look closer at query_optimizer_tree_id now.

SELECT xmv.name, xmv.map_key, xmv.map_value, xo.description, xo.type_name
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('\',REVERSE(olm.name))-1)) AS DLLName
,xp.name AS PackageName
,xo.object_type
FROM sys.dm_xe_map_values xmv
LEFT OUTER JOIN sys.dm_xe_object_columns xoc
ON xmv.name = xoc.type_name
AND xmv.object_package_guid = xoc.object_package_guid
INNER JOIN sys.dm_xe_objects xo
ON xmv.object_package_guid = xo.package_guid
AND xmv.name = xo.name
INNER JOIN sys.dm_xe_packages xp
ON xp.guid = xo.package_guid
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address
WHERE xoc.name IS NULL
AND xmv.name = 'query_optimizer_tree_id'
ORDER BY xmv.name, xmv.map_key;

I added a few more pieces of information to this query so I can get a bigger picture of the query_optimizer_tree_id map. I want to know the over-arching map name, the package name, the source dll, and I want to know what this map does. The results are as follows:

What a wealth of information into the internal workings of the query optimizer! I can see that the sqllang.dll is the source for the optimizer tree. I can see that this map reveals the different stages during optimization. And I can see that the Package is the sqlserver package in XEvents. This is fantastic! Of course, I may be a little geeky about it, but it is a cool way to get to know some of the internal operations of SQL Server.

What if I want to explore a different map? Well, I can pick one from the first query and then dive in and look just the same way I did for the query_optimizer_tree_id. In fact, let’s repeat that process with a different map, and with a slightly enhanced discovery query:

/* database_state */DECLARE @map VARCHAR(128) = 'database_state' --'database_state' --null for all
SELECT xmv.name, xmv.map_key, xmv.map_value, xo.description, xo.type_name
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('\',REVERSE(olm.name))-1)) AS DLLName
,xp.name AS PackageName
FROM sys.dm_xe_map_values xmv
LEFT OUTER JOIN sys.dm_xe_object_columns xoc
ON xmv.name = xoc.type_name
AND xmv.object_package_guid = xoc.object_package_guid
INNER JOIN sys.dm_xe_objects xo
ON xmv.object_package_guid = xo.package_guid
AND xmv.name = xo.name
INNER JOIN sys.dm_xe_packages xp
ON xp.guid = xo.package_guid
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address
WHERE xoc.name IS NULL
AND xmv.name = ISNULL(@map,xmv.name)
ORDER BY xmv.name, xmv.map_key;

And the results:

Nothing really earth shattering there. It is just the database state after-all and it should be rather familiar to the data professional.

Some of the internals maps will definitely be far more interesting than others. And the interest level will definitely depend on the person browsing the data. For me, learning more about the optimizer and internals in general is a lot of fun. Consequently, I am drawn more to the maps that can give more information about the internal workings of the optimization tree or even the different operators (logical and physical) within a query plan.

This article demonstrated a quick means to explore various stages of SQL Server internal operations. Accessing this kind of information is just scratching the surface for internals and really just a scratch on the surface of Extended Events. There is a lot of power with the little bit of information that XEvents yields. Explore and enjoy!

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.