I've said before how thrilled I am will the new extended event UI in SSMS for SQL Server 2012. However, you might be one of the early adopters who made up their own scripts to define extended event sessions, and use hardcoded scripts to harvest the results. So, you may run into this problem if you're using what I call "lazy XML" in the event harvesting script. Take, as an example, an extended event session defined as follows with 3 actions:

create event session errorsession on server
add event sqlserver.error_reported
(
action
(
package0.callstack,   
sqlserver.session_id,  
sqlserver.sql_text    
)
where error = 547 and package0.counter <= 3 
)
add target package0.ring_buffer
with (max_dispatch_latency = 1 seconds)
go

In previous versions, you could pretty much depend on the XML presenting the actions in order. So the following XML would return a subset of the event information in rows and columns.

SELECT
  Data2.Results.value ('(data/.)[1]', 'int') AS ErrorNumber,
  Data2.Results.value ('(action/.)[2]', 'int') AS Session,
  Data2.Results.value ('(action/.)[3]', 'nvarchar(max)') AS SQLStatement
from
(
select CAST(xet.target_data as xml) as data
from sys.dm_xe_session_targets xet
join sys.dm_xe_sessions xe
on (xe.address = xet.event_session_address)
where xe.name = 'errorsession') events
cross apply Data.nodes ('//RingBufferTarget/event')
  AS Data2 (Results)

The XML above is based on the assumption that action 2 is the sqlserver.session_id and action 3 is the sqlserver.sql_text, because it doesn't check names, just blindly uses the ordinal number in XPath. It makes the XML easier to write and a bit faster to execute, but its "lazy" XML. This order of actions was a valid assumption in SQL Server 2008; its not valid in SQL Server 2012. The data fragment containing the action data appears like this:

    <action name="sql_text" package="sqlserver">
      <type name="unicode_string" package="package0" />
      <value> 
        DELETE pubs.dbo.jobs
      </value>
    </action>
    <action name="session_id" package="sqlserver">
      <type name="uint16" package="package0" />
      <value>53</value>
    </action>
    <action name="callstack" package="package0">
      <type name="callstack" package="package0" />
      <value>..callstack elided...</value>
    </action>

So now, sql_text (the third action defined) is the first action presented. The fragile harvesting script will break. So, to be one the safer side, if you have any such scripts change them to actually look for the element for want using a named XPath predicate, like this:

SELECT
  Data2.Results.value ('(data[@name="error_number"]/.)[1]', 'int') AS ErrorNumber,
  Data2.Results.value ('(action[@name="session_id"]/.)[1]', 'int') AS Session,
  Data2.Results.value ('(action[@name="sql_text"]/.)[1]', 'nvarchar(max)') AS SQLStatement
from
(
select CAST(xet.target_data as xml) as data
from sys.dm_xe_session_targets xet
join sys.dm_xe_sessions xe
on (xe.address = xet.event_session_address)
where xe.name = 'errorsession') events
cross apply Data.nodes ('//RingBufferTarget/event')
  AS Data2 (Results);

Note that it's probably better to do this with the "data" items too (ErrorNumber in the query above), although you may be a little safer with these, as they have a defined schema per-event. But BOL does point out that events have a "versioned" schema. The actions can be defined in any order, so make your scripts more robust. Use XPath predicates. And if you're not on SQL Server 2012 yet, change your scripts now.

@bobbeauch

Last week, while teaching a class based around SQL Server 2008 R2, I happened to "brag up" the new tools in SQL Server 2012, mainly the Extended Event graphic user interface in SSMS and the ability of Database Tuning Advisor to tune from the plan cache. This, of course, immediately brought up the question: "Do the new tools/features work with pre-SQL Server 2012 instances". So, I had to try it.

Database Turning Advisor's "tune from plan cache" feature works just fine with a SQL Server 2008 R2 database. I'd assume that this means it works against SQL Server 2008, not so sure about SQL Server 2005, but the plan cache hasn't changed that much since 2005. It's worth a shot. Don't have a 2005 instance handy.

