Monitoring SSAS, SSRS and SSIS with SQL BI Manager
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.
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.)
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.
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 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 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:
- Current alerts
- Historical alerts
- 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.
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.
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.
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.
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.
To understand more about what some of the counters mean, it may be helpful to review these MSDN and MSSQLTips.com articles:
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.
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.
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.
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
and data labels. Additionally you can click on a
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.
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.
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
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.
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.
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.
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.
Package History Table
This gives you a table version of the package history data.
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.
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.
- Download SQL BI Manager: https://www.idera.com/productssolutions/sqlserver/sql-server-business-intelligence
- See the SQL BI Manager Fact Sheet: https://www.idera.com/~/media/Corporate/Files/DataSheets/Idera-datasheet-SQL-BI-Manager.ashx
- Request a Demo: https://www.idera.com/productssolutions/sqlserver/sql-server-business-intelligence/requestademo
Last Updated: 2016-06-07
About the author
View all my tips