\nIt's another rainy day in Portland in summer, so I thought I'd stay inside and write more about SQL Server 2008 Extended Events.\n<\/p>\n
\nI wanted to finish things off by talking about actions<\/strong> and predicates. Need to make a detour at targets<\/strong>, 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.\n<\/p>\n \nThe available actions can be seen with: \nSo let's try sql_text with our existing EVENT SESSION.\n<\/p>\n \nCREATE EVENT SESSION PubsLocksETW \nBut 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.\n<\/p>\n \nThe 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).\n<\/p>\n \nCREATE EVENT SESSION MostLocks \nBut how did they figure out what to put after "SET"? Where does 'filtering_event_name' come from?\n<\/p>\n \nSELECT * FROM sys.dm_xe_object_columns \nThere they are… and the description field suggests a use for 'action'.\n<\/p>\n \nCREATE EVENT SESSION PubsLockByText \n— source_type= 1 means buckets by action, not by event \nAha… now I have buckets created based on the text of the SQL statement that caused the lock, rather than by object_id.\n<\/p>\n \nThis 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:\n<\/p>\n \nUSE pubs \nSET TRANSACTION ISOLATION LEVEL SERIALIZABLE \nLeave 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…\n<\/p>\n \nSELECT CAST(target_data as xml) FROM sys.dm_xe_session_targets;\n<\/p>\n \n <\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,29],"tags":[],"class_list":["post-775","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2008"],"yoast_head":"\n
\nSELECT * FROM sys.dm_xe_events WHERE type = 'action'\n<\/p>\n
\nON SERVER
\nADD EVENT sqlserver.lock_acquired
\n (ACTION (sqlserver.sql_text)
\n WHERE sqlserver.database_id = 12) — (pubs database), add a predicate too
\nADD TARGET package0.etw_classic_sync_target
\n (SET default_etw_session_logfile_path = N'C:\\temp\\sqletw.etl' )
\nGO\n<\/p>\n
\nON SERVER
\nADD EVENT sqlserver.lock_acquired (where sqlserver.database_id = 12) — (pubs)
\n— this means "create buckets based on object_id (object being locked in this case)"
\nADD TARGET package0.synchronous_bucketizer
\n (SET filtering_event_name='sqlserver.lock_acquired', source_type=0, source='object_id')
\nGO\n<\/p>\n
\nWHERE column_type = 'customizable'
\nWHERE object_name = 'synchronous_bucketizer'\n<\/p>\n
\nON SERVER
\nADD EVENT sqlserver.lock_acquired
\n (ACTION (sqlserver.sql_text)
\n WHERE sqlserver.database_id = 12) — (pubs database)\n<\/p>\n
\nADD TARGET package0.synchronous_bucketizer
\n ( set filtering_event_name='sqlserver.lock_acquired', source_type=1, source='sqlserver.sql_text')
\nGO\n<\/p>\n
\nGO\n<\/p>\n
\nBEGIN TRAN
\nUPDATE authors SET au_fname = 'bob';
\nROLLBACK — You don't really want everyone named 'bob', do you?\n<\/p>\n