![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
johnpalmer(This is pure tech blogging. No need to read it if you're not tech-inclined. But if you do read it, and think you would understand it *if* you understood the references, I'd love to know that.)
Okay, if you've had your ear to the ground since SQL 2008, you knew that XEvents were A New Big Thing.
And you'd try to learn about them, and, if you were like me, you walked away stunned and confused. Here's an attempt to dispel some of the confusion.
XEvents are a way of tracking stuff that happens in SQL Server. You can track stuff that you can't track in a Trace, and XEvents are more efficient than a trace. In fact, SQL Profiler is now deprecated, as of SQL 2012 - it's going away after this version! - so you need to understand XEvents if you want to be able to dig into SQL Server's "guts", because Traces are going bye-bye.
I've been confused for a long time about XEvents, and I think a large part of the problem is the naming convention, and the coding conventions, of XEvents.
Here's a link to a sample XEvent collection:
http://blogs.msdn.com/b/grahamk/archive/2009/09/23/realistic-troubleshooting-example-of-extended-events-xevent-usage-in-sql-server-2008-part-1.aspx
Before we look at that sample, let's go over what I've learned.
An "Event Session" is similar to a Trace definition. So to collect information from XEvents, you need an event session.
Once you have an Event Session, you'll add "Events" to it. Well, you're not exactly adding "events" - what you're doing is adding event *listeners*... things that listen for a particular Event, and do something when the event occurs. You can also add a "predicate" - a WHERE clause - so the event (listener) will only do something if the correct conditions are met.
In the Event (listener), you'll add "actions". This can be confusing because an Action named Session_ID is actually a command to "collect the relevant session ID". (Any MS Developers want to tell me why Get_Session_ID was discarded, in lieu of just Session_ID? Nouns can be verbed, but they shouldn't be unless no obvious verbs exist. Don't "gift" something, *give* the gift!) So, key takeaway: if it sounds like a noun, the real action is probably "grab this".
Finally, an Event Session will have a Target. This is obvious enough; "where do we send the data we collect?"
With that in mind, let's look at Graham Kent's XEvent script. In SQL, you can include comments by using the --, or by enclosing it in /* and */. The comments that are in the /* */ are mine, not Mr. Kent's. I'll also include "JP:" to indicate that I'm the one writing.
-- XEvent Session Definition Arithmetic error, Log additional info
-- Sample Query: select convert(bigint,21568194 * 100)
if exists(select * from sys.server_event_sessions where name='ArithmeticError')
DROP EVENT SESSION ArithmeticError ON SERVER
go /* JP: "If this already exists, get rid of it. Now we start with a clean slate" */
CREATE EVENT SESSION ArithmeticError ON SERVER
/* JP: so, we're creating that Event Session I mentioned earlier. */
ADD EVENT sqlos.exception_ring_buffer_recorded (
/* JP: When SQLOS has an Exception_ring_buffer_recorded" event, it will tell us, and we'll take some action... */
ACTION (sqlserver.tsql_stack, sqlserver.sql_text)
/* JP: we'll get the TSQL_Stack and the SQL_text */
WHERE (error = 8115 ))
/* JP: but *only* if the exception_ring_buffer_recorded's error is 8115. Otherwise, we do nothing. */
/*
JP: Note something really important: we added that Event to our Event Session ArithmeticError, but nothing in the code above tells us that. So, you create an Event Session, and then immediately start adding Events; SQL knows to add them to the Event Session you just created.
*/
ADD TARGET package0.ring_buffer,
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\temp\ArithmeticErrors.xet', metadatafile='c:\temp\ArithmeticErrors.xem')
/* JP: now, we've added two targets: a "ring_buffer", whatever that is, and an Asynchronous_File_Target - ah! a file, with a file name, and a separate file for metadata. */
/* JP: The rest of these are settings for the Event Session. They're very important, but here is where my confusion ended on XEvents. Now I understood enough to follow along with what's happening. This Event Session is going to listen for a certain error, and when the error occurs, the event session will capture the SQL Event Stack and the SQL Text, storing them in a ring buffer and a file. */
WITH (MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 1 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF)
go
/* JP: This is also important: the Event Session isn't active - so the next step is to turn it on - and, later, to turn it off. */
ALTER EVENT SESSION ArithmeticError ON SERVER
state=start
go
(end of code, and a big thank you to Graham Kent.)
This is, of course, just scratching the surface of a massive topic. Next, you want to know what events you can listen for, and what information you might want to gather when those events fire; you want to know how to run this in a way that won't impair performance on your production server, while making sure you still collect the data you need.
That's far more than I could include in a single journal entry or blog post. Heh. Plus, I don't know that stuff yet, either :-). But now, maybe you have a bit of a head start when you want to explore this topic.
Okay, if you've had your ear to the ground since SQL 2008, you knew that XEvents were A New Big Thing.
And you'd try to learn about them, and, if you were like me, you walked away stunned and confused. Here's an attempt to dispel some of the confusion.
XEvents are a way of tracking stuff that happens in SQL Server. You can track stuff that you can't track in a Trace, and XEvents are more efficient than a trace. In fact, SQL Profiler is now deprecated, as of SQL 2012 - it's going away after this version! - so you need to understand XEvents if you want to be able to dig into SQL Server's "guts", because Traces are going bye-bye.
I've been confused for a long time about XEvents, and I think a large part of the problem is the naming convention, and the coding conventions, of XEvents.
Here's a link to a sample XEvent collection:
http://blogs.msdn.com/b/grahamk/archive/2009/09/23/realistic-troubleshooting-example-of-extended-events-xevent-usage-in-sql-server-2008-part-1.aspx
Before we look at that sample, let's go over what I've learned.
An "Event Session" is similar to a Trace definition. So to collect information from XEvents, you need an event session.
Once you have an Event Session, you'll add "Events" to it. Well, you're not exactly adding "events" - what you're doing is adding event *listeners*... things that listen for a particular Event, and do something when the event occurs. You can also add a "predicate" - a WHERE clause - so the event (listener) will only do something if the correct conditions are met.
In the Event (listener), you'll add "actions". This can be confusing because an Action named Session_ID is actually a command to "collect the relevant session ID". (Any MS Developers want to tell me why Get_Session_ID was discarded, in lieu of just Session_ID? Nouns can be verbed, but they shouldn't be unless no obvious verbs exist. Don't "gift" something, *give* the gift!) So, key takeaway: if it sounds like a noun, the real action is probably "grab this".
Finally, an Event Session will have a Target. This is obvious enough; "where do we send the data we collect?"
With that in mind, let's look at Graham Kent's XEvent script. In SQL, you can include comments by using the --, or by enclosing it in /* and */. The comments that are in the /* */ are mine, not Mr. Kent's. I'll also include "JP:" to indicate that I'm the one writing.
-- XEvent Session Definition Arithmetic error, Log additional info
-- Sample Query: select convert(bigint,21568194 * 100)
if exists(select * from sys.server_event_sessions where name='ArithmeticError')
DROP EVENT SESSION ArithmeticError ON SERVER
go /* JP: "If this already exists, get rid of it. Now we start with a clean slate" */
CREATE EVENT SESSION ArithmeticError ON SERVER
/* JP: so, we're creating that Event Session I mentioned earlier. */
ADD EVENT sqlos.exception_ring_buffer_recorded (
/* JP: When SQLOS has an Exception_ring_buffer_recorded" event, it will tell us, and we'll take some action... */
ACTION (sqlserver.tsql_stack, sqlserver.sql_text)
/* JP: we'll get the TSQL_Stack and the SQL_text */
WHERE (error = 8115 ))
/* JP: but *only* if the exception_ring_buffer_recorded's error is 8115. Otherwise, we do nothing. */
/*
JP: Note something really important: we added that Event to our Event Session ArithmeticError, but nothing in the code above tells us that. So, you create an Event Session, and then immediately start adding Events; SQL knows to add them to the Event Session you just created.
*/
ADD TARGET package0.ring_buffer,
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\temp\ArithmeticErrors.xet', metadatafile='c:\temp\ArithmeticErrors.xem')
/* JP: now, we've added two targets: a "ring_buffer", whatever that is, and an Asynchronous_File_Target - ah! a file, with a file name, and a separate file for metadata. */
/* JP: The rest of these are settings for the Event Session. They're very important, but here is where my confusion ended on XEvents. Now I understood enough to follow along with what's happening. This Event Session is going to listen for a certain error, and when the error occurs, the event session will capture the SQL Event Stack and the SQL Text, storing them in a ring buffer and a file. */
WITH (MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 1 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF)
go
/* JP: This is also important: the Event Session isn't active - so the next step is to turn it on - and, later, to turn it off. */
ALTER EVENT SESSION ArithmeticError ON SERVER
state=start
go
(end of code, and a big thank you to Graham Kent.)
This is, of course, just scratching the surface of a massive topic. Next, you want to know what events you can listen for, and what information you might want to gather when those events fire; you want to know how to run this in a way that won't impair performance on your production server, while making sure you still collect the data you need.
That's far more than I could include in a single journal entry or blog post. Heh. Plus, I don't know that stuff yet, either :-). But now, maybe you have a bit of a head start when you want to explore this topic.
