Logo
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
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
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 4)
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 3) - What to Keep in Mind When Tuning
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 2) - Create, Execute, and Analyze
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 1) - Plan Your Tests
- Windows Small Business Server 2011 : Planning the Network Infrastructure (part 2)
- Windows Small Business Server 2011 : Planning the Network Infrastructure (part 1)
- Introducing Windows Small Business Server 2011
- Microsoft Dynamics GP 2010 : Streamlining payables processing by prioritizing vendors
- Microsoft Dynamics GP 2010 : Gaining visibility with Horizontal Scroll Arrows
- Microsoft Dynamics GP 2010 : Speeding up account entry with Account Aliases
 
 
Most view of day
- Exchange Server 2007 : Migrating from Windows 2000 Server to Windows Server 2003 (part 5) - Moving Operation Master Roles
- Microsoft Lync Server 2013 : Deploying Lync Online - Configuring Lync-to-Phone, Creating a SIP URI Dial Plan
- SharePoint 2010 : Configuring Search Settings and the User Interface - Web Parts (part 3)
- Maintaining Security : Restricting Content in Windows Media Center, Creating Trusted Contacts, Installing Critical Fixes
- SQL Server 2008 R2 : A Performance Monitoring Approach (part 2) - Monitoring the Processors
- Extending the Real-Time Communications Functionality of Exchange Server 2007 : Installing and Using the Communicator 2007 Client, Web Conferencing with Office Live Meeting
- Installing and Configuring the Basics of Exchange Server 2013 for a Brand-New Environment (part 6)
- Microsoft Word 2010 : Creating Desktop Publishing Documents - Adding a Watermark
- Windows Server 2003 on HP ProLiant Servers : File Replication Service Design and Implementation (part 1)
- System Center Configuration Manager 2007 : Desired Configuration Management - Troubleshooting
Top 10
- Windows Server 2012 : Administering Active Directory using Windows PowerShell (part 3) - Performing an advanced Active Directory administration task
- Windows Server 2012 : Administering Active Directory using Windows PowerShell (part 2) - Finding Active Directory administration cmdlets
- Windows Server 2012 : Administering Active Directory using Windows PowerShell (part 1) - Managing user accounts with Windows PowerShell
- Windows Server 2012 : Enabling advanced features using ADAC (part 3) - Creating fine-grained password policies
- Windows Server 2012 : Enabling advanced features using ADAC (part 2) - Configuring fine-grained password policies
- Windows Server 2012 : Enabling advanced features using ADAC (part 1) - Enabling and using the Active Directory Recycle Bin
- Microsoft Excel 2010 : Protecting and Securing a Workbook - Marking a Workbook as Read-Only
- Microsoft Excel 2010 : Protecting and Securing a Workbook - Working with Office Safe Modes
- Microsoft Excel 2010 : Protecting and Securing a Workbook - Setting External Content Security Options
- Microsoft Excel 2010 : Protecting and Securing a Workbook - Setting Privacy Options - Set Parental Controls for Online Research
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro