Today is another one of those installments in the long-running TSQL Party held monthly (a.k.a TSQL2SDAY).
This month we have an open invitation from Mike Donnelly (blog | twitter), asking us to talk about something new we have learned and then to teach about it. You can read the invitation in Mike’s own words on his blog.
Coincidentally, the topic is both pretty straight forward and easy on the one hand while somewhat difficult on the other hand. Mike said: “The topic this month is straight forward, but very open ended. You must learn something new and then write a blog post explaining it.” I find the topic to be difficult because I will usually blog about a topic when I have learned something new. On the other hand, sharing new stuff is pretty straight forward and enjoyable. Enter the brain split!
So, what I have learned recently?
Quite a bit. But what would I really like to share on this occasion?
For today, I would like to share more information about extended events. XEvents are great. There is a wealth of information to be garnered from XEvents. Furthermore, XEvents provide a great opportunity to keep learning.
While researching some events to help troubleshoot a specific issue, it dawned on me that there was some info that I had only looked at when I was looking at specific events. I started wondering how much of that info was out there. So here I will share some of this information that is available to you via queries within SQL Server. Much of this info is attainable through the re-purposing of some scripts I shared previously – here.
Custom Data Types
This wasn’t too much of a surprise because I had seen them throughout and taken advantage of the custom data types to get better information. But I might consider these custom data types to be more of the EAV model coming through than custom data types. One can expose the custom data types through an evaluation of data in the map_values DMV. Let’s take a look at a script that would lay the groundwork to see these data types.
SELECT DISTINCT oc.type_name , CASE WHEN ISNULL(mv.name,'') = '' THEN 'Standard' ELSE 'Custom'END AS DataClass FROM sys.dm_xe_object_columns oc LEFT OUTER JOIN sys.dm_xe_map_values mv on mv.name = oc.type_name WHERE oc.column_type <> 'readonly' ORDER BY DataClass DESC, oc.type_name ; DECLARE @EventName VARCHAR(64) = NULL ,@ReadFlag VARCHAR(64) = 'customizable'
Evaluating this data, one will see that in addition to the “standard” datatypes such as integer, there will be a “wait_types” data type. This data type will map to all of the wait types available through extended events. Additionally, the event that is associated to each of these custom data types is exposed through this query. When getting ready to use an extended event, knowing the kinds of data that will be exposed through a data point in the session will make the session data more usable. Knowing there is a custom data type (yes, it is really just a key value pair), can be extremely helpful.
Collection Flags
Many of the available events have “customizable” collection flags exposed. Understanding that these collection flags can be on or off is essential to saving some hair. Not all data is automatically collected for all events. Some prime examples of such events that do not automatically collect certain pieces of data are sp_statement_completed and object_created. The nice thing about these flags is that they have a value of “customizable” in the column_type field. Another good thing with these flags is that the description field gives a little documentation on what the behavior should be for the “on” and “off” states.
DECLARE @EventName VARCHAR(64) = NULL ,@ReadFlag VARCHAR(64) = 'customizable' SELECT oc.object_name AS EventName ,oc.name AS column_name ,oc.column_value AS column_value ,oc.description AS column_description FROM sys.dm_xe_object_columns oc WHERE oc.column_type = @ReadFlag AND oc.type_name = 'boolean' AND oc.object_name = ISNULL(@EventName,oc.object_name) ORDER BY oc.object_name ;
There is a good reason that some of those may be off by default. The addition of this information may cause an additional load or may be information overload. It is up to the consumer to determine if the data is going to be of significant importance. Once determined, enable or disable the flag as appropriate.
These queries provide a good amount of information about the extent of custom data types as well as the collection flags that may be available to use when creating event sessions in SQL Server. Understanding that this data and these options are there is important to capturing better event info.
