BI Sentry Overview

Download: To learn more about BI Sentry pricing and downloading a trial, see the SentryOne BI Sentry product page.

Overview

BI 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 BI Sentry, you can monitor the CPU, Memory, Disk, and Network servers resources to determine what's causing an issue. 

BI Sentry Layout

Similar to the SQL Server Performance Analysis Dashboard, in BI 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.

Note:  For a complete list and description of metrics available in the BI Sentry Dashboard, see the Performance Metrics article.

BI Sentry Performance Analysis Dashboard

Chart AreaDescriptionScreenshots
SSAS ActivityAllows 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.SentryOne SSAS Activity
SSAS GeneralMonitors 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 BI 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.
SentryOne SSAS General
SSAS MemoryMemory 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 BI Sentry article.
SentryOne SSAS Memory
SSAS StorageTo 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.SentryOne SSAS Storage

SSAS Overview

Note:  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. BI 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 BI 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.

SentryOne Labelled as tab

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.  

BI Sentry Events

BI Sentry provides the popular Event Calendar feature that many are familiar with from SQL Sentry. The SentryOne 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 BI Event View that shows instances of Analysis Services. Right-click the BI Sentry target, and then select Event Calendar to open the BI Event View

SentryOne BI Event View

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. 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.

BI Sentry Queries

For a further breakdown of SSAS activity on a query-by-query basis in BI 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.
SentryOne MDX Query

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).

BI Sentry Alerts

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

General Condition Alerts

Blocking SQL 

ConditionsDescriptions
SQL Server: Blocking SQLA 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 MaxBlock 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 

ConditionsDescriptions
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 

ConditionsDescriptions
Index: Defragmentation CompletedIndex Defragmentation completed
Index: Defragmentation FailureIndex Defragmentation failed. 
Index: Defragmentation StartedIndex Defragmentation started. 

Top Commands

ConditionsDescriptions
Analysis Services: Top Commands: CompletedAn 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 MaxThe duration threshold maximum was exceeded for an Analysis Services command (MDX or XMLA).
Analysis Services: Top Commands ErrorAn Analysis Services command (MDX or XMLA) completed with an error.
Analysis Services: Top Commands: Output Content MatchA match condition was found in a completed Analysis Services command (MDX or XMLA).

Top SQL 

ConditionsDescriptions
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 MaxTop SQL event exceeded the maximum duration threshold.
SQL Server: Top SQL: Duration Threshold MinTop SQL event didn't exceed the minimum duration threshold.
SQL Server: Top SQL: ErrorTop 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 

ConditionDescription
Virtual Machine: VM MovedThe Virtual Machine moved.

Failsafe Condition Alerts

Analysis Services  

ConditionsDescriptions
Analysis Services: OfflineAnalysis Services is offline or otherwise inaccessible.
Analysis Services: OnlineAnalysis Services is online and accessible.

SQL Server 

ConditionsDescriptions
SQL Availability Group FailoverThe SQL Availability Group failed over.
SQL Availability Replica HealthyThe SQL Availability Replica is healthy.
SQL Availability Replica UnhealthyThe SQL Availability Replica is unhealthy.
SQL Server Agent: OfflineSQL Server Agent is offline or otherwise inaccessible.
SQL Server Agent: OnlineSQL Server Agent is online and accessible.
SQL Server Cluster FailoverSQL Server cluster failed over.
SQL Server: OfflineSQL Server is offline or otherwise inaccessible.
SQL Server: OnlineSQL Server is online and accessible.

Windows Computer

ConditionsDescriptions
Windows Computer: OfflineWindows Computer is offline or otherwise inaccessible.
Windows Computer: OnlineWindows 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.