Voice of the DBA

Tag: extended events

  • Extended Event Comfort

    I saw an interesting thread recently in the SQL Server Community Slack where someone posted about extended events (XE). They were asking about whether XE would have a problem with a situation. The problem wasn’t so interesting, but a quote from one of the responders was. The quote was:

    The best time to have learned Extended Events was ten years ago. The second best time is today.

    I love that, and I tend to agree. If you need to trace what is happening inside your SQL Server, you need to learn how to capture information with Extended Events. That’s the best way to dig into the details of how queries affect your system.

    It’s also hard. I know that whenever I need to use it, which is rare, I have to dig through some articles and docs to understand what thing I need to do. Even having some scripts hasn’t helped because it’s a sufficiently complex system that unless I use it regularly, I forget how all the filters, targets, events, etc. work.

    On one hand, I think it’s amazing, and on the other, it’s too hard to use. Even when I try the Extended Events profiler, it’s so different from Profiler that I find myself getting frustrated at times trying to dig through the information.

    I am curious how many of you think XE is easy to configure and if you use it often. What are the places it works well? For those of you that don’t use XE or haven’t learned, why not? Do you not have to trace what’s happening with queries in some detail? Or do you have another way that you dive deep into your system? Or do you not have the need?

    If you do want to learn more, we have a short Stairway Series on Extended Events to help you get started, as well as a few other articles. If you’re an expert, we’d love a few more on using XE in specific situations.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Learning Goals–May Update

    It’s 5 months in, and this is hard. I find that the time I’ve picked each week sometimes gets bumped by other items, which do have a higher priority. More on this later.

    Extended Events

    An epic fail here for May. I started with one week and managed to get an hour watching part of a PASS session and building a few questions. However, the second week I got busy with some family stuff that impacted my work week. With deadlines and travel the third week, I skipped this.

    The third week I was gone, and the fourth, well, only a little focus, though I did start to write a short piece on using Jon Kehayias’ script to convert a trace to an XE session. We’ll see if I get this done.

    Fifth week, not sure. That Tuesday will be in the June update.

    Python

    Not a lot better than the XE stuff. Week one I had some family issues get in the way of my Friday learning and ended up skipping this as I was away from home. With a busy weekend, I didn’t make this up.

    Week 2 I was prepping for week 3. Week 3 I was gone.

    This brings me to week 4. While I like to try and do something, I really didn’t get much done during the week. I am looking to add Python questions to the mix on SSC, so I have taken a little time during the week to build a couple questions. Going through basics like lists and dictionaries helps me come up with things that I think people might not know and can get inspired to learn a little with a question. That helped me get a few things done this month.

    I did take time to go through part of a Pluralsight course in week 4, about 90 minutes of total time, some of that pausing the course so I could generate a question and practice some Python skills.

    I keep a few browser tabs open with Python links to try some problem solving, but I keep not making time.

    Moving Forward

    I didn’t travel much until mid May, but I had a very busy life outside of work and didn’t make much time to learn away from work. Fortunately my work is related to these topics, so I can take a little time for learning.

    As I close out the first half of the year, I’m somewhat disappointed with progress. I definitely continue to learn, as my job requires it, but I’m not getting the focused time in these areas. Looking forward to next year, I want to aim for topics that are more tightly integrated with work so that I get more practice. Here, even though I’m doing something, I’m not as focused.

    For June and further, I’m going to shift my times around and pick what I think are quieter times for me to get something done. Since I can ride a bike at home and watch Pluralsight, I’ll pick those times for at least passive learning. Then I’ll set aside a shorter time period, 30 minutes, for practice at other points in the week when I have found myself with time.

  • Learning Goals–April Update

    Here is the update: I did better.

    Extended Events

    I did a little more multi-tasking this month, ensuring that if I rode the stationary bike for exercise, I had my tablet and watched some Pluralsight or read some content.

    Here I learned a few things:

    Predicates

    These short circuit, and this means that we want to write our predicates to end early if possible. We also want to write our predicates to look at local data before global data so that we can stop event code as early as possible.

    There is also a 3000 byte limit, so be careful with structuring the event predicate statement.

    I also found that u_tables.sql lets me rebuilt the default sessions for an instance. It’s in the install folder. That’s handy as there are times that I’ve removed the default sessions or changed them and I’d like to get back to the beginning.

    Event Sessions

    The structure for event sessions is like DDL for other objects. These are always created with events, but we can remove all events. I’m not sure why you want the placeholder, but it’s an interesting anomaly. I suspect that this is a hole in the DDL parser.

    I also learned you can drop sessions without stopping them. Convenient? Sure. However, this means that I could make a mistake and not get a question about dropping a session that I, or someone else, is using.

    Changing event or target configuration means dropping and adding back the item. That’s good to know. No way to alter an event predicate.

    Python

    I made it a point to do some coding every week, and work on Python. I did some fundamentals, ensuring I understood how modules work and are imported. I spent some time on variables and mutable/immutable items. I wanted to be sure I understood how these worked compared to other variables. It’s good to know that some of the assignments don’t create a new variable, but merely set a pointed to the existing item.

    I set up a Jupyter notebook in Azure, which was interesting. I need to use this more, and perhaps do some coding here on the Advent of Code or other projects. I solved a couple more days of puzzles, which was fun. These are challenging, and it took a little time to work through items.

    I didn’t do this every week, as I had some travel this month, but I did make an effort to work in python a bit more.

  • Learning Goals 2018–February Review

    Better than month 1. Only XE this month.

    My first week I got a reminder to spend some time on XE, so I watched a couple modules of Jonathan Kehayias’s Pluralsight course.  A lot of review, but I thought I’d start with the basics and Jon knows more about this than anyone I know.

    I spent a few days going through this. Some at my desk, but more while riding a stationary bike. This gave me the chance to actually sit and watch without being distracted by other items on the computer.

    I got through about a third of the course, but I still have more to watch. I also took about an hour in two sessions to practice a few of the items, explore how XE is setup and build some questions for SQLServerCentral. This allowed me to feel good about the process.

    Disruptions

    The first couple weeks of February were good here. Unfortunately things didn’t continue. I had travel the last week of Feb and a number of meetings that required prep and I got off track in working through XE.

    Even today, as Mar starts, I have missed an hour or learning time.

    On the plus side, I did take 3 days of vacation in Feb, which took away work and learning time, but allowed me to recharge a bit. I’ll continue that a bit in March, so it’s possible that I might not quite get what I want here, especially as I have a number of weekend commitments this month.

    This is a good lesson, allowing me to realize that I can’t count on adding to my busy schedule without having to make hard choices. Learning has been one of those lower priority items, though I am trying to continue growing in this area.