The third part of the invitation was to write this. I covered what I did, and some of what I learned. I’ll add a bit more here.
I certainly was clumsy working with XE, and despite working my way through the course, I realize I have a lot of learning to do in order to become more familiar with how to use XE. While I got a basic session going, depending on when I started it and what I was experimenting with, I sometimes found myself with events that never went away, such as a commit or rollback with no corresponding opening transaction.
This T-SQL Tuesday was a bit broader in scope, so it has been interesting watching people respond.
So how do we handle the scenario where the server is rebooted?
- Option 1: always remember to restart the trace after server reboots
- Option 2: create a SQL Agent job to poll for the SSAS service status and start the xEvent trace if its not already running
- Option 3: write a custom .NET watchdog service to poll for the SSAS service status and start the xEvents trace if its not already running
Those are the options I’ve used or seen used in the past… and to be sure, all of them have their drawbacks in reliability and/or complexity.
…which is why I was so excited when it was brought to my attention that there is an “AutoRestart” option for SSAS xEvents!
Do read the whole thing.
I have to be a little honest here. Prior to somebody asking how they could possibly achieve a statement audit via extended events, I had not considered it as a tool for the job. I would have relied on Audit (which is Extended Event related), or some home grown set of triggers. In this particular request, Audit was not fulfilling the want and custom triggers was not an option. Another option might have included the purchase of third party software but there are times when budget does not allow for nice expensive shiny software.
So, with a little prodding, I hopped into the metadata and poked around a bit to see what I could come up with to achieve this low-budget audit solution.
Read the whole thing.
In the following script, I have tried to accomplish just that – a single script to create the entire XML parser for me, for you, and for anybody wishing to use it. I don’t want to have to remember the subtle nuances of how to parse each of the events each time I need to parse the session data. I want something that is quick, easy, and repeatable.
With all of that said, here is the script that I now use to parse my session data. You should notice that it has been simplified and is more extensive now.
Jason also has sample usage. Check this out for sure.
Jason Brimhall has two recent blog posts on figuring out Extended Events information. First is a republication of an older article:
First let’s tackle the problem of discovery. When we want to use extended events to try and troubleshoot a problem or to capture more information, it is really good to know if such an event exists. There are many events that capture data for various different things within SQL Server. More and more events are being added with each release. More and more data is being made available to the DBA to help perform a better job and to help the DBA better understand what is really happening within the database environment.
In order to determine if there might be an event, that can provide the data for that one “thing” that may be happening within your environment, we could start by querying the SQL Server Internals. This next query will do just that for us.
After you read that article and check out the queries there, Jason has another post on finding the right event:
In my previous article I demonstrated how to find an event based solely on the name or description of the event. This is fantastic if the event name (or description) contains one of the magical words you have used. What if the event name or description has nothing to do with the terms you selected? Or, what if the data you seek may be attached to the event but wouldn’t necessarily stand out as a description for that event (by name or description details for that event)?
Now comes the more difficult task right? If the name or description of the event doesn’t relate to the search terms then you just might overlook a few events and be stuck trying to troubleshoot a problem. An equally big problem this could cause is yet another invisible barrier to using Extended Events. It would be easy to slide down the slippery slope and not transition to Extended Events just because an event, applicable to the problem at hand, could not be found.
Check out both of these posts.
Three conditions can trigger this warning to show up in showplan:
Excessive Grant: when max used memory is too small compared to the granted memory. This scenario can cause blocking and less efficient usage when large grants exist and a fraction of that memory was used.
Used More Than Granted: when the max used memory exceeds the granted memory. This scenario can cause OOM conditions on the server.
Grant Increase: when the dynamic grant starts to increase too much, based on the ratio between the max used memory and initial request memory. This scenario can cause server instability and unpredictable workload performance.
I like that this information also shows up when you view an execution plan using SSMS 2014 SP2.
Every time I talk about migrating from Profiler and Trace to Extended Events (XE), I seem to add something to my presentation. It’s not always intentional, and I don’t know if it’s because I just can’t stop tweaking my demos and contents, or something else, but here in London at IEPTO2 this week, I added a query that helps you see what event in Extended Events is comparable to the event you’re used to using in Trace/Profiler. While most of the events in XE have a name similar to the event in Trace (e.g. sp_statement_completed for SP:StmtCompleted), some mappings aren’t so intuitive. For example, SP:Starting in Trace is module_start in XE, and SP:Completed in Trace is module_end in XE. That’s not so bad, but if you’re ever monitoring file growths, note that the database_file_size_change event in XE is the event for the following four events in trace: Data File Auto Grow, Data File Auto Shrink, Log File Auto Grow, and Log File Auto Shrink.
This is a helpful query to keep around until you get really familiar with Extended Events.
…but what does this have to do with Extended Events?
As I started out, I was looking for something slick I do with a trace that I could replicate in Extended Events, and this was a great candidate.
The catch as I discovered, is that while file growths are captured in the default trace, they are *not* in the system health session…
Fortunately, you can create your own XEvent and Andy gives us the details.
I haven’t gotten a ton of comments, but I did get a few (thank you to those have responded!), and I decided to take one of them and create a Trace and create an Extended Events session and see how long it took for each. Jonathan has mentioned before that he can create an XE session as fast as a Trace, and I’ve been thinking that I can as well so I thought I’d test it. It’s a straight-forward Trace versus Extended Events test. Want to see what’s faster? Watch the video here.
I love the “I would pop up the timer on the screen but I don’t know how to do that” bit; very Friday afternoonish.
Back in April I wrote a post asking why people tend to avoid Extended Events. Many of you provided feedback, which I greatly appreciated. A common theme in the responses was time. Many people found that creating an event session in Extended Events that was comparable to one they would create in Trace took longer. Ok, I get that…so as a follow up I am interested in knowing how you typically use Profiler and Trace. If you would be willing to share that information, I would be extremely grateful.
Head over there and let her know. For science!