Argentina SQL Saturday
I was in Argentina for the SQLSaturday event earlier this year and made friends with Javier Villegas a 20 year veteran in the DBA world. His session that day touched on extended events and while it is a bit of an advanced topic, I thought we could sit down and discuss it. I also include a few sounds I recorded from my trip.
Show Notes
Javier on Twitter
Javier’s Blog
2008 GUI for Extended Events
Books on Line: Extended Events
SMS toolpack
Carlos Chacon: Hello there, compañeros, and welcome to episode two of the “SQL Data Partners” podcast. I am Carlos Chacon and I want to welcome you to the show. We’re going to hit some highlights on Extended Events. Now, today’s show has been a long time in the making. Some of you may know that I spent some time in Argentina as a missionary in the late ’90s for my church.
When Mariano Kovo and the Argentina team said they were going to host the first “SQL Saturday” in the country this last May, I had to be a part of it. Almost 20 years after I’d been there the first time, I went down and participated in the SQL Saturday. It was a great event. While I was down there, I made a new friend in Javier Villegas, who has been a DBA for 20 years and he agreed to come talk with me, so I’m super appreciative of his time.
Before we get started, there are a few sounds I want to share with you just really quickly from Argentina. As always, I hope you enjoy the episode. If you like what you hear, please rate our program and give us a review. If you want to hear something on our show, you can always hit me up at [email protected] or on Twitter @carloslchacon.
Children: SQL Data Partners.
[music]
Carlos: Javier, welcome.
Javier Villegas: Thank you. How are you doing?
Carlos: I am doing great and I appreciate you asking. It’s been great to be here in Buenos Aires. Now you’ve been here for some time and I know that you’ve been a DBA for the past 15 almost 20 years now.
Javier: Almost 20.
Carlos: Let’s see, who are you working for now?
Javier: Right now, I’m working for Mediterranean Shipping Company, the container company. We ship containers all around the world.
Carlos: Buenos Aires has a large port. Very important port to a lot of that shipping. You mentioned earlier, you’ve been a DBA since 6.5, right, and very involved in the SQL Server world. Today you gave a session on monitoring and one of the things was “Extended Events.” That’s actually the focus of our podcast today is Extended Events.Help me understand, what are Extended Events and what are we using them for?
Javier: Extended Events is a lightweight mechanism to get feedback on events and information from the SQL instance plus the operating system and the whole environment. It’s so easy to get the vital signs of our environment with Extended Event.
Carlos: You mentioned the events and some of that information. How is that different than the counters at PerfMon? Why would I choose Extended Events over PerfMon?
Javier: There are kind of different things. If you want to compare Extended Events with the tool, you have to take profiler because it’s deeper into SQL and it allows you to get the whole activity that you have in SQL. The good thing is that you can correlate some of the counter and the standard you get through Extended Event with the OS events. Probably you can match the information that you get through Extended Event with performer monitor and the counters.
Carlos: Would it be fair to say that the benefits of the Extended Events is that I can see both the query or the action inside of SQL Server, with some of the corresponding performance monitors like CPU or Disk or Memory?
Javier: Among other things. With Extended Event, you can get deadlock information. You can get SQL errors, you can track for long running statement or queries, while I mention errors. Whatever you can do with profiling or whatever you want to trace audit information that log-in log-out audit failed, et cetera, you can get it through Extended Event.
Carlos: You’ve mentioned the SQL Server profiler, why would I choose to use Extended Events when I have a profiler? I know how to use that. I open it up, I use my template, I click go and away I go.
Javier: One of the uses of profiling is that when you are on a difficult situation and a performer problem, you need to start doing something, and most DBAs the first thing they do is they start profiler. If you don’t know well how to use it or for some mistake you don’t set the fielders in the right way, you are going to be introducing additional problems to the one that you’re trying to solve.
Carlos: That idea of the watcher effect. While we are trying to monitor we’re actually causing more load on the system.
Javier: The benefit of the Extended Event is that it’s very lightweight mechanism. It’s running in the background and you can consume the output quite easily without affecting the performance.
Carlos: We talked about using Profiler, just to go back for a moment. For those of you who are still using Profiler, we hope that you’re using it in a server-side trace. If you’re just clicking “run” after you see the template, that watcher effect will be compounded. There’s a mechanism called server-side tracing, which may not be quite as lightweight as Extended Event, but definitely a lighter footprint than just running from the plain template.We’ve collected some of this information, and I know even for myself getting into Extended Events, there’s been a little bit of a learning curve. Take us through some of the concepts that we might need to know in order to become more familiar with an Extended Event or how to set up that capture of the Extended Events.
Javier: First of all, you have to define your Extended Event session. Within the definition, you have to specify what package or what kind of events you want to track. You have to specify which information you want to retrieve, like in Profiler when you select the columns that you want to get.Finally, the predicate. If you want to specify a particular filter, for example, if you’re capturing SQL errors, and you want to filter some error numbers, you have to specify it through this predicate. Finally, you have to define the target. Where you are going to store the Extended Event.
Carlos: In order to set that up, I have to define a section. I define the events that I want to capture in the session, I put some filters on that to only give me those, under certain conditions that I want information back, and then where I have to save the output from that. I know some part of the learning curve was that was only available via TSQL.
Javier: Yes.
Carlos: Is there now GUI interfaces to allow me to do Extended Events?
Javier: The first version which was back in SQL 2008, it was a hundred percent TSQL, no user interface at all. I believe there were some third party flaggings but…
Carlos: I think Jonathan Kehayias and Company actually put together a little GUI interface.
Javier: Which was actually good, but then back in SQL 2012, and now with SQL 2014, it is introduced within management studio. You have under management Extended Event, you can create the session, define your filters, the output, et cetera, through this user interface.Then you can use the same mechanism, through management studio, to consume the output. You can watch the data live as it is happening, and also you can go back and see the history and you can do your analysis with them.
Carlos: Are there standard templates you can use in the GUI?
Javier: You have your own user-defined template.
Carlos: Normally you use your own user-defined template?
Javier: Yes.
Carlos: I believe my experience there was I was using a certain template and it wasn’t the default template, like it wasn’t Profiler, but there is a general system performance template.
Javier: Correct.
Carlos: You have to go in and choose that one.
Javier: Yes. Choose it and do your own customization.
Carlos: Is it fair to say that once I set up these events, I’ve set up my WHERE clause or my filters if you will, for those, that once I start collecting that data, how often do I collect that and is this something that you’d look at every day? Under certain scenarios, or when are you most using this?
Javier: In my case, I use it to track. Two of the examples that I have is deadlocks. I have an Extended Events session just for deadlocks, so it’s running 24 hours a day. In addition to that session I have a SQLAction alert which get fired through the deadlock event, which execute a SQL shop, which executes a use of procedure by which read the information from the Extended Event, naturally, from the file.All the information is stored into XML format, so what these procedures does create a user friendly output and send it to the DBA through email.
Carlos: So that agent portion and then getting that email, that’s not part of Extended Events?
Javier: That is not part of Extended Events.[crosstalk]
Carlos: …if you have to customize that…
Javier: Correct, yes.
Carlos: You could open the XML in the GUI window.[crosstalk]
Javier: Yes, it’s a standard XML so you can open it and analyze what’s going on. In this case it deadlocks, you can see the deadlock victim, the killer, the results that’s involved within deadlock, et cetera. So you have all the information you need to troubleshoot that particular event in one output.The other case that I have is a user defined Extended Events session to track SQL errors. In my case, when we upgrade the application that we have in our company, it’s where I take most of…When I use this Extended Event session the most, why? If there is any SQL error that is happening right away after the upgrade, I can easily catch it, go through the proper marginal developer to take a connection.
Again, the Extended Events session can already open all the errors that are happening live. In parallel, I check that output and if I can see an error that is a hard one, or an important one, I take it and go to the proper one.
Carlos: I know sometimes chaining that together, particularly if you work very good on your filters, chaining the different events to a certain user experience, for example, was somewhat difficult. Is the filtering in Extended Events better than Profiling?
Javier: It’s different. Profiler is a quite mature tool. The good thing is that you have a lot of help. Whatever you were doing through Profiler, whatever you are checking to select, you may have a little help telling you what you are doing.In Profiler, let’s say that, if you are very, very, very good at Profiler you will find these filters quite easy to work with. Otherwise, you may have to read a little bit, but when you get used to work with the Extended Event, probably you will never use Profiler again.
Carlos: I was referring more to, once I have my output and let’s say I was just doing a system profile and it was generating lots of different users. Maybe there were even multiple databases that were called during that time. I’m only really interested in one database, or maybe even a specific user. Is it easier to filter that information?
Javier: Extremely easy. It’s just modifying the work tools. Like a simple select statement like you say were, “Log in, name, equal your user.” It’s quite easy.
Carlos: We’ve gone through that process, there is a little bit of a GUI. How would you recommend — for someone who’s not currently using Extended Events — they begin?
Javier: First of all, as I always recommend the books online. Books online is where you’re going to get all the information you need starting from the very basics, through complex cases, and through cases that you can even use in your production environment, probably with some little tuning, or assessment for your own environment. Books online is the best source for information.
Carlos: Do you have any blogs that you follow or folks in the community that have talked about Extended Events that you have found helpful?
Javier: Yes, there are some. I have my own blog. It’s JavierVillegas-DBA.com. You’re going to find some information in there, plus some examples of Extended Events session, the one that we were talking today here at the SQL Saturday.
Carlos: Thanks. I think that’s been a nice overview information. Obviously with any tool, we need to get in there, start playing with it, and then once we see some examples, then it will be easier to make the logical conclusions to the next step.We do appreciate that information, and so now we’re to some of the favorite things and then we’ll get to your DBA warrior story here in just a minute. Tell us, what is your favorite SQL tool? This tool can be either paid or for free, but tell us a little bit about it, whether it costs money, and how you use it.
Javier: First of all, my favorite tool overall is SQL Server Management Studio.[laughter]
Carlos: Which we all know and love.
Javier: Yeah. There is a plugin which is called SMS tool pack, which is a fave. Flagging, which makes the lines a little bit easier to the DBA because you can specify connection callers for your different servers. You can have shortcuts to your script, some pre-defined templates, something that you can use as an addition to the plan analyzer to get the deeper information. It’s a nice tool.
Carlos: It’s an administration, techniques, plus, OK.
Javier: It’s an administration, plus developer. It’s something that’s trying to fill those small holes that Management Studio has, and it’s a great tool.
Carlos: As a DBA, what’s your favorite thing to do in your role as a DBA?
Javier: My favorite things at DBA are performance tuning and disaster recovery. In terms of performance tuning, I love when users, developers, managers come to me saying I have this particular problem. This statement, this procedure, this application, that is not running as I expect. So I need to make it better.I love those challenges to get that piece of code and to make it better. To run with less resources and what everybody wants, running faster.
Carlos: You get to be the hero.[laughter]
Carlos: Javier made the database faster!
Javier: I really love that. The whole DBA administrations thing is right.
Carlos: I actually took two classes in college — DBA related classes — and both classes I hated and I swore I would never be a DBA. I was going to be a network engineer, that’s what I wanted to do and a funny thing happened and here I am as a DBA. I’ve enjoyed it, too.
Javier: Let me add something to that. In my early [inaudible 19:58] days, I started with the Microsoft certification process back in Windows NT 4 or NT 3.551 something and at the time you have the basic courses plus elective one.[crosstalk]
Javier: One was Exchanged and the other one was, I believe, Active Directory and there was SQL. I believe that I choose SQL because the other ones were full or something like that. It was like five days and at the second day, I say, “I will never work as a DBA.”[laughter]
Carlos: Never say never.
Javier: Never say never. Now after 20 years… [laughs] I remember that day.
Carlos: Yeah, I think that was my experience as well and hopefully through things like this podcast and some of the videos we have a great SQL server community that it will help lessen the blow, if you will, the learning curve in order to be a DBA because it is a very neat thing.The last piece that is your database administrator warrior story. This is something that has happened to you that…you get to be the hero. Something that was very difficult. I’d be interested to hear your story.
Javier: As I mentioned before, I love the high availability and the disaster recovery techniques. My company, we have many agencies with AlwaysOn and also that is mirroring a few years back.We had a couple of agencies, which suffer major earthquakes like in Chile and New Zealand. The good thing is that the disaster recovery solution configured and implemented there allowed us to have an operation quite good. We didn’t lose any piece of data because we have a remote data center in where we were hosting the replica, the secondary.
Carlos: Both of those earthquakes were very, very large, lots of devastation. I can only imagine right on top of that is to lose that data, lose that information. We’re not talking about people’s jobs that are impacted. They get their lives back in order, their homes back in order and then they have to get back to work and put all those pieces back together again.Being able to have that high availability available so that once they are ready to come back, it’s there, they can hop right in and just continue working. I can imagine that that’s pretty neat, that that was able to work.
Thank you for sharing that story.
Javier: Sure.
Carlos: Again, thanks for being here on the podcast, it’s been great talking with you.Before I let you go, I wanted to give you another way that you can learn about SQL server.
Hello there, compañeros, I want to tell you about a unique SQL Server training opportunity that is unlike anything you’ve encountered and as a listener of this podcast, you’re entitled to a special offer. SQL Cruise is a premier training experience set in the most unlikely of places, a cruise ship. Tim and Amy Ford have created a wonderful culture on SQL Cruise.
With five or six technical leads from various industry sectors, you and roughly 20 other students will talk shop in classes while at sea and while you are in port, you can enjoy the wonderful world we live in, either by yourself or with the group, the beauty of SQL Cruise is the unique relationships you will form with other SQL cruisers.
Worried about leaving your spouse? Bring them along. I did and she had a great time with the group. In fact, I have been on SQL Cruise twice and I’m looking to go a third time. You may be wondering if this is serious training and I can assure you it is as good as any conference you will attend on land.
It never crossed my mind that I could be talking about SQL Server with several Microsoft MVPs on the beaches of St Johns. I know you must have other questions so I suggest you head over to SQLCruise.com and check out the site.
Once you are satisfied and you want to sign up, when you get to the question, would you be so kind to share the name of who referred you and what source material lead you to us. You enter SQL Data Partners and Tim will ensure you get $100 off the training registration.
This is a great offer and I hope this $100 savings will entice you to check out the site. I did and went on the Caribbean cruise and had a great experience and I know you will too. Maybe I’ll see you on board. So head on over to SQLCruise.com and use the code SQL Data Partners to save $100.
[music]
Children: SQL Data Partners.