Secrets, hints, and tips for SQL Server Extended Events

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 thought on “Secrets, hints, and tips for SQL Server Extended Events

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.