Documentation forSQL Sentry

SSAS Overview for SQL Sentry

Overview

SQL Sentry allows you to continuously monitor and troubleshoot SQL Server Analysis Services (SSAS) issues in a Multidimensional or Tabular mode. The two primary types of activities that occur in SSAS are querying and processing. Processing involves creating and updating data, while the storage engine queries the file system to retrieve data from the SSAS caches with baselining and alerting functionality and partition usage totals. For more information about usage totals, see the SSAS Usage Totals topic. Common issues you may encounter in SSAS include the following: 

In SQL Sentry, you can monitor the CPU, Memory, Disk, and Network servers resources to determine what's causing an issue. 

SSAS Layout

Similar to the SQL Server Performance Analysis Dashboard, in SQL Sentry the Windows Performance metrics are on the left and Analysis Services are on the right. The color coding on the Memory, CPU, and Network graphs provide a way to see what is responsible for different types of traffic.

Performance Analysis Dashboard SSAS target Multidimensional

Note:  For a complete list and description of metrics available in the SQL Sentry Dashboard, see the Performance Metrics article.
Chart Area Description
SSAS Activity Allows correlation between rows transferred and Formula and Storage Engine threads. It's important to identify if your main bottleneck is in the Formula Engine or Storage Engine.
SSAS General Monitors all of the activity for you continuously at a fraction of the overhead a Profiler Trace tends to impart. Average Time is a unique chart with average time metrics calculated by SQL Sentry. These counters are not available through Windows Performance Counters. Discover what the instance is spending the most time on with this chart. It divides the information into more detail for you:
  • Serialization is handled by the Formula Engine and is related to NON_EMPTY behavior.
  • Processing and SQL queries are handled by the Storage Engine.
SSAS Memory Memory monitoring is very different between Multidimensional and Tabular modes, and different counters are available depending on the mode. Specific to the Tabular mode, the Mem Usage chart allows you to see memory counters, and the VertiPaq Memory Limit allows you to see where memory pressure might be causing performance issues. The chart also displays the VertiPaq paging policy that is important for monitoring the performance metrics. For more information about the VertiPaq paging policy, see the Analyzing VertiPaq Memory Usage in SSAS Tabular in SQL Sentry article.
SSAS Storage To determine whether the disk system is a bottleneck for your SSAS instance, you need to verify that SSAS is accessing the disk system. Use this chart to correlate storage counters to other activity on your system.

SSAS Overview

Additional Information: For a complete overview of SSAS, see the About SQL Server Analysis Services Microsoft article.

SQL Server Analysis Services (SSAS) is a multi-threaded storage engine with detailed query analysis and data processing. SQL Sentry performs queries in the Storage and Formula engines to aggregate and calculate data. The two most common types of queries performed include: 

  • MDX Queries—Cumulative MDX workloads across the server, cell-by-cell operations, and potentially high-impact operations in the Multidimensional mode.  
  • DAX Queries—Shows the storage engine time per query, internal VertiPaq cache matches and scans, and it allows you to identify possible bottlenecks in the Tabular mode.

Memory in SSAS is stored in KB, and is both shrinkable and non-shrinkable. For more information about memory in SSAS, see the Analysis Services Memory Limits article. The following are three groups of metrics that monitor memory in the SQL Sentry Dashboard: 

  • Mem Usage (MB)—Total memory SSAS is using on the server. Right-click to choose the Low and High limit. You can also show by category to see the portions of the non-shrinkable memory being used. 
  • Cache Lookups—Shows memory performance in the Flat cache and Calculation cache on the formula engine and in the Dimension cache and Measure Group cache on the storage engine. 
  • Cache Activity—Indicates the memory pressure on the server. Shrink counters indicated that you're exceeding your defined memory limit. 
Note:  Monitoring memory is different in Tabular mode from Multidimensional mode because the Storage engine operates in-memory. The dashboard is also labeled to denote Tabular or (MDX) Multidimensional mode.

Performance Analysis Dashboard select SSAS Tabular instance

Note:  SSAS isn't just an interaction with the disk system, it's also an interaction with the file system as a whole. Analysis Services, unlike the SQL Server, makes extensive use of the windows file cache.  

SSAS Events

SQL Sentry provides the popular Event Calendar feature that many are familiar with from SQL Sentry. The SQL Sentry Outlook-style Event Calendar view provides visibility into specific events occurring over a range of time. With the Event Calendar, you can see the entire chain of events and often identify what led up to a specific performance issue.

Note:  Consider creating a Custom Events View calendar called SSAS Event View that shows instances of Analysis Services. Right-click the SQL Sentry target, and then select Event Calendar to open the SSAS Event View. 

Event Calendar SSAS Tabular target

This view shows commands and queries (including text, start and end times, as well as duration) that were executed against SSAS. Events are color-coded to indicate things such as failures. Processing can be a bottleneck in SSAS, and with the Event Calendar, you can see an XLMA command that performed processing and quickly visualizes the other queries that were executing at the same time to identify what might have been impacted by that processing to better understand the performance hits. 

