2

Got an issue, similar to this one: Why does SQL Server Profiler trace not capture SELECT operator in execution plans?. There was an advice to use XE instead of Trace, but it does not help. Thing is, the same problem repeats on SQL Server 2016 with Extended Events.

I need to investigate a query from within a batch. This batch involves some temporary tables, so I can’t just catch a query from the middle and put it into Management Studio, Plan Explorer or any other app. So, the next idea was to watch it “in a wild”, gathering all the needed telemetry.

At first, I’ve set a Trace with “RPC completed”, “SP Statement completed” and “Showplan XML Statistics Profile” events. Everything went great except… I didn’t get just one plan – the plan I needed – because it was “skipped”.

Then I set an XE session with the same events: “rpc_completed”, “sp_statement_completed” and “query_post_execution_showplan”. I set “no data loss” policy (I know, it hurts, but I needed answers and it’s dev-box anyways). But… still the same result.

plan missing

All the sessions had 2 filters: by application name and by database id. I can’t see any good reason, why some plan just gets “skipped”. Maybe, someone can point me at what I am missing?

  • Try removing the database ID filter. And may as well remove the application name filter too while you're troubleshooting the issue. – Max Vernon Apr 10 at 13:11
  • Few updates: 1. Removing filters did not work -- plan is still missing, which is strange 2. Thanks for spotting on Query Store! I completely missed such an option. I found my plan there, but there's one thing: QS shows estimated plans without actual execution stats. I can see why it works that way, but this does not solve my current case: to get actual stats and find out, what I can improve. – 0wl Apr 10 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.