Applies to: SQL Agent Insight version 0.23
The Insight is the key focal point for each monitored SQL Server instance within SQL Agent Insight. It is from here that one will spawn other actions that drive the monitoring experience.
Let’s break down a typical Insight into its major components. Knowing these locations and terminology will assist in the quick navigation of an Insight and monitoring session.
Across the top of each Insight is presented:
- The edition of SQL Server being monitored, the product level, along with it’s build number,
- The most recent polled status of the SQL Server Agent service, and,
- The toolbar containing shortcuts to all of the accessible features of a monitored instance. More on that below.
Directly below the main toolbar is the metric summary, showing the totals of each metric state possible, being one of either:
- Warning, or,
The action bar is stationed to the left of the metric data on an Insight and has the following options, from top to bottom:
- Expand all metric groups,
- Collapse all metric groups,
- Size all columns to fit,
- Copy all Insight metrics to clipboard,
- Zero out all warning and critical thresholds,
- Set all warning and critical thresholds to latest polled values, and,
- Save thresholds.
This section of an Insight is the “meat and potatoes”, the focal point of an Insight. Here metrics are grouped into the following 10 categories (from top to bottom):
- Multi Server
- Database Mail
Here, the maintenance of each individual metric can be toggled on or off (with a simple checkbox), view the latest polled value of a metric or group of metrics, along with setting the warning and critical thresholds of each individual metric.
Additionally, right-clicking on the group category will toggle the maintenance for all of the metrics in that category, and provides a quick method of doing so instead of toggling each individual metric.
To understand all of the metrics and their specific details, navigate to the metrics page.
Located at the bottom of each Insight, the status bar showcases the following:
- The current authentication method to the monitored instance, either Windows or SQL Authentication. In the case of SQL authentication, the connected username will be shown.
- The most recent timeframe, as indicated by an icon, showing either 3, 5, 7, 14 or 30 days, depending on the current setting in the Insight options. Please see the metrics page on how the recent timeframe affects certain metrics during polling.
- The polling interval indicated beside the hourglass icon, being one of the following:
- 15 seconds right through to 1 hour, or,
When the polling interval is anything other than manual or paused, a progress bar will be shown next to the polling interval indicating when the metrics will be refreshed next.
Most actions and events are captured within the Insight log and can include:
- Loading / saving of metric threshold values.
- Any changes to the Insight’s options.
- Toggling of a metric’s maintenance.
- The listing of any running jobs.
- A summary of metric severities after each polling interval, either automatic or manual.
- Heartbeat messages.
- An entry when a metric breaches it’s warning or critical threshold.
Filtering of the log is facilitated by clicking on the filter icon in the column header. Upon doing so, the status bar will indicate “Filtered logs | x of y total” notifying that not all logs are being shown.
In addition, the following actions are available in the Log tab’s action bar:
- Size all columns to fit
- Copy current page of Insight log to clipboard
- Clear entire Insight log
- Clear any / all filter(s)
An Insight toolbar has all of the options when working with the monitored instance of SQL Server.
From left to right, they are:
/ PAUSE / RESUME POLLING
At any time, active polling of your SQL Server instance can be started and stopped with this option and indicated within the Insight status bar.
REFRESH METRICS VIA A MANUAL POLL
No matter what option is selected for the polling interval, the refresh button will instantiate a connection to your monitored SQL Server instance and update the metrics at any time refreshing all values to the most recent.
Aside from the Insight, the Job Monitor will be the most used functionality for a monitored instance. The Job Monitor is a detailed window into the most useful data for each of your scheduled jobs. With more features than the default Job Activity Monitor in SQL Management Studio, monitoring your job infrastructure is intuitive and easy.
JOB CONFLICT OUTLOOK
Another unique feature to SQL Agent Insight, the Job Conflict Outlook (“JCO”), allows the ability to peer into the future to see potential job run conflicts over the next 24 hours. The JCO does this by calculating all possible job runs and then determines if these conflict with any other scheduled jobs. These job run schedules are then represented in a Gantt style chart to easily see which jobs overlap. This helps in determining the optimal time to run those critical jobs without conflicts.
For jobs to appear on the chart, they must:
- have a schedule,
- be enabled,
- have a frequency greater than zero (i.e. run at least once in a 24 hour period),
- be a local job (TSX type), and,
- run at a minimum every 5 minutes. (Jobs that run more frequently, typically, are quick to run, and will likely conflict with a lot of other jobs).
Another one-up compared to the log viewer provided with SQL Management Studio, SQL Agent Insight’s Log Viewer shows just how large those SQL agent logs are, along with the ability to easily filter by severity or by message text to get to the root of a problem quickly.
MORE OPTIONS (which expands to the following)
- Database Mail Profiles & Accounts
- Database Mail Items
- Database Mail Event Log
- Registry Values
/ LIST RUNNING JOBS (indicator)
This icon is represented as a “running person” image. By default, this icon is blue in color when running jobs are not being logged into the Insight log. When enabled, this icon will be green in color.
/ EMAIL NOTIFICATIONS (indicator)
This icon is represented as a “megaphone” image. By default, this icon is blue in color when email notifications are disabled / not configured. When enabled, this icon will be green in color.
Everything that can be set with a monitored Insight is done via the Insight Configuration dialog.