Long-Running Jobs
You might want to tune the Long Running Jobs monitor. Default thresholds are specified in minutes, with 60.00 the low threshold and 120.00 the high threshold.
When a job runs between 60 and 120 minutes, the health state changes to Warning; for jobs over 120 minutes, the health state becomes Critical. OpsMgr also generates an alert.
To change these values, perform the following steps:
1. Open the OpsMgr console and select the Monitoring node in the left pane. In the Look for box, type long running jobs for the applicable list of monitors.
2. Find the Long Running Jobs monitor for the SQL Server 2008 management pack.
3. To override the value for a particular agent, right-click Long Running Jobs, Overrides. Click Override the Monitor, For a specific object of type: SQL Server 2008 Agent.
4. In the Select Object screen, select the agent of interest and click OK.
5. On the Override Properties screen, override the values for Lower Threshold and Upper Threshold as necessary. Be sure to specify a management pack other than Default as the place to save your changes.
Using the Long Running Jobs monitor incurs a performance hit. It also behaves differently depending on the SQL Server version:
• For SQL Server 2000, long-running-job monitoring is available only on a per-job basis. You must enable the discovery.
However, every job for every SQL Server installation you are monitoring is separately monitored—with a performance overhead. After enabling this discovery, each job has an associated Job Duration monitor. Enter thresholds for this monitor in HHMMSS format. The defaults in the current release of the SQL Server management pack are now one hour (010000) and two hours (020000), with updated knowledge regarding the thresholds.
• SQL Server 2008 and 2005 include this per-job functionality, disabled by default, with thresholds in minutes.
• SQL Server 2008 and 2005 also have per-SQL Server agent monitoring for SQL Server jobs. If you enable this discovery, each instance of the SQL Server agent is discovered, with the Long Running Jobs monitor targeted at the SQL Server agent class and monitoring duration of all jobs on that agent. Without overrides, if any job exceeds the defined threshold for that agent, state changes with the monitor generating an alert.
Reports
The SQL Server management pack comes with predefined reports. These include the following:
• SQL Broker Performance
• SQL Database space report
• SQL Server Configuration
• SQL Server Database Engine Counters
• SQL Server Lock Analysis
• SQL Server Service Pack Report
• SQL User Activity
• Top 5 Deadlocked Databases
• User connections by day
• User connections by peak hours
Selecting one of these reports in the Reporting node of the OpsMgr console brings up a Details pane that tells how the report works, how to use it, and its parameters. Parameters include a Date/Time selection, which lets you choose a relative or fixed date and time range and time zone.
Figure 4 displays part of the database counters report, showing performance over a week. Reports can be scheduled or run on-demand and saved in XML, CSV, TIF, PDF, MHT (Web archive), and Microsoft Excel workbook format.
The DBA's Monitoring Tool
OpsMgr monitors pretty much the same things that DBAs monitor for SQL Server performance: memory usage, disk I/O, CPU utilization, latency (network saturation), backups, and key indicators. And OpsMgr puts it all, along with reports, in one convenient tool.