6

To view "recent expensive queries" I use Activity Monitor. Some of the queries there are with parameters.

Is there a way to know the values?

Improve this question

2 Answers 2

Reset to default
3

Is there a way to know the values?

No, not directly from the Activity Monitor.

You would need to capture those details either using a server-side trace, or using Extended Events.

See the details on this question and the answers for details about how to create a trace to collect that data.

Improve this answer
2
  • thanks, how would I go about creating a server side trace?
    – user5495269
    Commented Nov 13, 2015 at 21:00
  • I edited my question with a link that shows how to create the trace.
    – Hannah Vernon ♦
    Commented Nov 13, 2015 at 21:53
7

You can right-click the query you are interested in and select Show execution plan: enter image description here

Once you have the execution plan open, just right-click in an empty area and select Show Execution Plan XML.... Once you have that you will see a section in the XML <ParameterList>. In that node it will show you the ParameterCompiledValue:

    <ParameterList>
      <ColumnReference Column="@records" ParameterCompiledValue="(0)" />
      <ColumnReference Column="@traceid" ParameterCompiledValue="(2)" />
    </ParameterList>
  </QueryPlan>
</StmtSimple>

Another way would be to copy the execution plan XML out and paste it into Plan Explorer and view the Parameters tab: enter image description here

Edit for the nerds

If you open up Activity Monitor, and then go into tempdb you will find all the temp objects (tables and all) that Activity Monitor is using. For the Expensive Queries it is using the object dbo.#am_get_querystats. You can open that procedure up and look at the exact query they use to pull it. Pretty cool. I did this for the activity section so I could get similar information without having to open up Activity Monitor all the time.

Improve this answer
2
  • 3
    Shawn, are you sure those are the values used at run time? I think they are the compile-time values.
    – Hannah Vernon ♦
    Commented Nov 13, 2015 at 21:09
  • @MaxVernon Not sure what the difference between "run-time" and "compile-time" variables is in the context of SQL stored procedures, but this method shows me the values being used by the instance of the expensive query in the Activity Monitor window.
    – Dan
    Commented Nov 10, 2016 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.