Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Monitoring SSAS, SSRS and SSIS with SQL BI Manager


By:   |   Read Comments   |   Related Tips: More > Monitoring

Problem

In the SQL Server world, there are plenty of tools available for monitoring the relational SQL Server database engine; these tools can analyze your servers and provide details on issues like missing indexes, long running queries, overburdened CPUs and more. However in the Business Intelligence (BI) space, the options for monitoring your BI services are more limited, but the need to monitor is just as important.

Solution

Fortunately, IDERA's SQL BI Manager offers dashboard functionality to monitor your BI services which provides intelligent monitoring capabilities for the three main SQL Server BI services:

  • SQL Server Analysis Services (SSAS),
  • SQL Server Reporting Services (SSRS), and
  • SQL Server Integration Services (SSIS).

SQL BI Manager Overview

Using SQL BI Manager, you can quickly gain insight on the overall well-being of your SQL Server BI services; furthermore the tool comes with the ability to drill into any area where you would like to see more granular details about a particular item, and the dashboard can be used to monitor as many instances as needed to keep your BI infrastructure running efficiently. (Note: in some of the screenshots personal information was blanked for items such as BI Service, user names, etc.)

Idera SQL BI Manager Main Interface

Adding SQL Server BI Services to Monitor

After installing SQL BI Manager, you can add as many instances to be monitored as you have services running the BI tool set. Within each instance, you can direct SQL BI Manager to process and evaluate the metrics from SSIS, SSAS, and SSRS individually. Thus on one server, you can decide to monitor only SSRS while on another instance, you can monitor SSAS, SSIS, and SSRS. Also, if you have a large list of servers to monitor, SQL BI Manager allows you to import the list or you can browse to find the server / instance to be added as shown below.

Add instance to SQL BI Manager

Finish adding instance to SQL BI Manager

Using SQL BI Manager to Monitor BI Services

Once you have added instances to monitor, you are now ready to start using the power of SQL BI Manager's metrics. For each of the three services, you can evaluate various operating system and metrics such as:

  • Bytes received and sent
  • Disk reads, writes, transfers
  • OS paging and pages per sec
  • Available bytes

In addition to the above server level counters, service specific metric values can also be monitored. These metrics include:

  • For SQL Server Analysis Services (SSAS):
    • CPU Usage
    • Cache Inserts and Evictions
    • Memory cleaner
    • Memory high and low limits
    • Queries from cache
    • Rows written per second
    • Total NONEMPTY calculated members and unoptimized
    • Total Sonar subcubes
  • For SQL Server Reporting Services (SSRS):
    • Bytes received and sent (specific to SSRS)
    • Errors per second
    • Logons attempts and successes per second
    • Memory shrinks
    • Active connections
    • Report requests per second
    • Requests not authorized and rejected
    • Data retrieval times for both interactive and subscription reports
    • Processing times for both interactive and subscription reports
    • Rendering times for both interactive and subscription reports
    • Web cached hits and misses
  • For SQL Server Integration Services (SSIS):
    • Buffer memory and spooled
    • Rows read and written
    • Package execution failures

In addition to the above numeric metrics, all three services include availability flags which display whether the service is currently available or if the service is unavailable as shown below. This way you can get a quick overview of all of your BI services.

SQL Server Instance Status in SQL BI Manager

SQL BI Manager Dashboard

One of the main benefits of SQL BI Manager is the ease of use of the dashboard. It is web based and thus can be accessed from anywhere by any user who has been assigned access to the dashboard. The SQL BI Manager home screen shows the most current set of alerts as they pertain to critical issues, warnings, and other information based alerts. It also shows what instances are up or down and purely the number of critical alerts.

SQL BI Manager Main Dashboard Screen

SQL BI Manager Alerts

We can easily move from the high level overview of the BI services to reviewing all the alerts currently in effect along with a historical view of the alerts that have occurred in the past.

The alerts feature performs in three ways:

  1. Current alerts
  2. Historical alerts
  3. Email alerts

First you can view the current active alerts as they appear in the top half of the screen as shown in the below screenshot. Next you can review all historical alerts that have been recorded based on a selected time criterion as shown in the bottom half of the below figure.

The historical alerts allow you to go back in time, and that functionality is especially helpful when you get a phone call from a user saying the "system was slow two days ago at 3 PM in the afternoon". You can then retrieve and review the statistics and metrics that were captured at the time. Maybe it was an SSRS issue, for instance, and thus SQL BI Manager allows you to see all the alerts that were reported at that time and compare it to all the reports that were running at the same time.

SQL BI Manager All Alerts Interface

Alert Drilldown

Often we want more details than the alert line provides, so we can just click on the Alert Preview hyperlink, illustrated below, which forwards us to a graph showing details about the selected alert.

Click on Alert in SQL BI Manager for more information

The Alert Preview link sends us to a list of graphs pertaining to the alert and, most importantly, to the time frame when the alert was logged. In the below illustration, you can quickly see the Threads Waiting data point jumped up severely around 5:30PM on the day of the alert. You will also notice an informative tool tip displays as you hover over each of the data points.

Historical Alert in SQL BI Manager

Adjusting Alert Levels

