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.

USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT * 
          FROM sys.server_event_sessions 
          WHERE name=N'')
    DROP EVENT SESSION [] 
    ON SERVER;
GO
CREATE EVENT SESSION [] ON SERVER
ADD EVENT sqlserver.ghost_cleanup (
ACTION ( sqlserver.database_name, sqlserver.client_hostname,
sqlserver.client_app_name, sqlserver.plan_handle, sqlserver.sql_text,
sqlserver.query_hash, sqlserver.session_id )
WHERE ( [database_name] = N'') 
AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' )
ADD TARGET package0.histogram ( SET filtering_event_name = N'sqlserver.ghost_cleanup'
, source = 'database_name' )
WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS
, TRACK_CAUSALITY = ON );
ALTER EVENT SESSION [] 
ON SERVER 
STATE = START;
GO

Now, let’s try to validate my suspicions.

Use [];
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF SCHEMA_ID('Halloween') IS NULL EXECUTE ('CREATE SCHEMA Halloween');
GO
IF OBJECT_ID('Halloween.Ghosts','U') IS NOT NULL
BEGIN
DROP TABLE Halloween.Ghosts
END
GO
DECLARE @BeginDate DATE = '2014-10-01'
,@EndDate DATE = '2014-10-31'
 SELECT TOP 1000000
        Pinky       = IDENTITY(INT,1,1),
        Blinky= 'GHOST ITEM ' + CONVERT(VARCHAR(20),ISNULL('', 0)),
        CandyMan= ABS(CHECKSUM(NEWID()))%50000+1,
        Sadako= CHAR(ABS(CHECKSUM(NEWID()))%26+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%26+65),
        Slimer= DATEADD(DAY,RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY, @EndDate,@BeginDate) ), @EndDate),
        Poltergeist= RIGHT(NEWID(),12),
        MalcomCrowe= CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
        TheBlob= REPLICATE('Hey, Dracula!! Why do you say blah, blah blah?',1000)
   INTO Halloween.Ghosts
   FROM master.dbo.sysColumns t1,
        master.dbo.sysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
/* let's get our Blinky updated properly */Update hg
Set Blinky = 'GHOST ITEM ' + CONVERT(VARCHAR(20),ISNULL(Pinky, 0))
From Halloween.Ghosts hg;
--===== A table is not properly formed unless a Primary Key has been assigned
     -- Takes about 1 second to execute.
  ALTER TABLE Halloween.Ghosts
        ADD PRIMARY KEY CLUSTERED (Pinky)
/* additional index to show the ghosts */Create NonClustered Index IX_GhostPinky on Halloween.Ghosts (Pinky)
/* tweak the ghost cleanup with an undocumented TF 
Required for DBCC Page results to show which pages have a GHOST entry
Don't try this at home - we are what you call experts (besides this is only a lab box!!) */--DBCC TRACEON (661, -1)
--GO
/* Somebody made a mistake and thought Halloween was Oct 30 instead of Oct 31 
we need to kill those records
*/Delete top(666)
From Halloween.Ghosts
WHERE Slimer = '10/30/2014';

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.

SELECT 
    n.value('(value)[1]', 'nvarchar(60)') AS database_name,
    n.value('(@count)[1]', 'bigint') AS ghost_count
FROM
(SELECT CAST(target_data as XML) target_data
 FROM sys.dm_xe_sessions AS s 
 JOIN sys.dm_xe_session_targets t
     ON s.address = t.event_session_address
 WHERE s.name = N''
  AND t.target_name = 'histogram' ) as tab
CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)
;

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?

USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT * 
          FROM sys.server_event_sessions 
          WHERE name=N'')
    DROP EVENT SESSION [] 
    ON SERVER;
GO
CREATE EVENT SESSION [] ON SERVER
ADD EVENT sqlserver.ghost_cleanup (
ACTION ( sqlserver.database_name, sqlserver.client_hostname,
sqlserver.client_app_name, sqlserver.plan_handle, sqlserver.sql_text,
sqlserver.query_hash, sqlserver.session_id )
WHERE ( [database_name] = N'') 
AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' )
ADD TARGET package0.event_file
(SET filename = N'C:DatabaseXE.xel'
 )
WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS
, TRACK_CAUSALITY = ON );
ALTER EVENT SESSION [] 
ON SERVER 
STATE = START;
GO

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.