\nIt's summertime in Oregon and its been nice and warm out (75-85F) lately. But today its raining (or specifically, the skies are quite ominous right now), so it's time to write more about my latest favorite subject: SQL Server 2008 Extended Events.\n<\/p>\n
\nThe SQL Server 2008 extended events introduce quite a bit of new terminology, but in investigating the specifics you come across some familiar themes.\n<\/p>\n
\nExtended events are all contained in packages<\/strong>. An event package is identified by a GUID and a name. Three packages are provided and you can see brief descriptions by querying sys.dm_xe_packages. A package is just a container for all of the other objects (like events<\/strong> and targets<\/strong>) you'll refer to in event sessions<\/strong>. The grouping has no effect of EVENT SESSIONs; you can specify any object from any package in a single event session. The package names are: sqlserver, sqlos, and package0.\n<\/p>\n \nTwo of the object types that packages contain are events and targets. Events name the information you can collect, these are defined in event_columns. Targets define where the event information is captured and how its processed before being collected. So what can you capture, already?\n<\/p>\n \nSELECT * FROM sys.dm_xe_objects WHERE type = 'event'\n<\/p>\n \nOnly sqlos and sqlserver packages contain events. The events in sqlos are 40 low-level operating system-interaction events, as you might guess. An example is async_io_requested. The sqlserver packages contains over 80 events. These events seems to correspond to SQL Server counters you would see in performance monitor, rather than SQL Profiler trace events that EVENT NOTIFICATIONs use in SQL Server 2005, although there is some overlap. Many of these events only collect one event-specific column, a counter.\n<\/p>\n \nYou can get a list of all the available events and the event-specific columns they collect by using:\n<\/p>\n \nSELECT convert(varchar(55),o.name) as [Object Name] \nSo, to put this all together in an event session, lets use a variation of the BOL example:\n<\/p>\n \nCREATE EVENT SESSION test0 \nSee the previous blog entry for information about getting ETW working. Note that, in a single event session, we're using items from two different packages, sqlserver and package0. Start the session, using ALTER SESSION, then take a few checkpoints (or produce whatever event you decide to collect), ALTER SESSION to stop the session. Then you transform the (binary) ETL file to a .csv file by using tracerpt.exe.<\/p>\n","protected":false},"excerpt":{"rendered":" It's summertime in Oregon and its been nice and warm out (75-85F) lately. But today its raining (or specifically, the skies are quite ominous right now), so it's time to write more about my latest favorite subject: SQL Server 2008 Extended Events. The SQL Server 2008 extended events introduce quite a bit of new terminology, but […]<\/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-776","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2008"],"yoast_head":"\n
\n ,convert(varchar(25),c.name) as [Column Name]
\n ,c.column_id as [Column ID]
\n ,convert(varchar(12),c.column_type) as [Column Type]
\nFROM sys.dm_xe_objects o
\nJOIN sys.dm_xe_object_columns c
\nON o.name = c.object_name
\nWHERE o.object_type = 'event' AND c.column_type != 'readonly' — readonly columns are common to most events
\nORDER BY [Object Name]\n<\/p>\n
\nON SERVER
\nADD EVENT sqlserver.checkpoint_begin
\nADD EVENT sqlserver.checkpoint_end
\nADD TARGET package0.etw_classic_sync_target
\n (SET default_etw_session_logfile_path = N'C:\\temp\\sqletw.etl')
\ngo\n<\/p>\n