It's another rainy day in Portland in summer, so I thought I'd stay inside and write more about SQL Server 2008 Extended Events.
I wanted to finish things off by talking about actions and predicates. Need to make a detour at targets, too. I noticed the BOL examples (my point is to try not to repeat things you can find in the BOL) don't contain an example of actions in DDL. So we'll start with them. An action is an additional piece of data that you can tack on to an event. Like a stack trace, or even a causality ID. Or sql_text.
The available actions can be seen with:
SELECT * FROM sys.dm_xe_events WHERE type = 'action'
So let's try sql_text with our existing EVENT SESSION.
CREATE EVENT SESSION PubsLocksETW
ON SERVER
ADD EVENT sqlserver.lock_acquired
(ACTION (sqlserver.sql_text)
WHERE sqlserver.database_id = 12) — (pubs database), add a predicate too
ADD TARGET package0.etw_classic_sync_target
(SET default_etw_session_logfile_path = N'C:\temp\sqletw.etl' )
GO
But the sql_text doesn't appear in the ETW file. I did this a few times, because I thought I got it wrong. Maybe it will appear in the async file target, which isn't in this CTP. The BOL also says that not every action is valid for every event. Hmmm… the metadata (sys tables) seemed to be happy, but it ain't there. But I can use it with the bucketizer and pairer targets.
The bucketizer makes …er' buckets (groups) of different "readings" (events) on a single data object. As in, group by lock type or group by cpu time. You can control how many buckets it makes. In fact that bucketizer target needs syntax like the following (from BOL).
CREATE EVENT SESSION MostLocks
ON SERVER
ADD EVENT sqlserver.lock_acquired (where sqlserver.database_id = 12) — (pubs)
— this means "create buckets based on object_id (object being locked in this case)"
ADD TARGET package0.synchronous_bucketizer
(SET filtering_event_name='sqlserver.lock_acquired', source_type=0, source='object_id')
GO
But how did they figure out what to put after "SET"? Where does 'filtering_event_name' come from?
SELECT * FROM sys.dm_xe_object_columns
WHERE column_type = 'customizable'
WHERE object_name = 'synchronous_bucketizer'
There they are… and the description field suggests a use for 'action'.
CREATE EVENT SESSION PubsLockByText
ON SERVER
ADD EVENT sqlserver.lock_acquired
(ACTION (sqlserver.sql_text)
WHERE sqlserver.database_id = 12) — (pubs database)
— source_type= 1 means buckets by action, not by event
ADD TARGET package0.synchronous_bucketizer
( set filtering_event_name='sqlserver.lock_acquired', source_type=1, source='sqlserver.sql_text')
GO
Aha… now I have buckets created based on the text of the SQL statement that caused the lock, rather than by object_id.
This is getting to be too much for a single blog entry. But one last thing for now. You may not have noticed that I switched from using the "package.asynchronous_bucketizer" as the BOL does to using "package0.synchronous_bucketizer". Why? Because I want to do a simple, controlled experiment and I may not want to wait for the buffer to be full and async bucketizer to write out. BTW, for a simple controlled experiment, you can do:
USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE authors SET au_fname = 'bob';
ROLLBACK — You don't really want everyone named 'bob', do you?
Leave the EVENT SESSION running to see the buckets. BOL has a cool query against the XML structure but to see the raw XML, if this is your only EVENT SESSION running…
SELECT CAST(target_data as xml) FROM sys.dm_xe_session_targets;