This section includes the following topics:
Almost every SQL Server instance contains user applications along with SQL Server Agent activities (or other activities that the DBA runs to monitor the instance). The Maintenance tab mainly displays information on the SQL Agent activities and other SQL Server activities that are related to the DBA maintenance tasks.
The Maintenance tab enables you to provide answers to the following types of questions: "When was a specific SQL Agent job executed? What was the duration of the last run and which changes in the job led to performance degradation?"
The Maintenance tab displays information on a selected entity and its associated entities. When you open the Maintenance tab from the Dashboard tab, the selected entity is by default Instance, meaning that information is displayed on the instance level.
When you open the Maintenance tab from the Activity tab, the tab is launched, in context, if the selected entity exists in the Maintenance tab. For example, if you launch the Maintenance tab from the Activity tab after examining a Job's performance, the same job entity will be displayed when you launch the Maintenance tab.
If you open the Maintenance tab from another tab, the historical settings (meaning those settings which were selected when you left the tab, such as the last entity you drilled down to) are taken into account and the information displayed the last time you viewed this tab is displayed (similar to clicking the History button and returning to a previous tab).
The selected entity is always reflected in the Tab heading, which serves as a point of orientation. The highest-level entity you can view information for in the Maintenance tab is Instance. You can select an instance from the Instance list.
The entities displayed in the Association area are associated with the selected entity displayed in the Main area. At times, the relationship between the entity displayed in the Main area and those displayed in the Association area is that of parent to child and sometimes it merely represents that there is a relationship between the selected entity and the entities displayed in the Association area.
The Main area shows comprehensive information on the selected parent entity. You can choose from several views to examine the entity from different angles. You can, for example, focus exclusively on Jobs data (default), or view data on database maintenance plans or alerts.
The Association area provides corresponding information on the child entities. You can view information on one type of child entity at a time, such as jobs or database maintenance plans, by selecting a control from the Association Controls list. The selection you make is reflected in the Association area only; the Main area remains unchanged. For some entities, tabs above the Association area enable you to view additional information: Clicking a tab changes the table columns for the same type of child entities.
From the Association area, you can also drill down to another parent entity by clicking a table row. A drill-down operation affects the whole tab: When you drill down to another entity, the Tab heading reflects the new selection, the Main area displays information on the new parent, and the Association area shows data on the child entities.
For example, when you want to view information on a specific alert, choose Alerts from the Association controls. The Association area changes to display alert-related information. Note that the Tab heading and the Main area remain unchanged. In the Association area, click the row of the alert you want to view detailed information for. The Tab heading indicates the newly selected entity; the Main area displays information on the selected alert, and the Association area shows information, such as jobs executed as a result of the alert.
See About Precise for SQL Server tabs.
The Maintenance tab displays information on different entities. This section provides an overview of all entities, their meaning, and their views.
The following entities can be examined in the Maintenance tab:
The Instance entity displays DBA-related information on the monitored SQL Server instance, in the following views:
The Overview displays general information on the OS and the SQL Server instance. In addition, the status of the Agent (SQL Server Agent, Microsoft search and Distributed transaction coordinator) is reported.
The Job view displays the job changes that were carried out during the selected time period. The changes can occur in the job itself, and in the job's steps and schedule details.
The following information is displayed:
The Job entity displays information on the selected job. While job performance information is also available in the Activity tab, the Maintenance tab displays additional information, such as, configuration parameters and scheduling, as well as performance information on the job runs. Unlike the Activity tab, where the source of information is the Precise for SQL Server Collector, the information displayed in the Maintenance tab is retrieved directly from the SQL Server.
The following views are available:
The Overview displays information on the selected job along with information on its last run and job runs, over the selected time period, as follows:
A job can be configured to notify various messages when it completes its run. The Notifications view displays how the job has been configured to send its various notification messages.
The following information is displayed:
The Job Changes view displays the job changes and job schedules and step changes that were performed over the selected time period. The following information is displayed:
The following job details are monitored by the Collect Schema Changes process:
You can view information on the entities associated with the selected Job entity in the Association area. It is possible to associate to the Job entity from the Instance, DB Maintenance Plan, Operator and Alert entities.
The following table describes the details displayed for each job.
Table 1 Job details table
| Column | Description |
|---|---|
 | Indicates whether the job has been changed according to the Precise for SQL Server schema change process, which runs by default once a day. |