Let's say you know that the system is taxed and you want to adjust the default threshold levels for an alert or to turn an alert On or Off. As shown in the below screenshot, the Administration area gives you the ability to do just that. We can, as displayed in #3, enable or disable a specific alert by simply checking or unchecking the Enable check box. If we want to change the threshold levels, we just click the pencil edit icon (#4), and then adjust the figures as appropriate.

Change Alerts Levels in SQL BI Manager

Here we can see how we can configure the alert settings. There are three levels of measurement: informational, warning and critical. For each item, you can set the appropriate threshold level for your environment.

Change Threshold Levels in SQL BI Manager

To understand more about what some of the counters mean, it may be helpful to review these MSDN and MSSQLTips.com articles:

Email Notifications

The third and final alerting feature in SQL BI Manager allows you to set up email notification for critical and warning level alerts. The email alerts are in addition to the historical messages in the alert log which allow you to research and respond to past events. Setting the email is as simple as adding the email address for each instance, service, and level for the alerts as outlined below.

Confiure Email Alert in SQL BI Manager
Confiure Email in SQL BI Manager

Validate Email Alert in SQL BI Manager

These alerts of course generate an email to the selected users as the alert is triggered by the instance. A sample of this email alert is shown below.

Sample Email Based Alert in SQL BI Manager

Monitoring SSAS with SQL BI Manager

SQL BI Manager dashboard also lets you drill into specific details pertaining to each of the three services. Thus for SSAS we can see what current SSAS alerts are being triggered and the SSAS related OS and performance counters. We then can monitor SSAS related activities such as SSAS sessions, object usage, models (database / cube list), and current user activity. For each performance category metric, as noted in the below screen print, a set of charts or graphs is displayed for further analysis.

These charts can easily be exported to pdf, png or jpeg. Similarly, for the SSAS specific metrics, such as current sessions or user activity, the table details can be exported to Excel, XML, or pdf. If a significant amount of user activity is displayed on a particular SSAS metric, then filtering can easily be applied to the table to limit the list of values.

Furthermore each chart contains interactive features which allow you to click on a data point which, in turn, will pop up a tool tip with detailed data values and data labels. Additionally you can click on a series label within the legend which toggles that particular series on and off within the chart or graph. I particularly find the active sessions metric to be very helpful in determining who is running what on the server right now.

SSAS Counters in SQL BI Manager

SSAS Sessions in SQL BI Manager

Monitoring SSRS with SQL BI Manager

Next switching to Reporting Services, the SSRS dashboard provides a comprehensive overview on the current state of Reporting Services. It is great to see the current critical alerts and then by using the Reporting Activity area, we get a good historical perspective on what has been run and rendered on the report server. The pattern of the metric list is similar to the SSAS area in that we first see OS and performance counters; then we move into the SSRS specific metrics and detail items such as Report Executions and User Activity. Similar to the SSAS dashboard, we can easily get chart coverage for SSRS metrics over a period of time and again we can quickly export these charts and tables.

SSRS Counters in SQL BI Manager

SSRS Requests in SQL BI Manager

As shown below, we can easily see details about a SSRS report execution such as the output format, who ran the report, what parameters were selected, and how long it took to retrieve, process, and render the report, and which reports took the most time to run. Similar to the SSAS dashboard, we can easily adjust a charts date parameters for SSRS metrics. Likewise, we can quickly export the table or chart to various formats.

SSRS Render Time in SQL BI Manager

Monitoring SSIS with SQL BI Manager

The last main dashboard in SQL BI Manager is for SSIS. Again and in a similar fashion to the SSAS and SSRS areas, we are able to report on the disk, processor, cache and CPU usage. Likewise, you can also report on specific SSIS packages as shown below.

SSIS Counters in SQL BI Manager

SSIS Metric in SQL BI Manager

Package Execution

This contains the execution history of all SSIS packages.  This information consists of status as well as error messages for any failed packages.  You are able to select the package and get details about the execution of the package.  These details include the steps executed in the package as well as any messages that were generated for these steps.  This can be very useful in pinpointing exactly where a package fails as well as why the package failed at that step.  This helps developers quickly determine what needs to be done to fix the failed execution.

sql bi manager package execution

Package Execution Details

This shows the details that you can get by selecting a package that has multiple steps and the messages associated with a particular step which are displayed in the Task Details pull down.

sql bi manager package execution details

Package History

This gives you a list of all packages that have been executed.  By selecting a package you can see that package's execution performance over time.  The default is a chart over time of the execution time.  The history also shows the last execution status of the package.  This is useful for monitoring performance over time to determine if there might be an issue somewhere in the tasks being executed within the package.

sql bi manager package history

Package History Table

This gives you a table version of the package history data.

sql bi manager package history table

Overall, the key values are the ability to drill into package execution to quickly identify why a package failed.  As part of the monitoring, an alert can be set to notify on package failure.

Recommendation

IDERA's SQL BI Manager is definitely a valuable tool for your BI tool set for monitoring the Microsoft BI stack on all your servers. It provides real-time metrics and alerts for all three BI services: SSAS, SSRS, and SSIS. You can monitor these services online via the IDERA SQL BI Manager Dashboard while at the same time creating critical email notifications to keep you informed when specific metrics meet your customized levels of urgency.

IDERA continues to enhance the product with a development road map in mind which includes many enhancements recommended from end users; they will also include support for SQL Server 2016 instances shortly after SQL Server 2016 is made available.

To get started, download a fully functional 14 day trial of SQL BI Manager.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools