Waiting, is it a Bad Thing?

One of the more useful troubleshooting tools (granted when used properly and not with a knee jerk approach) is waits. There are waits in SQL Server that are very specific to Extended Events.

stockinghatDespite the desire to get away from the GUI talk in these articles about Extended Events, I have so far been unable to do it. Each article of late has something more to deal with the user interface. Let’s see what we can do with the GUI today.

One of the more useful troubleshooting tools (granted when used properly and not with a knee jerk approach) is waits. There are waits in SQL Server that are very specific to Extended Events. Not all waits are bad. Some are innocuous. But with a shoot from the hip approach, these waits can cause many DBAs to focus on the wrong thing.

In this article, I will show one particular wait for Extended Events. As a matter of fact, if you were paying attention to the last article, you will have already seen this wait in passing. To get a quick glimpse or to recall what was discussed, please read the article about the live stream target here.

Patience Padowan

The first thing I want to do is clear my wait stats. Here is a quicky on doing that. Understand that this clears out the wait stats and resets the counters to 0. If you track your waits on a regular basis, this may cause a raised eyebrow by your team-mates.

After clearing my waits, I can check for a baseline. When checking for this baseline it is important to note that I have nothing ready from an extended event target currently. I will start that after getting my baseline. Here is what my waits look like prior to working with the target data from any XEvent Session.


This is pretty vanilla prior to working with the targets. That is a good thing for now. This gives me a good sense that the baseline is a good starting point. Now, similar to what was shown in the live stream article previously mentioned, I am going to open a live stream viewer for the system_health session. At this point, you could wait for a minute or three and then re-query the waits. This additional step would be to help show that the XE wait has not yet introduced itself.


Perfect. Now I have a live stream viewer open for the system_health session. I have a good baseline. Now I just need to watch the viewer for a bit. I am doing this to ensure enough time has passed by that my waits have incremented. After a few events pop into the system_health session, I will re-query my waits.


Look at how that wait has zoomed clear to the top! This wait is huge! This wait does not appear until the “Watch Live Data” option is being used to tap into the streaming target (really should be anything that is tapping into the live stream target via the GUI or via some other program). An example of “some other program” could be as simple as somebody querying the sys.fn_MSxe_read_event_stream function from management studio and trying to return the live stream data (as was discussed in the previously mentioned article).

Not understanding what causes the XE_LIVE_TARGET_TVF wait type can cause a data professional, or two, to chase their tail on something that may not be an issue overall. I have seen this happen on more than one occasion where somebody has spent hours trying to chase down the problem that this wait indicates. It doesn’t necessarily indicate a problem (unless you are a shoot from the hip gun-slinging troubleshooter type). It just means that the process accessing the live stream is waiting for more data to come through. That said, if this wait is high, maybe it is time to look into who might be tapping into the Live stream target.

Pretty straight forward and short today. I hope this helps avoid some time-waste for something that can be ignored most of the time.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.


2 thoughts on “Waiting, is it a Bad Thing?”

  1. Thanks for the blog post – it is very informative.

    I have a concrete example of this wait, the Quest software Spotlight for SQL server creates an XE session with data target to the ring buffer. After installing this we can see that there is a 1,000 ms per second constant wait of this wait type.

    Could this be caused by Spotlight continuously waiting for data from the ring buffer – and if so – is this a problem for the performance of the SQL server or can it be safely ignored also when it is *NOT* ad-hoc monitoring only.


    1. The xe_live_target_tvf which can be caused by accessing the sys.fn_MSxe_read_event_stream function programmatically, may or may not be a bad thing for performance. Is this concealing other waits because it is constantly polling? I prefer to not use any XE sessions created by 3rd party tools because I have found they are all too often heavy handed and duplicate efforts of other more efficient sessions I deploy to my clients. Could this be caused by Spotlight directly? Absolutely! Spotlight should probably be checking on a timer or something instead of constantly reading from the live stream buffer.
      There is no hard-line answer as to whether it is a problem indicator or not. Generally, I would say it is a benign wait. However, you threw in there that you are using Spotlight so my ears perk up a little and I would say it may be something you shouldn’t ignore and that you should keep an eye on for a while.

      Any “benign” wait can be a mask to other issues and when troubleshooting issues, we should look at the whole picture instead of a hyper-focused segment of the picture. Here is a good article from Erik Darling that underscores that point – https://www.erikdarlingdata.com/2019/07/too-much-of-a-harmless-wait-can-be-harmful/ .

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.