CODEWITHSUNDEEP
iisloggingiis-7ssasssas-2008
Suraj
Suraj

Reputation: 36547

Measure perf - components of a roundtrip MDX query

I would like to decompose the performance of a round-trip MDX query from a client to Analysis Services and back. In particular, I'm looking to identify/distinguish individual queries and record the time each query takes for:

I am open to other data-points that would be beneficial to identify bottlenecks in the lifecycle of a query.

My company has tested a mix of software including: Periodic SSAS DMV data collection, PerfMon, Flight Recorder, Splunk and SQL Sentry. We are having trouble tying it all together.

Upvotes: 0

Views: 135

Answers (1)

FrankPl
FrankPl

Reputation: 13315

One of the main problems that you have is that there probably are overlaps in time: msmdpump in IIS can start sending the first bytes to the AS server as soon as it has available the first few bytes of the XMLA from the http request, and vice versa, it probably starts sending the message as soon as the first few bytes from the response from the AS server is available.

Actually, the communication between msmdpump and the AS server is a binary version of the XML that is sent between msmdpump and the client, and hence easy to translate without knowing information later in the message. See http://sqlblog.com/blogs/mosha/archive/2005/12/02/analysis-services-2005-protocol-xmla-over-tcp-ip.aspx for some details about the protocol.

To track the times, my approach would a low level one: I would run Wireshark (http://www.wireshark.org/) on on the computer running IIS, and filter to only the http frames between the client and IIS and the frames between the IIS computer and the AS server. The contents of the frames would be more or less irrelevant, but you could see the time stamp of the first and last package of a request, giving you an rough estimate about the durations of the different communications. And staying on one computer for all network traffic logging avoids the need to have the clocks of all computers exactly synchronized.

Upvotes: 1

Related Questions