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;
GONow, let’s try to validate my suspicions.
Use [
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;
GOThe 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.