XEvents

You may be familiar with extended events in SQL Server, but in Analysis Services it's actually been there since SQL 2012 and we're going to take a look at what's coming with SQL 2016. Today's Tuesday, so we're going to take a look at a technical item and t You may have played around with extended events in SQL Server and you may know a little bit or have played a little bit with extended events in Analysis Services, but what's made it really hard is that you need to know how to script it properly and so we're going to take a look at what's actually coming with SQL Server 2016 and these are items that you can go play with today in the 2. 3 CTP. So we're starting off in Management Studio and we're looking at a tabular instance that I'm connected to. T So if we go connect to our 2014 instance, you'll notice management isn'there. So let's go ahead and get rid of that. If we expand management, you'll see extended events and if we expand that you'll see sessions. So wit So let's just call t Currently right now there's in the build that I'm running there's no templates listed here. There will be templates that are available. It's just in the one that I'm running. It's not there. So you may have that option. What we can do regardless of the templates, we can go to events and in here, if you've used extended events in SQL Server, t So if we do login, it'll So if I do audit, you can see that these are all the audit events that are available to us. The other t So we've got a profiler category and then there's a pure X Events category, w So the profiler ones are an actual match to the profiler events that you can get with a profiler trace. So if you're looking to just get a direct comparison between X Event and profiler trace, go ahead and select those profiler events that you would want or that you would select wit In t One t So these are fields that are available to every event that we can include such as call stack. So let me go ahead and click call stack and you'll see here when I did that, it was for that given event. So t So if I go to audit login, I can say call stack on that as well. And then on the event field, you'll see the actual fields that are part of that event that you have selected and those will change as you select those different events. Now that'selected, we can go to data storage. So data storage is all about the targets that we can output the data to. And from the GUI interface, we've only got three items available to us. We've got event file, event stream and ring buffer. So an event file is an XEL file that will be placed on your hard disk or wherever you point it to. The event stream is if you want to do a live stream of the data. And then the ring buffer is we will maintain that data in a ring buffer that's in memory for the process instead of outputting it to a file. And the t So if you restart the AS service itself or the mac It's not persisted at all. So let's go and choose event file. What you'll see here is that we've got an option to say, where do we want to place that file? We can give it the maxim We can enable file rollover so it'll keep going instead of just staying in that same 4 gig file. It'll actually go and create new files as it grows bigger. And then you can cap it to say So the default here is 10. So that would be 40 gigs worth of data. So you'll have to look at how much hard drive space you have on your system to gauge what's right for you. So I'll go and leave the defaults here. Another t So I can have the event file as well as ring buffer or live stream. And then on the advanced tab, t From a buffering perspective, the first t So that'somet In general, X event is pretty lightweight as compared to a profiler trace. You should do some test runs to see how that actually plays out on your end. So the next item on the list is dispatch latency. And dispatch latency is all about how long do we want to wait before serving that event from the buffer to the targets for processing. The next items are all about the memory size for the buffers themselves. And so the larger the buffers, the longer it will before we dispatch that to the target. And the last one is the memory partitioning. So this is all about that we want to have memory per CPU or per N Before going You can just go and So you can then play with the events, the actions, and the targets. One t And one t So it'still CTP, so I'm gonna chalk it up to that. Keep an eye on it as we go into later builds to see if that works better for you. Let's go ahead and run that and it'll give us a return message. It'll give you an error if an error occurred and then we can right click on sessions. Take a look and we see our extended event session there. And if we go look at the log file, we can actually see that our session is there. Alright, so to stop the trace in order to delete the trace you have to delete the trace. So let's go and right click and delete. T So we can see that file is still there. And one t So if we've got a file open, a couple t So you can open file to open the individual file or if you have multiple XEL files, so in the case that you did the rollover, you can actually say merge extended events files and you can So I just have a single file. So we'll go to log extended events. And in here you'll see our audit login logouts. And from here we can go and add other col So we can go in and choose col So we can go in and say go and grab username. We can grab domain. Okay, and then add those in there and then we can correlate and do all sorts of t And I'm gonna be posting a blog post on t It should be out on the AS product teams blog here shortly and that'll have some sample scripts in there as well that you can use as examples. I'll have that link in the description below once it's available. Alright, hopefully that gave you a good taste for extended events and analysis services with what's coming in SQL Server 2016. You can go play with that today in CTP 2. 3. And I'd love to hear your comments. Is t Go ahead and leave your comments down below and let me know what you thought. If t Every Tuesday I do a technical item such as t And every Thursday I do an information roundup where I take a look at the last week and find the items that I found most interesting and share that out with you. And really t I want to help you be more successful in what you do by getting as much out there as possible. So go ahead and subscribe and be part of the conversation.