If you've set up alerts for certain conditions, such as the SSAS Storage Engine Processing Pool Job Queuing and those alerts are triggered, they appear on the Event Calendar next to the other events for that time period.

Note:  If you have performance issues with Processing, confirm the following:

  • Check scheduled jobs and activities that may exist for that time frame. Move them to a less busy time, if possible, to mitigate resource contention.
  • Verify that you're using the correct Processing option. For example, ProcessFull refreshes everything whether there is new data or not. While this option may be necessary at times, you may not need to use it for all cases. Use a less resource-intensive Processing option when possible. Additional Information: For additional information on available options, see the Processing Options and Settings (Analysis Services) Microsoft article.
  • Confirm you are using partitions that provide additional performance benefits that minimize activity to what you need.

SSAS Queries

For a further breakdown of SSAS activity on a query-by-query basis in SQL Sentry, see the Top Commands tab. You'll see whether your query experienced more Formula Engine or Storage Engine time, and you can expand the view for a complete breakdown by Measure Group, Partition, Aggregation, and Dimension.

SQL Sentry MDX Query example

Download: To learn more about SSAS Performance, download our free eBook Optimizing SSAS Performance or watch this hour-long presentation on our Vimeo channel called Getting Peak Performance for SQL Server Analysis Services (SSAS).

SQL Sentry Alerts

Alerts allow the user to define global actions for the most common issues across your environment.

General Condition Alerts

Blocking SQL 

Conditions Descriptions
SQL Server: Blocking SQL A block was detected that was subject to the Minimum Block Duration set for the Blocking SQL Source. View the block on the Event Calendar or the Blocking SQL tab.
SQL Server: Blocking SQL: Duration Threshold Max Block exceeded the maximum duration threshold.
SQL Server: Blocking SQL: Output Content Match  A match condition was found in the output content of a block.

Deadlocks 

Conditions Descriptions
SQL Server: Deadlock  A deadlock was detected. View the deadlock on the Event Calendar or the Deadlocks tab.
SQL Server: Deadlock: Output Content Match  A match condition was found in the output content for a deadlock.

Index 

Conditions Descriptions
Index: Defragmentation Completed Index Defragmentation completed
Index: Defragmentation Failure Index Defragmentation failed. 
Index: Defragmentation Started Index Defragmentation started. 

Top Commands

Conditions Descriptions
Analysis Services: Top Commands: Completed An Analysis Services command (MDX or XMLA) completed. Note:  It is highly recommended that a ruleset be used with this condition to avoid excessive logging.
Analysis Services: Top Commands: Duration Threshold Max The duration threshold maximum was exceeded for an Analysis Services command (MDX or XMLA).
Analysis Services: Top Commands Error An Analysis Services command (MDX or XMLA) completed with an error.
Analysis Services: Top Commands: Output Content Match A match condition was found in a completed Analysis Services command (MDX or XMLA).

Top SQL 

Conditions Descriptions
SQL Server: Top SQL: Completed   Top SQL event completed. Note:  It is highly recommended that a ruleset be used with this condition to avoid excessive logging.
SQL Server: Top SQL: Duration Threshold Max Top SQL event exceeded the maximum duration threshold.
SQL Server: Top SQL: Duration Threshold Min Top SQL event didn't exceed the minimum duration threshold.
SQL Server: Top SQL: Error Top SQL event completed with an error.
SQL Server: Top SQL: Output Content Match  A match condition was found in the content of a completed Top SQL event.

Windows Computer 

Condition Description
Virtual Machine: VM Moved The Virtual Machine moved.

Failsafe Condition Alerts

Analysis Services  

Conditions Descriptions
Analysis Services: Offline Analysis Services is offline or otherwise inaccessible.
Analysis Services: Online Analysis Services is online and accessible.

SQL Server 

Conditions Descriptions
SQL Availability Group Failover The SQL Availability Group failed over.
SQL Availability Replica Healthy The SQL Availability Replica is healthy.
SQL Availability Replica Unhealthy The SQL Availability Replica is unhealthy.
SQL Server Agent: Offline SQL Server Agent is offline or otherwise inaccessible.
SQL Server Agent: Online SQL Server Agent is online and accessible.
SQL Server Cluster Failover SQL Server cluster failed over.
SQL Server: Offline SQL Server is offline or otherwise inaccessible.
SQL Server: Online SQL Server is online and accessible.

Windows Computer

Conditions Descriptions
Windows Computer: Offline Windows Computer is offline or otherwise inaccessible.
Windows Computer: Online Windows Computer is online and accessible.

Advisory Condition Alerts

For more information about the advisory condition alerts, see the Advisory Conditions Pack topic and the Advisory Conditions for Monitoring SSAS Performance article.