If you have browsed XEvents to any extent you should probably be familiar with at least one map object that Microsoft has given us. That particular object is sys.dm_xe_map_values and I wrote a bit about it here. That object presents some good information about various data values within XEvents that are delivered with the Event payload as well as some hidden gems that can be extra useful for exploring SQL Server internals. The point is, maps is not necessarily a new concept.
While the concept of a map is not new within XEvents, the implementation in this case is a tad different. You see, the dm_xe_map_values object is more like an EAV object while the map I will introduce today is more of an ordinary lookup table. Let’s take a look.
The map I want to concentrate on for now is the sys.trace_xe_event_map table. You read that correctly – it is a table. We don’t have very many “tables” when dealing with XEvents, but this one happens to be. As Microsoft Docs describes it, the table “contains one row for each Extended Events event that is mapped to a SQL Trace event class.”
Click through for a script which shows how to map them, as well as a couple interesting points.
Extended Events are the all-around smart choice. They take a little bit of time to get used to, however. With thousands of new events and data points, it can be difficult to create an event session in a pinch. That is why it is important to have event sessions pre-scripted or pre-implemented on your SQL Server instances. A little bit of up-front work can save you a lot of time when you need information on the spot. Having them pre-scripted also prevents you from jumping back to Profiler, which has a much heavier footprint on your server.
When I create Extended Event sessions, I tend to use the SQL Server Management Studio wizard to find the events and actions (additional fields) that I want. Then, I will script it out and save it for later.
Below are five Extended Events sessions that I have found particularly useful and recommend you add to your toolbox.
Click through for all of those scripts, as well as queries to shred the resulting XML.
I am proud to introduce XESmartTarget: the easiest way to interact with extended events sessions without writing a single line of code.
XESmartTarget uses the Extended Events streaming API to connect to a session and execute some actions in response to each event. The actions you perform can be configured in a simple .json file, which controls the Response types and all their properties.
For instance, if you have a session to capture deadlocks, you may want to be notified whenever such an event is captured. All you need to do is configure XESmartTarget to send an email using the EmailResponse class. You can do that by creating a simple .json file with all the parameters needed
This looks quite interesting.
XE Profiler looks promising and can be really a great feature. We can use it with no issues on any version of SQL Server which supports extended events – not only with newest SQL Server 2017. I tested it with SQL Server 2014 and it was working well. Currently, lack of configuration of new templates, and logic based on hard-coded names is the biggest concern and discomfort for the user. However Microsoft didn’t officially release yet this version of SQL Server Management Studio, so it’s hard to say what will be the final feature functionality.
I’m hoping that when the final version appears, it will be good enough to get people finally to kick the Profiler habit.
There are, as of RC2 being released, 194(!) new Events to Extend your mind with. Not all of them are interesting to me, and I haven’t had time to pry into all of the ones that are interesting just yet.
This is a rundown of the new Events with names or descriptions that I found interesting, and will try to spend some time with.
I can’t promise anything
After all, getting some of these to fire is tougher than using a Debugger.
There are some interesting events here.
So what is the solution? Well it really depends. But the solution I wrote walks you through
Setting the default fill factor
Tracking page splits
Lowering the fill factor
Read on to learn more.
There was a question in dba.stackexchange.com titled “module_end extended events duration in microseconds?” that I answered and it was Microsecond in that instance.
Later on I questioned myself if the duration is always in Microseconds for extended events. I found, it is a mix of Millisecond, Microsecond and some are unknown meaning famous NULL. I wrote below tsql code to determine which is what.
Click through for the script that Taiob used to determine the answer.
Now that the extended events session is setup we can use some queries to query information about our AGs and error messages happening on our servers. First to query when the server failover and becomes your primary server query the event availability_replica_state_change. The first 10 lines just read in the files for the extended event session so you don’t have to identify the exact filenames. Then we parse the xml event for get the timestamp, previous state, current state, repliace name, and group name for the event FROM the filenames we collected before. In the WHERE clause were are looking for when the state has changed to PRIMARY_NORMAL indicating a failover to the server.
Read on for scripts showing what she extends and also how to query this data.
Per BOL you get the following information:
Errors with a severity of >= 20.
Memory related errors (Errors 17803, 701, 802, 8645, 8651, 8657 and 8902).
Non-yielding scheduler problems (Error 17883).
Sessions that have waited on locks for > 30 seconds.
Sessions waiting for a long time on preemptive waits (waits on external API calls).
Read on to learn more of the things this session contains as well as a couple ways you can access the data.
What this is going to do is create an extended event that will automatically startup when the SQL instance starts and capture all errors recorded that have a severity level greater than 10.
Full documentation on severity levels can be found here but levels 1 through 10 are really just information and you don’t need to worry about them.
I’ve also added in some extra information in the ACTION section (for bits and bobs that aren’t automatically included) and have set the maximum number of files that can be generated to 10, each with a max size of 5MB.
Check it out. At one point, I had created a small WPF application to show me errors that extended events caught. It completely freaked out a developer when I IM’d him and told him how to fix the query he’d just run from the privacy of his cube, with me nowhere to be seen.