Creative Extended Event Sessions

In the previous article, I showed how to take advantage of collations to add more unique and interesting names to databases. In that article, I alluded to the better collation to use but failed to be very explicit about it.

As Solomon Rutzky pleads in many articles, the best default collation to use is not any collation that starts with SQL, but rather the collation one should use is Latin1_General_100_CI_AS_SC. I agree. Let’s not use the old default collations anymore and start using more modern collations – even if Microsoft does not change the default, you should change your defaults in your builds to represent a better more modern collation!

Fun with Sessions

Let’s suppose I strongly suspect I am having an issue with the ghost cleanup process for a specific database called ‘👻’. I want to prove whether the suspicions are accurate or not so I resort to an old tried and true XE session to validate. Here is that session.

Now, let’s try to validate my suspicions.

The best part is yet to come. With the event session running, I need to execute the preceding query. The query may take several minutes to complete – don’t worry yet. After the query completes, it is time to check the event session data to try and confirm the suspicions.

This is where things start to get a little interesting! If I try to open the session data to view it from the GUI, I will see something like the following.

Well, that is not very useful! Let’s try it from a TSQL query instead!

Well, that seems to be a little better. At least I get some data back at this point. Sadly, the database name is poorly displayed. That said, the database name that is displayed is accurate – just not very useful. As it turns out, XE is lagging a bit behind in the ability to display the characters that require certain code points (seemingly any that require multiple code points or above a certain range). I have not yet tested the full limitation, just understand that there is a limitation.

What if I tried a different target such as an event_file target?

The event session will start successfully. In addition, I can confirm that the event file is created on disk.

Then I will rerun the experiment to test the ghost_cleanup process against theΒ 💩 database. If I query the event session with TSQL, I will see something like this.

If I look at the event session from within the GUI, I will see something slightly more usable.

Peeking at the file data (instead of displaying it in tabular format) from tsql commands, I will see something like this.

Stay tuned to see how we can resolve this issue where the 💩 andΒ 👻 are not being translated properly in these TSQL queries for these sessions.

Put a bow on it

A picture is worth a thousand words, right? Sometimes, a picture for an event session just may be able to say it better than 50-60 letters used to try and describe the session. Maybe you just want to try it out for fun. Either way, the use of these characters in an event session can be a little difficult when looking to review the data.

Interested in learning about some deep technical information instead?Β Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the tenth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

5 Comments - Leave a comment
  1. Erik Darling says:

    There’s a small typo in one of your screen caps — I think “perviously” should be “previously”. But perviously should be a word. A fantastic word.

  2. […] the previous article, I explored the opportunity of using special characters (or emojis) within Extended Event Sessions. […]

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.



Calendar
January 2020
M T W T F S S
« Dec    
 12345
6789101112
13141516171819
20212223242526
2728293031  

Welcome , today is Friday, January 24, 2020