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)', 'nvarchar(60)') AS database_name, n.value('(@count)', '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 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.
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!
This is the tenth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.