|
Extended Events (EE) is a great SQL Server tool for monitoring Analysis Services, both multidimensional and tabular models. I use EE primarily to answer five questions about SSAS. 1. Who is actually using our tabular models and multidimensional cubes? 2. How often is [someone] running queries? 3. What are my longest running queries? 4. For multidimensional cubes, and I missing aggregations? 5. For long-running queries, is the problem my storage or formula engine? I also like to use EE to warm the multidimensional cache after cube processing, but that is a subject for another blog post. The subject of this blog post is really about interpreting data captured by EE. Prerequisite: Knowledge of how to start, stop and import data from EE sessions. If EEs are new to you, I have a blog post on EE for Analysis Services Database Administration that might be helpful. This blog post assumes that you have already imported EE data into a SQL Server database table and are at the point of wanting to analyze that data. Summary: If you want to jump to the finish line, download the two files I've attached to this blog post. They are t-sql statements that read from EE SQL Server tables for both multidimensional and tabular models returning login, query durations, execution counts, storage and formula engine durations. Enjoy! Detail: The most important thing to understand when reading EE data is how EE reports storage and formula engine durations. Very simplistically, the storage engine is exactly what you think it is -- it stores the data. The formula engine does the number crunching. The first step in SSAS query optimization is to understand where you are paying the price of the query duration. Multidimensional Cubes  Tabular Models  The attached t-sql documents have this all figured out for you, but I fully expect you to take these code samples and make them your own. For example, group the resulting row set ordering by QueryDuration DESC, or select for one particular user Id. Compare the storage and formula engine durations and email a SSRS report for percentages exceeding a predetermined amount. Basically, use EE to be proactive in finding long-running queries and don't wait for the help desk phone to ring. If working with multidimensional cubes, pay attention to the QuerySubcubeVerbose event. The query text will help you identify missing aggregations. Expert Cube Development with Multidimensional Models will give you more information on this. My experience has been that once I make sure all necessary hierarchies are in place, all fact tables are partitioned properly, aggregations have updated with usage-based aggregations, and poorly written MDX has been revised, I rarely have to spend time in the QuerySubcubeVerbose event. However, as a matter of last resort, this event has much complicated but informative query information. Files for Download 
0 Comments
|
|||||
| Microsoft Data & AI | SQL Server Analysis Services |
RSS Feed
