Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
Windows Server

Microsoft SQL Server 2008 Analysis Services : Monitoring query performance

7/15/2011 6:21:11 PM
We have already introduced many tools that can be used to get data on processing operations, using trace, performance counters and dynamic management views. The same tools can also be used to monitor query performance, and in this section we'll see how this can be done.

Monitoring queries with trace data

Regardless of the tool we use to collect it (SQL Server Profiler, ASTrace, XMLA, or Flight Recorder), trace data is the most important source of information on query-related operations. Trace data provides information on the internal operations of the Storage Engine and the Formula Engine, for example showing if aggregations are used or not or if calculations are evaluated in bulk mode or not.

The most important trace events for analyzing query performance are as follows (once again, the integer identifier for each event is shown in parentheses after its name):

  • Progress Report Begin (5) / Progress Report End (6) there is only one subclass event that is relevant for query operations:

    • Query (14)- shows when the Storage Engine accesses a partition or aggregation to get data. This will only happen if the data required is not available in the Storage Engine cache.

  • Query Begin (9) / Query End (10)- are raised at the start and end of query evaluation. They usually contain only one interesting subclass event:

    • MDXQuery (0) shows the MDX statement sent to Analysis Services. For the Query End event, the Duration column shows the overall amount of time taken to run the query and return its results back to the client.

  • Calculate Non Empty Begin (72) / Calculate Non Empty End (74)- have no related subclass events. The Calculate Non Empty events are raised when Analysis Services performs non empty filtering operations, for example when the NonEmpty MDX function or the Non Empty statement is used in a query, and these operations are often the cause of slow query performance. When the value of the Duration column for Calculate Non Empty End is large, we should check the column to see if it bulk mode is being used or not. When the value of IntegerData is 11, Analysis Services iterates over all the tuples in a set to perform non empty filtering, and this can be very slow. For all other IntegerData values (usually 1), Analysis Services is operating in bulk mode which is usually much faster. IntegerData

  • MDX Script Begin (78) Execute / Execute MDX Script End (80)- have no related subclass events. They are raised at the beginning and end of the evaluation of the MDX Script on a cube, which is usually a very quick operation. However, if there are any complex named sets defined on the cube then MDX Script evaluation can take a long time.

  • Get Data From Aggregation (60)- has no related subclass events. This event is raised when the Storage Engine reads data from an aggregation.

  • Get Data From Cache (61)- is raised when data is read from cache. There are several subclass events that identify the type of cache used. This event will be seen very frequently on a production server, because after a query has been run all of the data it returns should be kept in cache. If this is not the case, it usually indicates that either there is not enough memory available for caching, or that calculations have been written so that the values they return cannot be cached.

    • Get data from measure group cache (1)

    • Get data from flat cache (2)

    • Get data from calculation cache (3)

    • Get data from persisted cache (4)

  • Query Cube Begin (70) / Query Cube End (71)- have no related subclass events. After a Query Begin event has been raised, in some scenarios the MDX Script of a cube must be evaluated before query evaluation proper can start; in these scenarios Query Cube Begin is raised after the Execute MDX Script End event and shows when query evaluation actually starts.

  • Query Dimension (81)- this event is raised when queries retrieve members from dimension hierarchies. These members may or may not be in cache already; if they aren't we would expect to see some I/O read operations executed.

    • Cache data (1)

    • Non-cache data (2)

  • Query Subcube (11)- a single MDX query might result in several different subcubes of data being requested from the Storage Engine. A Query Subcube event is raised for each of these requests, and may be one of the following types:

    • Cache data (1)

    • Non-cache data (2)

    • Internal data (3)

    • SQL data (4)

  • Query Subcube Verbose (12)- is functionally identical to Query Subcube, but it adds more information about the multidimensional space of the subcube that is queried.

    • Cache data (1)

    • Non-cache data (2)

    • Internal data (3)

    • SQL data (4)

  • Serialize Results Begin (75) / Serialize Results End (77)- have no related subclass events. They mark the start and end of query results being sent back to the client. The Serialize Results Begin event might be raised before the calculation of all of the cell values in a cellset has been completed, although it often starts just after all calculation is finished (for example, a Non Empty statement in an MDX query could force this behavior).

Monitoring queries with Performance Monitor counters

Analysis Services Performance Monitor counters are more useful for monitoring general query behavior than understanding what is happening when individual queries are run. Logging these counters can help us to understand the characteristics of all of the queries our users are running, and therefore help us to find query performance bottlenecks. Here's a list of all of the relevant categories and the important counters in them:

  • MSOLAP: Cache

    • CurrentEntries is the number of entries in the cache

    • Total Direct Hits is the number of subcube queries answered from existing cache entries this number should be compared to Total Misses to evaluate the percentage of subcube queries that are resolved using cache

    • Total Misses is the number of cache misses this number should grow after the cache has been cleared (for example, after the Analysis Services service has been restarted, or after cube processing) and should not grow too much once the cache is warm. If this number continues to grow, we need to evaluate if there is not enough memory for cache or if the queries that are being run cannot take advantage of existing cache.

  • MSOLAP:MDX contains several counters with very detailed information, the most important of which is:

    • Total NON EMPTY un-optimized is the number of Calculate Non Empty operations (as described earlier in the section on trace data) that are using an un-optimized algorithm. If this number continues to grow, there may be MDX queries that are running slowly for this reason and we might want to find them and optimize them.

  • MSOLAP: Memory we can use the same counters used to monitor processing operations to monitor query operations.

  • MSOLAP: Storage Engine Query contains several counters with detailed information that we can use to measure the workload of Analysis Services; the most generic counter here is:

    • Total Queries Answered shows the total number of queries run against Analysis Services; if this number does not increase, no queries are being run.

Monitoring queries with Dynamic Management Views

DMVs are not really useful for finding slow queries or understanding what happens when a query is run; trace data is much more useful for this purpose. That said, the data that DMVs return on memory usage, for example, is very useful for understanding the state of Analysis Services at a given point in time, which will have an impact on query performance.

MDX Studio

MDX Studio is a powerful tool that allows us to analyze MDX expressions, monitor the performance of MDX queries and use trace data to determine whether the Storage Engine or the Formula Engine is the cause of poor query performance. A complete description of MDX Studio and links to download it (for free) are available at:http://tinyurl.com/MdxStudio.
Other -----------------
- Microsoft SQL Server 2008 Analysis Services : Monitoring processing performance
- Securing Dynamics NAV Applications : Sarbanes Oxley compliance
- Securing Dynamics NAV Applications : Roles and permissions
- Microsoft Dynamics CRM 2011 : Removing Members from a List by Using Advanced Find
- Microsoft Dynamics CRM 2011 : Adding Members to a List by Using Advanced Find
- Microsoft Dynamics AX 2009 : The MorphX Tools - Project Designer
- Microsoft Dynamics AX 2009 : The MorphX Tools - Application Object Tree
- SQL Server 2008 : Backing Up the System Databases & Backup History
- SQL Server 2008 : Managing Backups - Using Encryption
- SQL Server 2008 : Managing Backups - Backups from T-SQL
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
Top 10
- Windows Phone 8 Apps : Camera (part 4) - Adjusting Video Settings, Using the Video Light
- Windows Phone 8 Apps : Camera (part 3) - Using the Front Camera, Activating Video Mode
- Windows Phone 8 Apps : Camera (part 2) - Controlling the Camera’s Flash, Changing the Camera’s Behavior with Lenses
- Windows Phone 8 Apps : Camera (part 1) - Adjusting Photo Settings
- MDT's Client Wizard : Package Properties
- MDT's Client Wizard : Driver Properties
- MDT's Client Wizard : Application Properties
- MDT's Client Wizard : Operating System Properties
- MDT's Client Wizard : Customizing the Deployment Share
- Windows Server 2012 : Software and User Account Control Administration (part 5) - Maintaining application integrity - Configuring run levels
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro