Wait Stats tab always disappears

sam.bishop 2016-10-20 17:36:29

I started using Plan Explorer just a bit after version 3.0 was released. So far, every time I have run a query in a new tab, the "Wait Stats" tab disappears, never to come back, without any warning or error message. (I have also tried queries that run for over a minute, in case it's a timing issue.) Also, I think I always use the "Get Actual Plan" button, so I don't think it's an "Estimated" versus "Actual" plan issue.

Here's some information about my environment:

  1. I'm not a DBA, but my login is a member of the "public" and "sysadmin" roles.
  2. "SELECT @@version" returns this: "Microsoft SQL Server 2014 (SP2) (KB3171021) – 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: )"
  3. Plan Explorer is version 3.0, build 10.9.9095.0.

I am able to create an Extended Events session on this server, but I don't know what is or isn't captured via Extended Events.

I would be happy to answer any questions or try different things.

Aaron Bertrand 2016-10-20 18:16:36
Hi Sam, sorry you are having trouble.

This is an uncommon but known issue due to a limitation in the Extended Events API; specifically, how the event_stream handles buffers. I don't believe this is documented anywhere, so I won't go into too much detail, but it is something you may have observed in the Live Data Viewer in SSMS as well, where events fail to show up until more events fire.

It is easy to work around in SQL Server 2016, but not so easy in earlier versions. We are working on both, hopefully for the next maintenance release. In the meantime, a workaround you could try is adding one or two WAITFOR commands at the end of the batch (and with or without a GO batch separator), which might be enough pressure on the buffers to force the other stuff you care about to display:

-- your queries here
WAITFOR DELAY '00:00:01'; -- you may have to experiment
WAITFOR DELAY '00:00:01'; -- with slightly longer durations

As an aside, this site is dedicated to assistance with specific execution plans; for product questions or technical support, please use our new help center.

sam.bishop 2016-10-20 19:36:00
Thank you, Aaron! That did it. And for the benefit of anyone else who has this problem, note that I was able to make the tab reappear in an existing session.

I wondered which forum I should use. I will use the correct one if I have anymore questions.