Today, I continue on with exploring some of the metadata concerning Extended Events. To date, I have explored various aspects of session metadata whether the session is running or stopped. Today, I want to take a step back and look at the framework that helps give us this tool.

The first core building block to the data that builds Extended Events is the concept around packages.

When I think of package, I like to think of a container that gives me access to one or more smaller items. It is just like a parcel that may be shipped to you via your favorite courier. Only this is obviously something electronic and could potentially contain much much more than any traditional parcel would contain.

Microsoft exposes the packages that contain all of the components of extended events via the DMV sys.dm_xe_packages. Combine this DMV with another DMV not specific to Extended Events and we can see which DLL on the file-system exposes that particular package and grants us access to various components of Extended events. That second DMV is sys.dm_os_loaded_modules.

Packages

Knowing the source of the Extended Event object is useful in some cases. Being able to pull out the pertinent information isn’t terribly difficult either. I can run a query like the following and get just about everything I might want to know about the package (well maybe not if I want to know the memory address as well, but that is an easy fix).

SELECT xp.name AS PackageName
,xp.description AS PackageDesc
,xp.capabilities_desc
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('\',REVERSE(olm.name))-1)) AS DLLName
,olm.file_version
,xp.module_guid
FROM sys.dm_xe_packages xp
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address;

Running that on a SQL Server 2014 instance, I will see results like this:

Looking at my results, there is one that is highlighted – package0. This package is basically like the utility drawer – it contains most of everything that you will need to (or maybe want to) use with extended events. At least that is the definition of the package. It is supposed to contain all of the data maps, data types, operators, actions, and targets.

There is another very interesting package that is visible with this particular query. That is the SecAudit package. This package is used by SQL Audits. The contents of the package are accessed automatically when creating audits and audit specifications. That is a topic for a different discussion.