24.
Click the
Execute button within the toolbar, as shown in Illustration 19.
Illustration
19: Click the Execute Button to Run the Query ...
25.
Shift
immediately back to the SQL Server Profiler window.
26.
Pause the trace
once again, as we did earlier.
27.
Scroll to the
bottom of the rows in the viewer (the most recent events logged are based upon
the MDX query we executed above), and select the row containing the Query
Begin EventClass / 0 MDXQuery EventSubClass combination,
as depicted in Illustration 20.
Illustration
20: Select the Query Begin Event for the MDX Query ...
The MDX
query appears in the lower half of the trace viewer, as follows:
SELECT
{[MEASURES].[Internet Order Quantity]} ON AXIS(0),
NON EMPTY {[Customer].[Customer Geography].[Full Name].MEMBERS}
ON AXIS (1)
FROM
[Adventure Works]
<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
<Catalog>Adventure Works DW</Catalog>
<SspropInitAppName>Microsoft SQL Server Management Studio -
Query</SspropInitAppName>
<LocaleIdentifier>1033</LocaleIdentifier>
<ClientProcessID>5820</ClientProcessID>
<Format>Native</Format>
<AxisFormat>TupleFormat</AxisFormat>
<Content>SchemaData</Content>
<Timeout>0</Timeout>
</PropertyList>
The Query
Begin event indicates the syntax that was dispatched to the Analysis
Server. The Query Cube events that follow provide notification that
the formula engine has initiated query execution, indicating code as it is
executed. Moreover, subsequent events are logged (other than the core events,
these can differ, depending upon query complexity) to report upon events that
occurred in resolution of our query. The information provided can be highly
useful when we need to diagnose issues, or if we otherwise want to understand
details of the steps of query execution. We can filter for MDX queries
just as we did for SQL queries, should isolation of this sort prove
useful.
28.
Click the
Stop Selected Trace button within the toolbar, as shown in Illustration 21,
to stop the trace.
Illustration
21: Click the Stop Selected Trace Button ...
And so we
see, through our brief overview of its capabilities, that SQL Server Profiler
can serve as a formidable tool in assisting us in understanding the details of
what is going on within Analysis Services. The trace mechanism
that the Profiler provides us allows for sophisticated control over the
selection of events for our analysis and diagnosis efforts. As we have discussed,
the SQL Server Profiler trace can be highly useful in our efforts
with tuning of processing and query performance within Analysis Services
2005. We employ the SQL Server Profiler within other
articles of this series, as well is in my MDX
Essentials Series, here at Database Journal.
29.
Experiment
further with the trace, and with SQL Server Profiler, as desired.
30.
Select File
-> Exit to dismiss SQL Server Profiler,
when ready, as depicted in Illustration 22.
Illustration
22: Exiting SQL Server Profiler
31.
Within the SQL
Server Management Studio, Select File -> Exit to leave the application, when
ready, as shown in Illustration 23.
Illustration
23: Exiting SQL Server Management Studio
32.
Save the MDX
query file, if desired, by navigating to a convenient location, and
clicking the Save button, on the Save File As dialog that appears
next, similar to that depicted in Illustration 24.
Illustration
24: Saving the MDX Query (If Desired) ...
Conclusion
In this article, we introduced
the SQL Server Profiler, a monitoring and troubleshooting tool that is
highly useful in performance tuning Analysis Services 2005, including SQL
and MDX queries. Our examination of the Profiler began with a
discussion surrounding its use, in preparation for gaining some hands-on
exposure via our practice exercise. We performed a connection to the Analysis
Server with SQL Server Profiler, and defined a trace to
monitor examples of processing and queries within Analysis
Services.
We next executed our trace to monitor Analysis Services
events, adding a
filter as we proceeded to examine sample processing events, and then
moving on to monitor sample query events,
all of which we initiated, for purposes of our practice session, via the SQL
Server Management Studio, just as we might in many real-world scenarios. Throughout the steps of our
practice session, we touched upon various details to consider surrounding the usage of the SQL
Server Profiler trace within our respective business environments.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.