| Job | Displays the name of the job. Each row in the table indicates a different job. |
| Duration | Displays the total time that elapsed for the executions of the job, over the selected time period. |
| Runs | Displays the number of job executions carried out over the selected time period. |
| Job Status | Displays the current status of the job (Executing Job Step, Waiting For Thread, Between Retries, Not Running, Suspended). |
| Owner | Displays the job owner, which can be one of the logins defined in the instance. |
| Category | Displays the category to which the job belongs, for example, Uncategorized (Local), Database Maintenance, Replication. |
| Enabled | Indicates whether the job is enabled or disabled. |
| Scheduled | Indicates whether or not a job is scheduled (YES/NO). |
| Warnings | Shows the warnings defined for a job. For example, if no schedule was defined for a job the following message is displayed: "No schedules were defined for this job." |
The following table shows information on the job's last run.
Table 2 Last Run table
| Column | Description |
|---|---|
| Status | Displays an icon indicating the status of the last run. Status can be one of the following: Failed, Succeeded, Retry, Canceled, In Progress. |
| Job | Displays the job name. |
| Last Duration | Duration of the last run. |
| Date | Date of last run. |
| Last Step Executed | The last step executed during the last execution of the job. |
The Job Step entity displays information on the selected Job step. While Job Step performance information is also available in the Activity tab, the Maintenance tab displays additional information, such as, configuration parameters, what action to perform when the current step has succeeded, in addition to performance information on the job step runs. Unlike the Activity tab, where the source of information is the Precise for SQL Server Collector, the information displayed in the Maintenance tab is retrieved directly from SQL Server.
The following views are available:
The Overview displays information on the selected job step along with information on its last run and job step runs, over the selected time period.
The Command view displays the command to be executed by the subsystem during the current job step.
The Job Step Changes view displays the Job Step changes that were carried out during the specified time frame, as follows:
The following job step details are monitored by the Collect Schema Changes process:
You can view information on the entities associated with the selected Job Step entity in the Association area. It is possible to associate to the Job Step entity from the Job entity.
The following table shows the general job step information displayed in the Details tab.
Table 3 General job step details
| Column | Description |
|---|---|
| Indicates whether the job step has been changed according to the Precise for SQL Server schema change process, which runs by default once a day. |
 | Indicates whether the current step is the first step in the job. |
