\nI'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?\n<\/p>\n
\nBefore 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:\n<\/p>\n
\ncreate event session errorsession on server
\nadd event sqlserver.error_reported
\n(
\naction
\n(
\nsqlserver.session_id,
\nsqlserver.sql_text
\n)
\nwhere sqlserver.error_reported.error = 547
\nand package0.counter <= 3
\n)
\nadd target package0.ring_buffer
\ngo\n<\/p>\n
\nAbout one third of the time, running this DDL would produce:\n<\/p>\n
\nMsg 25706, Level 16, State 8, Line 1
\nThe event attribute or predicate source, "sqlserver.error_reported.error", could not be found.\n<\/p>\n
\nThen, I'd move the DDL code to another window, execute it again, and it would work. Sometimes starting up the session:\n<\/p>\n
\nalter event session errorsession on server state=start\n<\/p>\n
\nwould 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…\n<\/p>\n
\nAfter looking at the definition of the system_health session, I decided to try a one-part attribute name. Instead of:\n<\/p>\n
\n…where sqlserver.error_reported.error = 547 and…
\n— how about
\n…where error = 547 and…\n<\/p>\n
\nBingo! 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.\n<\/p>\n
\nBTW, a few other things to remember to save you some head-scratching.\n<\/p>\n
\nWhen 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.\n<\/p>\n
\nWhen 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).\n<\/p>\n
\nUse an asynchronous target with default event latency rather than a synchronous target, for less overhead, better throughput. However the ETW target is synchronous only.\n<\/p>\n
\nAll 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.\n<\/p>\n
\nAll that said, Happy extended eventing!<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/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-668","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2008"],"yoast_head":"\n