Using SQL Server to Analyze Analysis Services Profiler Traces from Tabular Models.

My last blog post was focused on using SQL Server to analyze profiler traces against Multidimensional Analysis Services databases, so I though it only appropriate to follow-up with a post on using SQL Server to analyze profiler traces from a Tabular Analysis Services database. In July of 2013, John Sirmon, Greg Galloway (Artis Consulting), Cindy Gross and Karan Gulati published the Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services whitepaper (http://msdn.microsoft.com/en-us/library/dn393915.aspx). They described the process that they used for determining whether a bottleneck was in Storage Engine for Formula Engine. I tried that approach (basically did a cut and paste of the SQL Query that begins on page 35) with a brand new profiler trace and was more than a bit surprised to see the user name associated with the fastest query in the trace associated with the slowest query in the trace. I was also a bit surprised to find that the Storage Engine time values exceeded the durations for the queries and that the duration reported was the sum of the durations of all eight (8) queries that were captured in the trace..