| Step ID | Displays the ID of the step in the job. |
| Step Name | Displays the name of the step. |
| Type | The name of the subsystem used by the SQL Server Agent to execute the job step. For example, TSQL, and Replication Distributor. |
| On Success | Action to perform when the execution of the step succeeds. For example, Go to next step. |
| On Fail | Action to perform when the execution of the step fails For example, Quit the job when a failure is detected. |
| Database | The name of the database in which the command is executed (relevant only if the subsystem is TSQL and Replication Queue Reader). |
The following table displays details of the job step's last run.
Table 4 Last run tab
| Column | Description |
|---|---|
| Indicates whether the job step has been changed according to the Precise for SQL Server schema change process, which runs by default once a day. |
| Indicates whether the current step is the first step in the job. |
| Step ID | Displays the ID of the step in the job. |
| Step Name | Displays the name of the step. |
| Date | The date and time the job step was last executed. |
| Status | Status of the job step execution. The status can be one of the following: Failed, Succeeded, Retry, Canceled, In progress. |
| Message | Displays an SQL Server error, if the job step finished with an error, and information on the step run, such as under which user the step was executed. |
| Duration | Amount of time that elapsed during the last execution of the job step. |
The following table displays the job step command to be executed by the subsystem.
Table 5 Job Step command
| Column | Description |
|---|---|
| Indicates whether the job step has been changed according to the Precise for SQL Server schema change process, which runs by default once a day. |
| Indicates whether the current step is the first step in the job. |
| Step ID | Displays the ID of the step in the job. |
| Step Name | Displays the name of the step. |
| Step Command | Displays the command to be executed by the subsystem during the current job step. |
The Job Schedule entity displays scheduling information for the selected Job, such as schedule details, and next run date.
The following views are available:
The Overview displays the following Job Schedule details:
The Job Schedule Changes view displays the Job Schedule changes that were carried out during the selected time frame, as follows:
The following job schedule details are monitored by the Collect Schema Changes process:
You can view information on the entities associated with the selected Job Schedule Changes entity in the Association area. It is possible to associate to the Job Schedule Changes entity from the Job entity.
The following table describes the information displayed on job schedule changes.
Table 6 Job Schedule Changes table
| Column | Description |
|---|---|
| Indicates whether the job schedule has been changed according to the Precise for SQL Server schema change process, which runs, by default, once in a day. |
| Schedule | Displays the name of the job schedule. |
| Enabled | Indicates whether or not the schedule is enabled (YES/NO). |
| Creation Date | Displays the date the job schedule was created. |
| Next Run Date | Displays the date of the next job execution. |
| Schedule Details | Displays job scheduling details, such as frequency, date range, time range, and on which days of week job should be run. |
The Job Run entity displays information on the selected Job run, such as, run date, duration, last step executed.
The Overview displays general information on the Job Run.
You can view information on the entities associated with the selected Job Run entity in the Association area. It is possible to associate to the Job Run entity from the Job entity.
The following table describes information displayed in the Job Run table.
Table 7 Job Run table
| Column | Description | |
|---|---|---|
| Date | Displays the date the job run was executed. | |
| Status | Displays an icon indicating the status of the job run (Failed, Succeeded, Retry, Canceled, In progress). | |
| Duration | Amount of time that elapsed during the execution of the job run. | |
| Last Step Executed | The last step executed in the job run. | |
| Steps Succeeded | Number of steps that were executed successfully during the job run. | |
| Steps Failed | Number of steps that failed during the job run. | |
| Message | Displays general information on the job run, such as, whether or not the job was successfully executed, which schedule executed the job run and the last step that was executed.
|
The Job Step Run entity displays information on the selected Job Step Run, such as, run date, duration of execution, and status.
The following views are available:
The Overview displays general information on the Job Step Run, as follows:
Message. Displays an SQL Server error if the job step run finished with an error, and additional information, such as indicating under which user the step was executed.
if the text in the Message field is cut off, hold the cursor over the Message to view the rest of the text. |
The Command view displays the command to be executed by the subsystem during the current job step run.
The command displayed is the command currently saved in the production system table. If you change the command after this step is run you will not be observing the command that was actually executed. To view the command that was actually executed, check the Job Step Changes view for the specified step. |
You can view information on the entities associated with the selected Job Step Run entity in the Association area. It is possible to associate to the Job Step Run entity from the Job Run and Job Step entities.
The following table describes the information displayed in the Job Step Run table.
Table 8 Job Step Run table
| Column | Description |
|---|---|
| Date | Displays the date the selected job step run was executed. |
| Status | Displays an icon that shows the status of the job step run. Status can be one of the following: Failed, Succeeded, Retry, Canceled, In progress. |
| Duration | Displays the time that has elapsed during the execution of the job step run. |
| Message | Displays an SQL Server error, if the job step run finished with an error, and additional information on the job step run, such as under which user the step was executed. |
The Operator entity displays information on the selected Operator, such as operator name, E-mail address, and pager address.
The Overview displays the following general details on the Operator. The following information is displayed:
You can view information on the entities associated with the selected Operators entity in the Association area. It is possible to associate to the Operators entity from the Instance and Alert entities.
The following table describes the information displayed in the Operators table.
Table 9 Operators table
| Column | Description |
|---|---|
| Operator | Displays the operator's name. |
| Enabled | Indicates whether or not the operator can receive notifications when an alert occurs (YES/NO). |
| E-mail Address | Displays the operator's e-mail address. |
| Last E-Mail Date | Displays the date the operator last received an e-mail alert notification. |
| Pager Address | Displays the operator's pager address. |
| Last Pager Date | Displays the date the operator last received a pager alert notification. |
| Net Send Address | Displays the operator's Net Send address. |
| Last Net Sent Date | Displays the date the operator last received a network message. |
The Alert entity displays information on the selected SQL Server Alert, such as alert name, error number, and E-mail notification.
The Overview displays general information on the Alert. The following information is displayed:
You can view information on the entities associated with the selected Alert Entity in the Association area. It is possible to associate to the Alert entity the Instance entity.
The following table describes the information displayed in the Details table.
Table 10 Details table
| Column | Description |
|---|---|
| Alert | The name of the alert. |
| Enabled | Indicates whether the alert is enabled or disabled (YES/NO). |
| Error Number | Displays the number of the error that occurred. |
| Severity | Displays the level of severity that triggered this alert. |
| Last Occurred | Displays the last occurrence of this alert. |
| Occurrences | Number of occurrences for this alert. |
| Count Reset Date | Displays the date the occurrence count will be initialized to 0. |
| Performance Condition | The condition that is defined on the performance counters that trigger the alert. For example, SQLServer: Access Methods|Extent Deallocations/sec||<|15. |
The following table describes the information displayed in the Notifications table
Table 11 Notifications table
| Column | Description |
|---|---|
| Alert | Displays the name of the alert. |
| E-Mail Notify | Indicates whether or not an E-mail notification will be sent (YES/NO). |
| Pager notify | Indicates whether or not a pager notification will be sent (YES/NO). |
| Net Send Notify | Indicates whether or not a network message will be sent (YES/NO). |
| Last Notification | Displays the alert's last notification date. |
| Notification Message | Displays additional information sent with the alert. |
The DB Maintenance Plan entity displays information on the selected DB Maintenance Plan such as, owner, databases, and actions.
The following views are available:
The Overview displays general information on the maintenance plan. The following information is displayed:
The History view displays a list of the actions performed by the selected maintenance plan. The following information is displayed:
Message. Displays a message regarding the action. For example, the message displayed for the “Backup database" action is: "Backup Destination [path]."
If the text in the Message field is cut off, hold the cursor over the Message to view the rest of the text. |
The Performance view displays the total duration of the jobs that ere executed by the DB maintenance plan, during the selected time period.
See Focusing on information in overtime graphs.
You can view information on the entities associated with the selected DB Maintenance Plan in the Association area. It is possible to associate to the DB Maintenance Plan entity from the Instance entity.
The following table describes the information displayed in the DB Maintenance Plan table.
Table 12 DB Maintenance Plan table
| Column | Description |
|---|---|
| DB Maintenance Plan | The name of the selected DB maintenance plan. |
| Creation Date | Displays the date the selected DB maintenance plan was created. |
| Owner | Displays the owner of the selected DB maintenance plan. |
| Databases | Displays a list of the databases associated to the selected DB maintenance plan. |
| Actions | Displays a list of the actions to be performed by the selected DB maintenance plan. |
You can identify a performance problem by doing one or more of the following:
Examining job runs over time can help you identify problematic time periods and view the general trend of the job runs. Examining how the job ran, comparing its number of executions and total duration, and analyzing a breakdown of its performance in the SQL Server can help you detect bottlenecks and discover their causes. Bottlenecks can be caused by a variety of causes, such as changes in scheduling, running the job at an inappropriate time, and offending SQLs.
To examine job performance
Changing a job's details may affect the performance of your system. Some changes are implemented to improve the performance of your application. Precise for SQL Server allows you track job changes and correlate them to the resource consumption of the job. This allows you to detect changes that increase the resource consumption of the job.
The jobs that were changed are indicated by an icon in the Association area. To determine how job changes affect performance
Examining a job's last run can help you detect job failure and abnormal duration. Drilling down to the specified job run can help you learn more about its run by viewing the steps chain and the status and duration of each step.
To examine the last execution of a job
Monitoring alerts can help identify when an alert last occurred, what notifications were defined on the alert, when the notifications where last sent and which operators were involved.
To monitor the occurrences of alerts
Monitoring a database (DB) maintenance plan can help you learn about the actions being performed in each maintenance plan and detect performance problems resulting from these actions. Drill down to a DB Maintenance Plan entity and choose the Performance view to analyze a plan's duration over time and detect problematic periods.
To monitor a database (DB) maintenance plan
|