Problem:

Our organization has many SQL Server Agent Jobs that run over the course of a day. Many of these jobs are critical to the business so we need to monitor the status of the jobs closely. There are many options for monitoring such as the Job Activity Monitor or View History in SQL Server Management Studio (SSMS), executing custom T-SQL queries that use tables and views in the msdb database, PowerShell, third-party tools, etc. I would like to simplify our monitoring by creating a Power BI dashboard that will give us the details we need and provide an on-demand refresh capability. We are looking for a few pieces of information about each job that is currently running such as how long has the job been running, the average time the job runs, etc. Every so often I want to click on the dashboard and have the data refresh with the latest available status on the jobs currently running. In addition to the basic monitoring I have described, we need to have an estimated completion time for each job that is currently running. For any given job, its completion provides the availability of certain data that is critical to the business. As a for instance, there are many dashboards that get their data refreshed every morning. There is an expectation that the dashboards will be available no later than 8:00 AM. We need to know if any of the jobs that the dashboards depend on will finish later than 8:00 AM and if so, we need to post a company-wide alert that the dashboards are delayed and when they will be available. How can we accomplish this with Power BI?

Solution:

In this tip, I will demonstrate a simple approach to gathering data from the SQL Server Agent tables, populating a data mart, and rendering the data with a Power BI dashboard. At a high level, I will walk through the following steps:

1. Provide an overview of the Power BI dashboard that can be used to monitor SQL Agent jobs currently running.
2. Review the SQL Server Agent tables used to gather data for the dashboard.
3. Review the data mart tables that will be populated from the SQL Server Agent tables.
4. Provide an overview of the SQL Agent jobs and SQL Server stored procedures that extract data from the SQL Server Agent tables and populate the data mart.
5. Discuss the design considerations for the Power BI dashboard.

Power BI Dashboard

The following is an example of the Power BI dashboard that I created to monitor active SQL Server Agent jobs:

The main points for the dashboard are:

• The dashboard is shown running in Power BI Desktop Version 2.84.701.0 64-bit (August 2020).
• The dashboard is populated from a data mart that will be discussed in the sections that follow.
• Click the Refresh button to update the data from the latest data loaded to the data mart.
• SQL Agent Status is a Card visual that displays the status_desc column from the DMV sys.dm_server_services. The value retrieved should be Running, indicating that the SQL Agent service is currently running.
• Last Refresh is a Card visual that displays the date and time that the underlying data mart tables were last refreshed from the SQL Server Agent tables.
• The SQL Agent Jobs Currently Running Table visual is populated from a view in the data mart with the details on the SQL Agent jobs currently running. Duration and Avg. Duration are in seconds. Duration is how long the job has been running. Exec Count is the number of times that the SQL Agent job has run per the data in the SQL Server Agent tables.

SQL Server Agent Tables

SQL Server Agent provides many tables in the msdb database that can be queried to retrieve current and historical data about SQL Server Agent jobs. For our purposes, we will be using the following tables:

• sysjobs stores data about each SQL Agent job
• sysjobsteps stores data for each step in a SQL Agent job
• sysjobactivity stores data about current SQL Agent job activity and job status
• sysjobhistory stores data about SQL Agent jobs and job steps that have executed
• syssessions stores data each time the SQL Agent service is started

Data Mart History Tables

The data mart has a group of tables that contain historical data extracted from the SQL Server Agent tables. The main points for the model are:

• The data mart tables are populated from the data in the SQL Agent tables.
• The Job table is created from the data in the sysjobs and sysjobsteps tables. The step_count column is the count of steps in the SQL Agent job per the sysjobsteps table.
• The JobInstance table is used to assign a sequential value (job_instance) for each time that the SQL Agent job executes. The JobStepInstance table is used to assign a sequential value (job_instance) for each time that the SQL Agent job step executes. The job_instance associates the job step with the job_instance in the JobInstance table. It is the way to join the job steps executed to the job.
• The JobStepInstanceAllStepsCompleted table contains every job instance where all steps of the job completed successfully.
• The ExcludeActiveJobs table can contain the list of SQL Agent jobs that are to be ignored by the Power BI dashboard.

Data Mart Active Job Tables

The data mart has a small group of tables and views that contain the data pertaining to SQL Agent jobs currently running. The data is extracted from the SQL Server Agent tables and the data mart history tables.

SQL Agent Job: CALCULATE JOB HISTORY

The goal of the CALCULATE JOB HISTORY SQL Agent job is to calculate the average duration of SQL Agent jobs and job steps. The calculations are primarily based on the data available in the sysjobhistory table. When the data is retrieved to refresh the Power BI dashboard, the average durations are already calculated and simply retrieved from a table.

SQL Agent Job: SQL AGENT DATA MART ETL

The SQL AGENT DATA MART ETL job only has one step; it executes the stored procedure GetActiveJobs. The GetActiveJobs stored procedure extracts data for the SQL Agent jobs that are currently running from the sysjobactivity table and also retrieves the average job and job step durations as explained above. The data is inserted into the ActiveJobs table. The view vActiveJobs queries the ActiveJobs table and is used by the Power BI dashboard to populate the list of the SQL Agent jobs currently running.

Power BI Dashboard Development

When you are retrieving data from a SQL Server database with Power BI, you can choose from the Import or DirectQuery Data Connectivity modes. Import makes a copy of your data and stores it in the Power BI pbix file. DirectQuery queries the data directly from the SQL Server database(s). Given that the SQL Agent data is in the msdb database on the SQL Server instance where the SQL Agent service is running, I chose DirectQuery.

Whenever you retrieve data for analysis and reporting, it is often a good idea to transform the data into a schema that is conducive to reporting. This is typically the star schema. I did this and created the data mart although it is not the typical star schema as mentioned above. However, Power BI has some really impressive capabilities for transforming data as shown in the tip Introduction to Power BI Desktop and the M Language. With data coming from SQL Server, I choose to do the transformations in SQL Server using stored procedures that write the data to tables in the data mart. At this point I am much more comfortable with this approach. In addition, I have created a data mart which could easily be used outside of Power BI.

As far as deployment of the Power BI dashboard, I only used it running in Power BI Desktop which is quite an impressive application. It supports both development and rendering of the dashboard. Literally you just click Refresh to update the data rendered on the dashboard. The other options for deployment are the Power BI Report Server and the Power BI Service. I usually use the Power BI Report Server which is an enhanced version of SQL Server Reporting Services (SSRS) which allows you to upload and execute Power BI dashboards (i.e. .pbix files) the same as you upload and execute SSRS report (i.e. .rdl files).

One final note is that I developed the code using a single SQL Server instance. This allows me to create stored procedures that retrieve data from the msdb database and insert into the SQL_AGENT_DATA_MART database tables. I simply use the three part name for the msdb tables; e.g. msdb.dbo.sysjobhistory. If I wanted to put the SQL_AGENT_DATA_MART database on a different SQL Server instance, I would create a SQL Server Integration Services (SSIS) package that uses a Data Flow to retrieve the data from the msdb database tables and write the data to staging tables in the SQL_AGENT_DATA_MART database. I would then modify my stored procedures to process the data in the staging tables to update the tables in the SQL_AGENT_DATA_MART database. I chose my approach of using stored procedures and a single SQL Server instance to make it easy to develop and test.