The Extended Event graphic user interface, however, was another story. This node just didn't appear when connecting SQL Server 2012 RC0 SSMS to a 2008 R2 instance. Since I knew that the Extended Event GUI just used new SQL Server 2012 SMO XEvent classes, I tried writing a simple PowerShell script to get a XEStore class against a SQL Server 2008 R2 instance. The error message "Operation not supported on version 10.50.2500" appeared. And, of course, a similar error message appeared when attempting to use the PowerShell SQL Server provider XEvent "directory". Strange, as most SMO classes are supported against older versions of SQL Server, some of them back to SQL Server 2000.

Well, one out of two ain't bad, I guess. Enjoy tuning from the plan cache.

@bobbeauch

I'm not usually one for repeating stuff you can find in BOL. Usually, a waste of your time and mine. But this feature, for SQL Server Denali, is pretty well buried; well at least from me, and I knew what I was looking for.

I've always been interested in (well, wrote a whitepaper on) using client-side/network trace (known as BID trace) with server-side trace and coordinating/merging the traces using ETW. Extended Events and even, although its not recommended, even trace events (but DO use Extended Events), support ETW.The biggest drawback to this is that, in order to see the correlation you almost have to be running your traces in a vacuum. There's no client/server correlation ID.

Well, in SQL Server Denali, at least for some clients currently, there IS. Only the SNAC11 provider (that's ODBC and OLE DB) supports this. All the information you need to get you going (if you've done this before, if not, read the whitepaper), is doc'd in BOL here. Two cavaets: the ETW trace for clients contains one (two in some cases) columns related to IDs and correlation. One's called ActivityID, the other RelatedActivityID. The correlation ID is NOT in these, but is in the field after the UserData field. You'll see it as "ActivityID m_id:[some guid]". In the Extended Event (server) trace, it shows up as an "Activity ID Transfer Event". The other cavaet is that its not on every client-side event, only some of them.

I'll be showing client-server tracing at the SQLskills immersion event in Bellevue next week.

@bobbeauch

I've been looking at the Extended Event library and PowerShell provider "directory" in Denali and decided that I like it. It's a traditional SMO set of classes, which means there's not a whole lot new to learn.

All the SMO libraries connect to a "store" using either an integrated security default connection or an SqlStoreConnection. SfcStoreConnection has a constructor that takes a SqlConnection. All the stores expose collections of database objects and each object may have its own collection of objects (e.g. tables contain columns, XEvent sessions contain events). Each object has Create, Drop, and Alter methods that represent CREATE, ALTER, and DROP in SQL DDL.

The XEvent library reflects the XEvent metadata views and DMVs and is divided into metadata about the event system (these are the "Info" classes, e.g.SessionInfo) and information about the store's event system objects (e.g. Sessions, Actions, etc). Reference Mike Wachal's blog post for a nice diagram of the object model.

The XEvent library continues on a path started if I recall, by the PBM libraries. There is a method on the XEStore to CreateSessionFromTemplate and a static method, SaveSessionToTemplate. These load and save session definitions from XML files. Although this likely corresponds to template files in SQLProfiler, it also means that XEvent sessions can be loaded into the store in different different ways: SQL DDL, direct SMO programming in .NET languages (like PowerShell), and importing XML files. I kind of like the "import XML" way as an easy way to move Session or Policy definitions among instances. Although there's a few examples of using PowerShell to create/manipulate XEvent sessions in BOL, I'd likely choose DDL over this method. With PBM, the choice between system sprocs and PowerShell is more of a tossup.

The biggest user of SMO has always been SQL Server Management Studio. Although the SSMS interface is currently limited to Session enumeration Import/Export Session definition, the model is set to allow much more.

@bobbeauch

I've been going through the XEvents in Denali CTP1 in a fairly rigorous amount of detail, looking for what's changed and specifically for breaking changes. Running all my old event sessions... found one.

