How to use it

Directly pasting a query

queryfrompaste.jpg
Pasting a parameterized query directly in the Parameterized Query textbox will de-parameterize automatically, if the text contains parameter XML data (as is typical if one copies the Query Begin event TextData field from the SQL Profiler trace window directly - a manual way of getting this data during an investigation). If found, the XML for parameters will be extracted and isolated in the Parameters XML textbox, and a de-parameterized version of the query displayed in the De-Parameterized Query textbox (and highlighted in green), which can then be executed directly against the SSAS server in Management Studio.

Pasting back a modified de-parameterized query

dpqueryfrompaste.jpg
Pasting a de-parameterized query into the De-Parameterized Query textbox will correspondingly use any parameter data specified in the Parameters XML window to re-parameterize the query (provided parameter values match the values used in the query text of course) to display in the Parameterized Query window (highlighted in green).

Modifying parameters

paramsfrompaste.jpg
Modifying the Parameters XML textbox also updates both the Parameterized Query and De-Parameterized Query textboxes to reflect modified parameters (both highlighted in green).

Parsing a trace for longest queries automatically

loadfromtrace.jpg
To search a trace for the top X longest duration queries, simply click the Parse Trace File button and choose an SSAS profiler trace. The trace automatically loads rollover files, and by default, discovers the top 10 longest running distinct queries, eliminating duplicate queries from consideration. The number of queries returned can be changed, and the option to find distinct queries can be disabled. Parsing can be cancelled for a large trace file and results reported on the rows parsed so far at any time.

Including queries missing duration in the trace

There is also an option to include queries with no known duration. This problem occurs when a query was started in the trace but the trace ended before its Query End event, which reports duration. This may frequently occur if a query runs so long that it fails to complete before the trace must be stopped, and so these queries may be important to consider in some investigations.

Getting parameters when they are missing

Parameters are extracted automatically for all queries reported in the tool, but if the Query Begin event for a query is missing in the trace data, this cannot be retrieved then, since the parameter data is specified in that event. This may occur if a trace was started after the query, but the query ends while the trace is running. In these queries, one may sometimes find another query with matching parameter data, and simply copy that parameter XML, to paste directly in Parameters XML window then for the query missing that. The tool will then parameterize the query based on the provided parameters. This can sometimes allow an executable de-parameterized query to be obtained even when the original parameter values were not captured in the trace.

Last edited May 26, 2014 at 1:29 AM by jburchel, version 4