Create a Job-Scheduling Advisor

If you’re of a certain age, you might remember your teachers using an overhead projector and overlaying transparencies on it to demonstrate a significant event changing over time. I had long wanted to use one of my favorite SQL Server tools, SQL Server Reporting Services (SSRS) 2005, to do something similar: Juxtapose information from different sources, times, or locations into a single point of reference. I couldn’t find an easy way to produce overlay reports in SSRS 2005, so I gave up trying to do so—until SSRS 2008 was available. I’ll show you how I used SSRS 2008 to produce a Gantt chart overlay report, which is also an add-on to my DBA Repository solution, covered in “Use SSRS and SSIS to Create a DBA Repository,” February 2008. (You might find it helpful to refer to this article when following along with my description of the overlay reporting solution here.)

First Tries

Of the many metrics that DBAs monitor, SQL Server Agent jobs are often at the top of the list. Knowing how long a job runs, its status, and when it begins and ends are all critical components that need to be analyzed daily. I needed a report that I could glance at and determine when to schedule a new job to run at a time when there would be little contention from other jobs or servers. What I really wanted was a Gantt chart like those provided by project management tools.

My first step in creating the overlay report was to determine what time-range data I wanted the report to analyze, using data from my DBA Repository solution. I’d had mediocre results in my first attempt at building the Gantt chart in SSRS 2005. I wrote an alternative solution, a T-SQL query using data from the DBA Repository that would display at a glance a view of job schedules and run times for me. Using this textbased visual aid, I could compare SQL Server Agent job activity on one server or multiple servers by using the consolidated information in the DBA Repository database.

The query was lengthy and complex because it used datetime conversions and re-conversions to calculate and display start and end time and durations. Listing 1 shows a snippet of the code that produces the text-based Graph column.

Notice the use of the REPLICATE, DATEDIFF, and SUBSTRING functions that work together to build the graph of job run times. Web Listing 1 shows the full query, which will execute against the DBA_Rep database. (You can download the complete code and files for the overlay report solution by clicking the Download the Code link at the top of this article’s web page.)

I needed the graph to display a start time, signified by leading spaces to simulate a time of day—for example, 13:00:00; a duration, in minutes (m) or hours (h); and the end time. The result of the query, run against two test servers, shows the textual Graph field that's in Figure 1.

Figure 1: Results of T-SQL report showing job durations

SSRS 2008 to the Rescue

When SQL Server 2008 was available, I jumped at the chance to convert the textual query to an SSRS report using one of the new graphical charts in SSRS 2008. I was surprised to find how easy it was to implement the Gantt chart report by using the new Range graph.

My first step was to modify the base query that would feed the report. Of course, I no longer needed the text-based graph query, so I quickly wrote a new, much more compact version of the query from the same source data. The source data comes from one table in the DBA_Rep database called Jobs. This table is populated via a SQL Server Integration Services (SSIS) package that’s part of the DBA Repository. The underlying structure is an amalgam of fields derived from several system tables in the msdb database, including sysjobs, syscategories, sysoperators, and sysjobschedules. With the new query, I have what I need to create the report.

Two of the key components are the job start time and end time, as the query excerpt in Listing 2 shows (Web Listing 2 has the complete query). These values weren’t as easy to gather as I had surmised because of the datatype conversion that must be done. Although there’s an active_start_time field in the Jobs table, the value is stored as an integer and must be converted. I couldn’t use the active_end_time value, also an integer, because it isn’t actually the job’s end time, but instead is when the job schedule ends. To obtain the job start- and end-time values, I needed to take the job’s active_start_time, assuming it was also the last time the job ran, and add to it the last_run_duration, using the DATEADD function to derive the end-time values. I planned to include controls within the report to mitigate any potential inaccuracy, even with all the complex required conversions.

Building the Overlay Report

After I had written the query, it was time to build the Gantt-style overlay report. I knew the report would have two multivalued parameters, one for a server input and one for the job’s last-run date. Let’s look at the overall report, then its constituent pieces.

In the report’s Report Data section are two parameters, Server and Last_Run_Date. The two parameters take multiple values when the report executes and the values are passed to the Jobs data set, which you can see under DataSource1. As long as the multi-valued parameters are passed to the queries’ WHERE clause using the IN keyword, SSRS will automatically determine how to pass in multiple values. Listing 3 shows the WHERE clause of the Jobs data-set query, the driving query for the report. Also notice that additional date conversion is required for both the report parameter and last_run_date field values.

The other two data sets, Servers and LastRunDate, provide values to populate the parameter drop-down lists when the report is run. When the report is executed, the parameter drop-down lists are populated with the two server names available for the query. I have one server with a default instance and a named instance of SQL Server, W4HD1 and W4HD1SRVSAT, respectively. In a real-world scenario, you could have many servers available. The report will resize itself automatically as more than one server is selected. I’ve limited this example to two servers for simplicity and ease of viewing.

Once you’ve selected the server(s) and the last run date (the two parameters I mentioned earlier), the report is generated and can be previewed. These parameters let you control which servers to compare, or “overlay,” and which run dates you want to view. If on a Monday morning in November, for example, you wanted to see which jobs ran the previous day, I can select only the day before from the Last_Run_Date parameter drop-down list.

What I had originally wanted was to see not only the job start times but also the duration and any jobs that overlap in time. Since the data field for the Range chart is simply the start and end time difference, the graphical representation of the overlapping jobs is handled automatically by the report. Figure 2 shows the data series properties, top and bottom values, which are the start and end times of the jobs from the Jobs data set. The Category field is simply the job name, referenced by [name]. The Server field will be placed in the Series section of the chart. I also added 3D elements to the chart, by right-clicking the range bars in Design view and selecting 3D Effects.

Figure 2: Data series properties for report

You might also notice in Figure 2 that the Tooltip is an expression, indicated by the value . Since I wanted this overlay report to be interactive and also to conserve report real estate, I placed some of the details in the Tooltip item, which will be displayed when you hover the mouse over it. Listing 4 shows the Tooltip expression used to display report information. To view the tooltip Visual Basic code in the report, simply click the Expression button (the ƒx button) at the right. I use the Chr function to insert a carriage return—the equivalent of Chr(13)—in the tooltip.

The final view, Figure 3 shows the report after it was executed with two server values and the run date 9/21/2008. You can see that two jobs overlap between the two servers. Hovering the mouse over a job shows the job name, run date, and the actual duration. I also determine from the tooltip that both jobs have UNC in their title, indicating that a network resource is being used and possible resource contention that I might want to investigate further.

Figure 3: Final version of overlay report

Adding Value to DBA Repository

If you’ve already downloaded and implemented my DBA Repository solution, all you need to do to implement the overlay report is to download, load, and deploy the Overlay.rdl file. The overlay report has already helped me immensely in my daily tasks, and I hope it adds value to your own reporting capability. I’ll keep expanding this solution and plan to post updates to it if readers are interested. As always, I welcome your feedback and enhancements.