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.