13

The new Query Store introduced in SQL Server 2016 is great. It's a nice replacement for much of what I used to do with the older Profiler tool. However, I haven't found a way to capture parameter values associated with individual calls to the high resource-consuming queries it sniffs out. Is this possible?

I understand that Query Store deals more with aggregated data vs. individual calls, so I suspect I might be out of luck here. When I find a slow query, I find it handy for troubleshooting to also have the parameters associated with one of its slowest calls. I'd like to know how to do this using the latest and greatest tools. (I do not miss using Profiler!)

Security-wise is Query Store any less locked down than Profiler? I would think it need to capture data from individual calls at some level in order to calculate aggregates. Just not sure if it stores any of it.

Community
  • 1
Todd Menier
  • 515
  • 1
  • 6
  • 13

1 Answers1

13

Depending on the plan, you should be able to see the ParameterCompiledValue for each parameter by inspecting the XML plans saved in sys.query_store_plan.

The list is typically contained towards the end of the xml plan, and can be found in the ParameterList node.

This doesn't provide the value for every execution, but does give you an idea of what the plan was compiled with.

If you truly need to get the value for each execution, you could create an Extended Events session to gather the details.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323