One (well OK, quite a few) of my "for illustration" XEvent Sessions have a predicate that looks at the error number for the event sqlserver.error_reported. In SQL Server 2008/R2 XEvents, this predicate would be something like "where error = 999", but Denali the event field for "error" in this event is "replaced" by one for "error_number".

This is a small change but a breaking change, meaning that all event sessions that rely on the event field "error" would break. There are some additional event fields for this event in Denali (so there's probably a good reason for the change), but since I don't think I'm the *only* person with event sessions referencing this event and field, I thought this would be useful information to share. The system_health session uses this event field too, and it (naturally) had to be updated.

@bobbeauch

Just having a look at PowerShell support in SQL Server Denali CTP1. In Denali you must install PowerShell V2 or it will be installed as part of the installation process. Although the minishell architecture of SQLPS is unchanged so far. Rumor is that changing this (maybe into a module, like Chad Miller did?) might in the offing.

Not new cmdlets yet, unless I'm missing something, but there are enhancements to the provider. There are two new "subdirectories" in the provider, under the SQLSERVER: psdrive: XEvent and IntegrationServices. XEvent has two subdirectories, Packages and Sessions. Packages is a hierarchical rendering of metadata; the information you'd get from the sys.dm_xe_* metadata views. Sessions is a hierarchical rendering of information about currently defined sessions.The IntegrationServices subdirectory contains a Packages subdirectory (after the subdirectory specifying server\instance, naturally).

Having XEvents in the provides means that there is, of course, a set of classes that encapsulate XEvents in SMO. This was something that was missing in SQL Server 2008.So Jonathan Kehayias wrote one for his Extended Events Manager And where there is SMO, there is likely a set of dialogs in SSMS. Well, not really... there's a node off of the Management Node in SSMS and you can import event sessions from XML files. Not quite Extended Events Manager.

By browsing through the hierarchy (or looking at the sys.dm_xe_* views, you can also observe that XEvents has been greatly expanded in SQL Server 2011 CTP1. At the top level, there are three new XEvent packages, ucs, sqlclr, and a second sqlserver (same name but different package id). XEvent events in Denali have increased in number from 254 to 449; almost twice as many items can be tracked by extended events. I selected each event metadata set into tables on both releases and did the SQL equivalent of a diff, there's something new in almost every kind of metadata. For example There is a target called the "router" target that "routes events to listeners". But...routes to whom?

There may be a hint in two new DMVs for extended events, dbo.trace_xe_event_map and dbo.trace_xe_action_map. These DMVs map XEvent events to profiler events and XEvent actions to profiler columns. Perhaps an XEvent-based/profiler trace synergy of some kind?

I've been doing talks and demos on SQL Server 2008 Extended Events for a while now, it's one of my favorite parts of the product. Per session waitstats, SQL stack, built-in system health session, what's not to like?

Before my talk at TechEd EMEA for IT Professionals last week (which went really well), I discovered something that was very helpful in getting things to run more smoothly. Since the betas, I've always used multi-part names, not only for events, actions, and providers, but for predicates too. To be clearer. Lately, I'd been running into a weird problem. Consider the following event session:

create event session errorsession on server
add event sqlserver.error_reported
(
action
(   
sqlserver.session_id, 
sqlserver.sql_text    
)
where sqlserver.error_reported.error = 547
and package0.counter <= 3 
)
add target package0.ring_buffer
go

About one third of the time, running this DDL would produce:

Msg 25706, Level 16, State 8, Line 1
The event attribute or predicate source, "sqlserver.error_reported.error", could not be found.

Then, I'd move the DDL code to another window, execute it again, and it would work. Sometimes starting up the session:

alter event session errorsession on server state=start

would produce the error. And, of course that attribute DID exist, when I got past the weird error, it resultiing session worked like a charm. You can see the attribute/field in sys.dm_xe_object_columns. Hmmmm, scratches head...

After looking at the definition of the system_health session, I decided to try a one-part attribute name. Instead of:

...where sqlserver.error_reported.error = 547 and...
-- how about
...where error = 547 and...

Bingo! Now CREATE and ALTER SESSION work first time and every time. Don't know why this should happen, but at least there's a syntax correction that works. For the folks at the talk, all my event session code will be up on the SQLskills website shortly, under the "Past Conferences" section. With ONE-PART attribute names in predicates.

BTW, a few other things to remember to save you some head-scratching.

When using the ETW target, your SQL Server service account (which DOES have least-privileges...right?) needs to be a member of the Performance Monitor Users and Performance Log Users Windows groups.

When using a file-based target, the service account has to have permissions on the directory where you're writing the file. This sounds obvious, but folks forget that the SQL Server service account isn't all-powerful. Or at least *shouldn't be*, if you're running SQL Server as local system or administrator, FIX IT, using SQL Server Configuration Manager (not Control Panel/Services).

Use an asynchronous target with default event latency rather than a synchronous target, for less overhead, better throughput. However the ETW target is synchronous only.

All of the events, targets, etc are sharable, mix-n-match, across all packages WITH ONE EXCEPTION. The items in the SecAudit package are private. Folks always want to use SecAudit.asynchronous_security_audit_event_log_target, ... you can't. It's for the new Auditing feature only.

All that said, Happy extended eventing!

One of my favorite new features of SQL Server 2008 is extended events. I've written a bunch of blog entries on 'em (use the search, type in Extended Events). So a few days ago, I recieved an email from Jonathan Kehayias directing me to his new program on Codeplex, the SQL 2008 Extended Events Manager, asking for my opinion and suggestions.

Well, my opinion is "I like it a lot". And one of my first suggestions was a starter help file, because those of us who are sometimes "GUI challanged" might miss features upon first glance. And every new dialog I discover enforces my appreciation for the program's usefulness.

So here's a short starter walkthrough.

1. When you bring up the program, an empty window appears. Choose File-New Connection from the menu to get a connection to an instance. You can change connection but you can only have one connection open at a time.
2. A treeview appears in the lefthand pane of the main window. It shows information about your current event sessions. An event session consists of one or more events. Events contain event fields, actions, and can contain predicates. Each event session has a target with options related to the target specified.
3. Each event session has a context menu. You can Edit or Drop the event session, Script the event session for CREATE or DROP, and Stop and Start the event session.
4. Choosing New Event Session from the context menu on the (top-level) instance node of the tree or choosing Edit Event Session on an existing event session brings you to the Session Editor dialog.
  a. For a new session, you need to enter the session name.
  b. Clicking the hyperlink for Add Event brings you to the Event Editor dialog. Here you can choose events, actions, and predicates with the help of "search terms" that help you locate the event you want. There's even a Predicate Editor.
  c. Clicking the hyperlink for Add Target brings you to the Target Editor.
  d. Saving an event session in the Session Editor creates it immediately or you can script the event session.
5. The Extended Events Metadata Viewer is available from the content menu of the (top-level) instance node as well. This dialog lets you browse graphically through the Extended Event metadata.

BTW, the program consists of two pieces, the GUI program and the ExtendedEventsManager library. The library is meant to be as SMO-like as possible (there currently are no SMO classes for Extended Events). This means that you could even load the library into...let's say PowerShell..and use it there also.

Post enhancement requests, bug reports, etc to the Codeplex project page.

It's easier than you'd think... When you use the SQL Server 2008 CREATE EVENT SESSION DDL statement with extended events, you specify:

Events to be captured (e.g. sqlserver.error_reported)
Actions to be fired to add more information (e.g. sqlserver.sql_text)
Predicates for conditional capture (e.g. where sqlserver.error_reported.error = 547)
Event target to collect the events (e.g. package0.ring_buffer)
Addtional options (e.g. MAX_MEMORY)

In general, the way you look for events, actions, predicates, etc to use is to query against the metadata views:

select p.name + '.' + o.name as [Full Name],
       o.description s [Description]
from sys.dm_xe_objects o
join sys.dm_xe_packages p on o.package_guid = p.guid
-- @type can be 'event', 'action', etc
where o.object_type = @type order by p.name, o.name

Two of the actions listed have to do with activity (causality): package0.attach_activity_id and package0.attach_activity_id_xfer. In addition there is an option TRACE_CAUSALITY. I thought you'd have to set the TRACE_CAUSALITY option and add the actions to collect activity information. But attempting to add either action produced an error.

It's easier than that. Simply turning on the TRACE_CAUSALITY option is enough. The actions can't be specified in CREATE EVENT SESSION (or ALTER EVENT SESSION) because they are for internal use. The activity_ids show up at the target without explicitly naming the actions.

Last blog entry on SQL Server Extended Events for a while. But...a couple of questions came up since I wrote my first blog entry on SQL Server Extended Events.
  What are the major advantages to SQL Server Extended Events?
  Is this really using the Crimson event system?

There's a couple of reasons that come to mind as an answer for the first one. First, and maybe foremost, this eventing system has an ETW target and therefore allows end-to-end tracing. ETW is a provider-based tracing system that is integrated throughout Windows. With the providers available ("logman -query providers" from the command line) you could trace from your ASP.NET app (by way of your IIS server), into SqlClient, across the network (providing that you can decode a TDS trace), into SQL Server, and back. That's a lot of power.

The main hassle I've always had with ETW is the dearth of post-processing tools available. You can use the tracerpt utility to process the ETW output into a comma-separated value file, but where you go from there depends on how well how you post-process the CSV. There is a logreader utility that can do simple SQL-like queries against a variety of log file formats. At one point, I made up a simple SSIS job to load SqlClient ETW output into SQL Server to do T-SQL queries, but where you go from there depends on your ability to decode the variable "data" fields in each event. Although the .mof files allow you to decode the binary format into columns based on the data type, CSVs aren't usually self-describing either, you have to know what each bit means.

Next reason why I was intrigued was the granularity of the eventing. At first glance, you can:
1. Create arbitrary groupings/rankings (buckets) on the event data with the bucketizer
2. Pair alloc/dealloc of most any type of resource with the pair_matcher
3. Add extra data (actions) to events. They even added a mechanism to determine causality.
4. Use as many targets as you wish (targets are separate from events and actions)
5. Add events and targets to a running session
6. Specify how much resources (like memory, dispatch latency) your trace should take (see CREATE EVENT SESSION DDL)
7. Use synchronous or asynchronous event collection, and event buffer retention
8. Specify memory partitioning by CPU or NUMA node

One of the good things about a trace is to attempt to balance "intrusiveness" (which slows things down) with thoroughness (you ARE usually tracing because there's a problem, after all).

The other question concerns the Crimson eventing system. Crimson is a really old codename for Windows Unified Eventing (Windows Eventing 6.0). You can collect your events in XML format and it uses an XML config file for registration, hence my possible confusion with the tern "XEvent" which I'd heard used for SQL Server's Exgtended Events. Matt Pietrick describes it as "an attempt to unify event log and ETW tracing". It's available on Vista and Longhorn OS's only. Here's a couple more references:

http://msdn.microsoft.com/msdnmag/issues/07/05/SecurityBriefs/
http://msdn.microsoft.com/msdnmag/issues/07/04/ETW/

Don't search for XEvent like I did, you'll get a lot of hits for the XWindow system XEvent (remember XWindows?). Or Crimson, you'll get a lot of hits on University of Alabama.

All of the articles refer to using the wevtutil utility to list event providers, like logman lists ETW providers. So I installed SQL Server 2008 on Longhorn Server (Windows Server 2008) beta3 and looked for "new" event providers and events. I didn't see any, so I'm not sure that SQL Server Extended Events will register anything more than ETW with Windows Unified Eventing. And the bucketizer/pairer targets write to dynamic management views, not to the event log. There is one more target, the asynch file system target, but that's not in this CTP. So stay tuned, perhaps this is an investing towards future "unified eventing".

BTW, I began to wonder: is unifying the event log with ETW data is really a good idea. Event logs are the "normal" messages that are emitted, tracing seems to me to be a "special occasion" messaging with possible very high message volumes. I don't think the two are actually mixed in the same physical location in Vista/Longhorn, but...what do you think about this unification?

This won't be as long of an entry because I'm trying to finish describing the items that you can use in an XEvent session, that is, the items that exist in a package.
  Events
  Targets
  Actions
  Predicates
  Maps
  Types
 
Let's do types and maps. A type is simply a data type, a simple type like Int16 or complex type like 'SOS_context'. Almost all the types live in package0, there's only one each in sqlos and sqlserver packages. Maps are enumerated constants.

See types:
select * from sys.dm_xe_objects where object_type = 'type'

and map:
select * from sys.dm_xe_objects where object_type = 'map'

and legal enumeration (map_key) values:
select map_value, map_key from sys.dm_xe_map_values where name = 'keyword_map'

Predicates: for predicates (think filters in SQL Profiler), you need a predicate source and a predicate comparator.
select * from sys.dm_xe_objects where object_type like 'pred%' order by object_type

There's probably more to it than this, there are customizable event attributes that can be SET in ADD EVENT, and predicates can use event fields for filtering (but not actions). But this will get you started.

One last thing that bears mentioning is the pair_matching target. This target allows you to specify a pair of events (like lock_acquired, lock_released) and after you've run the workload a while, it will show you (the relevent fields in the XML structure exposed by target_data (as in, SELECT CAST(target_data as xml) FROM sys.dm_xe_session_targets;) those events that do not match. That is, the locks that have been acquired but not yet released. VERY cool.

Once again BOL shows an example that takes advantage of knowledge of the pair_matching target's XML data structure. The XML used for target_data appear to be schema-less, i.e. they don't go by a named XML schema, i.e. you have to know what the structure items (elements, attributes, and values) mean. BTW, I keep referring to the BOL because I very much like the info in the BOL, as far as it goes, especially at this early stage. I come to (hopefully) elucidate and expound upon the BOL, not to complain about it. THANKS Buck, Alan, Steve, and all...

You can specify begin and end events, begin and end matching_columns and matching_actions. This is from:

SELECT * FROM sys.dm_xe_object_columns
WHERE column_type = 'customizable'
WHERE object_name = 'pair_matching'

Hope this was useful. Happy event tracing.

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;

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 in investigating the specifics you come across some familiar themes.

Extended events are all contained in packages. 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 and targets) you'll refer to in event sessions. 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.

Two 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?

SELECT * FROM sys.dm_xe_objects WHERE type = 'event'

Only 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.

You can get a list of all the available events and the event-specific columns they collect by using:

SELECT convert(varchar(55),o.name) as [Object Name]
      ,convert(varchar(25),c.name) as [Column Name]
      ,c.column_id as [Column ID]
      ,convert(varchar(12),c.column_type) as [Column Type]
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_object_columns c
ON o.name = c.object_name
WHERE o.object_type = 'event' AND c.column_type != 'readonly' -- readonly columns are common to most events
ORDER BY [Object Name]

So, to put this all together in an event session, lets use a variation of the BOL example:

CREATE EVENT SESSION test0
ON SERVER
ADD EVENT sqlserver.checkpoint_begin
ADD EVENT sqlserver.checkpoint_end
ADD TARGET package0.etw_classic_sync_target
    (SET default_etw_session_logfile_path = N'C:\temp\sqletw.etl')
go

See 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.

When starting out with XEvent support, I thought it would be good to start with the ETW target, although you can capture and catagorize events in buckets with the async bucketizer target, and pair related events (like obtain lock/release lock) with the pair matching target. Both VERY cool. But I just wanted a raw, vanilla trace, to start out. And I wrote a paper on ADO.NET and ETW once. So easy one first, I thought...

It turns out that you need privileges to start an ETW session. The ETW session is started for you (rather than you using the logman utility and starting it yourself) when you issue an ALTER EVENT SESSION...STATE=START. But mine never started. It always produced:

Msg 25602, Level 17, State 17, Line 1
The target, "CE79811F-1A80-40E1-8F5D-7445A3F375E7.package0.etw_classic_sync_target", encountered a configuration error during initialization.  Object cannot be added to the event session.

BTW, the guid before the name of the package is the package module id. You don't have to use it in CREATE EVENT SESSION...ADD TARGET...

The reason for this (for those of you that aren't reading the Katmai forums) is that the SQL Server service account is used to activate the ETW session. In order for this to work, the service account must be a member of the groups "Performance Monitor Users" and "Performance Log Users". Mine wasn't a member of "Performance Log Users". Make it a member of the group and this target "works a treat", as my UK friends would say. I'm tracing events to ETW as I write this. THANKS to Jerome Halmans for this information, its not yet in BOL that I could find.

I've always installed SQL Server (since 2005) by creating a simple account that's a member of only the USERS group in Windows machine/domain. During install SQL Server gives this account all the privs (and only the privs) it needs. Mostly it does this by creating a group SQLServerMSSQLUser[machine][instance]. But it also makes the user you specify (I call it SQLService) members of groups (like "Performance Monitor Users") when it requires group membership. It's a good idea to pick a service account this way for principal of least privilege, rather than running SQL Server as something else, like Admin or LocalSystem. See the security best practices whitepaper for details.

It's an interesting observation that not all the privs you need are tied to that single group, created at installation. That's (one of a few reasons) why its always best to use SQL Server Configuration Manager to change the service account rather than the "Services" control panel applet.

I don't know if they're going to add "Performance Log Users" to the list of things that the installer does. If they don't add it automatically, don't forget to add it yourself for this feature (that is: ETW target in SQL Server XEvent). And don't forget to point the ETL file to a directory that the service account has permission to write to.

When I'm doing problem solving, its always good to have too much information rather than too little. With this in mind, I was quite interested in looking at SQL Server Extended Events (XEvent support) in SQL Server 2008.

You could always get diagnostic information in SQL Server through DBCC and SQL Trace/SQL Profiler. In SQL Server 2005 there are enhancements to SQL Profiler, dynamic management views (which enhanced and in some cases superceded DBCC information), DDL Triggers, and Event Notifications. There is also a WMI provide for events which uses event notifications internally. Event Notifications are sent to a SQL Server service broker queue and the events that are exposed are the same ones DDL triggers handle and most of the ones that SQL Profiler sees. In addition to all this info, there is an ETW (event tracing for Windows) provider for SQL Server. ETW support also appeared in System.Data.dll and the SNAC OLE DB provider/ODBC driver in SQL Server 2005/ADO.NET 2.0.

SQL Server 2008 adds support for XEvent (was codenamed Crimson), the new event system in Windows. The BOL provides info on this support, which works by creating and activating EVENT SESSIONs with DDL statements. Event sessions deal with items from event packages: events, targets, actions, types, predicates, and maps. You can mix and match the items from different packages in an EVENT SESSION.

So how do you get started? Create an event session (with CREATE SESSION DDL) and add items from the packages to your session (either in CREATE SESSION or in ALTER SESSION). You need at least one event and one target. You start/stop collecting by using ALTER EVENT SESSION...STATE=START/STOP.

- SQL Server 2008 ships with three packages: sqlserver, sqlos, and package0.
- There are lots of events. You can find them in sys.dm_xe_objects where object_type = 'event'.
- There are four targets, three of which work in the July CTP. The ones that work are:
    package0.asynchronous_bucketizer
    package0.pair_matching
    package0.etw_classic_sync_target

The first two targets write their info to sys.dm_xe_session_targets. You can join this to sys.dm_xe_sessions (after starting a session and collecting events) and look around. The fields in these DMVs are doc'd in BOL. Start with sys.dm_xe_session_targets.target_data.

The third target writes to an ETW session/file, providing compatibility with ETW. More on this one next.