Page History
This section includes the following topics:
...
- About the Maintenance tab
...
- How the Maintenance tab is structured
...
- About examining Maintenance Tab Entities
...
- How the Maintenance tab can help you identify performance problems
About the Maintenance tab
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?"
How the Maintenance tab is structured
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.
...
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.
About the Main area in the Maintenance tab
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.
About the Association area in the Maintenance tab
...
See “About Precise for SQL Server tabs” on page 22.
About examining Maintenance Tab Entities
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:
...
- Instance Entity
...
- Job Entity
...
- Job Step Entity
...
- Job Schedule Entity
...
- Job Run Entity
...
- Job Step Run Entity
...
- Operators Entity
...
- Alert Entity
...
- Database Maintenance Plan Entity
About About the Instance Entity
The Instance entity displays DBA-related information on the monitored SQL Server instance, in the following views:
■ Overview
■ Job Changes
- Overview
- Job Changes
About getting an overview of the OS and SQL Server instance
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.
About viewing which Job changes were carried out during a selected time period
...
The following information is displayed:
- Name Name of the job that was changed.
...
- Change Type Type of change. The following types of changes are reported—Dropped, Created, Changed. Any change in a job's steps is reported as “Changed”, including Create and Drop.
- Change Date The estimated change date. The change is reported by the Precise for SQL Server schema change process, which runs, by default, once a day. In other words, the Change Date is actually the date the schema change
...
- process was executed (or the sample date).
...
...
About the Job Entity
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:
- Overview
■ Overview
■ Notifications
■ Job Changes
...
- Notifications
- Job Changes
About getting an overview of the selected job
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:
- Details Displays the following job details:
...
- Job—displays the job name.
...
- Category—displays the category of the selected job. For example, a job can be categorized as
...
- “Database Maintenance”.
...
- Enabled—indicates whether the job is enabled or disabled.
...
- Scheduled—indicates if there is at least one schedule for the job.
...
- Description—displays the job description entered by the job's creator.
- Last Run Displays information related to the last execution of the job.
The following information is displayed:■ Date—displays
- Date—displays the date of the last run.
- Last Duration—displays the duration of the last run.
- Status—displays the status of the last run. Status can be one of the following: Failed, Succeeded, Retry, Canceled, In Progress.
- Last Step Executed—displays the last step executed during the last execution of the job. In MS SQL
...
- graph
- Displays the job's resource consumption over time, within the selected time frame.
- Run history graph Displays the number of job executions versus duration over the selected time period.
About viewing a job configuration to send notifications
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 The following information is displayed:
Operators E-mail Operator—E-mail address of the operator to notify.
Page Operator—Computer or user ID used when sending a page.
Net Send Operator—Computer or user ID used when sending network messages.
Write to Windows Application Log—Indicates whether notification will be sent to the event log (YES/NO). Delete Job—Indicates whether the job should be deleted when it is completed (YES/NO).
...
Whenever the job completes (regardless of the job outcome).
Page Notify—indicates whether a notification page should be sent, when the job is completed. The following values are available:
...
Whenever the job completes (regardless of the job outcome).
Net Send Notify—indicates whether a network message should be sent, when the job is completed. The following values are available:
...
Whenever the job completes (regardless of the job outcome).
Write Notify—indicates whether a notification event should be logged to the Microsoft® Windows NT®
...
Whenever the job completes (regardless of the job outcome).
Delete Notify—indicates whether or not the job should be deleted when the job is completed.
About viewing job changes, schedules and step changes
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:
Type The type of object that was changed—Job, Job step, Job schedule. Name Name of the object.
...
Change Date The estimated change date. The change is reported by the Precise for SQL Server schema change process, which runs, by default, once a day. In other words, the Change Date is actually the date the schema change process was executed (or the sample date).
The following job details are monitored by the Collect Schema Changes process:
■ Enabled
...
- Enabled
- Category name
■ Owner
...
- Owner
- Start step name
...
- E-mail notify
...
- Page notify
...
- Net send notify
...
- Event log notify
...
- E-mail operator
...
- Page operator
...
- Net send operator
■ Delete level
...
- Delete level
About the Job entity Association area
...
The following table describes the details displayed for each job.
Table 11-1 Job 1 Job details table
Indicates 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 Status Displays the current status of the job (Executing Job Step, Waiting For Thread, Between Retries, Not Running, Suspended).
...
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 11-2 Last 2 Last Run table
Column Description
...
Job Displays the job name.
Table 11-2 Last Run table
Column Description
Last Duration Duration of the last run.
...
Last Step Executed The last step executed during the last execution of the job.
About the Job Step entity
...
The following views are available:
- Overview
■ Overview
■ Command
...
- Command
- Job Step Changes
About getting an overview of a job step
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.
Details Step Name—displays the name of the step.
Step ID—displays the step ID. Job—displays the name of the step's job. Owner—displays the owner of the step's job.
Category—displays the category of the selected job step. For example, a job step can be categorized as “Database Maintenance”.
Type—displays the name of the subsystem used by SQL Server Agent to execute the job step (for example, TSQL, and Replication Distributor.).
Sample Date—the estimated last change date of one of the job step details. The change is reported by the Precise for SQL Server Schema Change Process, which runs, by default, once a day. In other words, the Sample Date is actually the date the schema change process was executed.
Action Displays configuration information regarding step execution.
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.
Retry—number of retry attempts to perform if the step fails. Retry Intervals—amount of time to wait between retry attempts.
Last run Displays information on the last execution of the Job Step.
Date—date of the last run. Duration—the duration of the last run.
...
Message—displays an SQL Server error, if the job step finished with an error, in addition to information on the step run, such as under which user the step was executed. (Note: if the text in the Message field is cut off, hold the cursor over the Message to view the rest of the text.)
In MSIn MS-SQL graph Displays resource consumption for the job step over a specified time frame.
Run history graph Displays the number of job executions versus duration, over the selected time period.
About viewing the command executed by a subsystem
The Command view displays the command to be executed by the subsystem during the current job step.
About viewing Job Step changes
The Job Step Changes view displays the Job Step changes that were carried out during the specified time frame, as follows:
Data Changed Displays the data that was changed (such as, command text, and what action to perform in case of failure).
Old Value The value before the change. New Value The value following the change.
Change Date The estimated change date. The change is reported by the Precise for SQL Server schema change process, which runs, by default, once a day. In other words, the Change Date is actually the date the schema change process was executed (or the sample date).
The following job step details are monitored by the Collect Schema Changes process:
...
- Sub System Name
...
- Command Text
...
- On Success Action
...
- On Success Next Step
...
- On Fail Action
...
- On Fail Next Step
...
- Database Name
...
- Database User Name
...
- Retry Attempts
■ Retry Interval
...
- Retry Interval
About the Job Step entity Association area
...
The following table shows the general job step information displayed in the Details tab.
Table 11-3 General 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.
...
Step ID Displays the ID of the step in the job.
Step Name Displays the name of the step.
Table 11-3 General job step details
Column Description
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.
...
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 11-4 Last 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.
...
Duration Amount of time that elapsed during the last execution of the job step.
About the Text tab
The following table displays the job step command to be executed by the subsystem.
Table 11-5 Job 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.
...
Step Command Displays the command to be executed by the subsystem during the current job step.
About the Job Schedule entity
...
The following views are available:
■ Overview
■ Job Schedule Changes
...
- Overview
- Job Schedule Changes
About getting an overview of a job schedule
The Overview displays the following Job Schedule details:
Job Displays the name of the job.
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 days in week to run the job.
Sample Date The estimated last change date of one of the job schedule details. The change is reported by the Precise for SQL Server schema change process, which runs, by default, once a date. In other words, the Sample Date is actually the date the schema change process was executed.
About viewing Job schedule changes
The Job Schedule Changes view displays the Job Schedule changes that were carried out during the selected time frame, as follows:
Data Changed The data that was changed. Old Value The value before the change. New Value The value following the change.
Change Date The estimated change date. The change is reported by the Precise for SQL Server schema change process, which runs, by default, once a day. In other words, the Change Date is actually the date the schema change process was executed (or the sample date).
The following job schedule details are monitored by the Collect Schema Changes process:
■ Enabled
■ Schedule Details
- Enabled
- Schedule Details
About the Job Schedule entity Association area
...
The following table describes the information displayed on job schedule changes.
Table 11-6 Job 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.
...
Next Run Date Displays the date of the next job execution.
Table 11-6 Job Schedule Changes table
Column Description
Schedule Details Displays job scheduling details, such as frequency, date range, time range, and on which days of week job should be run.
About the Job Run entity
The Job Run entity displays information on the selected Job run, such as, run date, duration, last step executed.
About getting an overview of a job run
The Overview displays general information on the Job Run.
Details Job—displays the job name.
Owner—displays the job's owner.
Category—Displays the category of the selected job run. For example, a job run can be categorized as “Database Maintenance”.
Run Date—the date the selected job run was executed.
Duration—amount of time that has elapsed while the selected job run was executed. Status—displays the status of the job run (Failed, Succeeded, Retry, Canceled, In progress). Message—displays general job run information, such as whether or not the job was successful, which schedule executed the job run, and the last step that was executed. (Note: if the text in the Message field is cut off, hold the cursor over the Message to view the rest of the text.)
Last Step Executed—the last step executed in the selected job run.
Steps Chain—displays the step ID of all the steps executed in the selected job run in the order of their execution time. A plus sign (+) or minus sign (-) follows each step ID indicating whether or not the step was successful. Plus (+) indicates that the step was executed successfully; Minus (-) indicates that the step's execution failed.
About the Job Run entity Association area
...
The following table describes information displayed in the Job Run table.
Table 11-7 Job 7 Job Run table
Column Description
...
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. (Note: if the text in the Message field is cut off, hold the cursor over the Message to view the rest of the text.)
About the Job Step Run entity
...
The following views are available:
- Overview
■ Overview
■ Command
- Command
About getting an overview of a job step run
The Overview displays general information on the Job Step Run, as follows:
Details Step Name—displays the step name.
Step ID—displays the step ID. Job—the name of the step's job. Owner—the owner of the step's job.
Category—displays the category of the selected job run. For example, a job run can be categorized as “Database Maintenance”.
Type—the name of the subsystem used by SQL Server Agent to execute the job step. For example: TSQL, and Replication Distributor.
Run Date—the date the selected job step run was executed.
Duration—amount of time that elapsed during the execution of the selected job step run. Status—status of the job step run (Failed, Succeeded, Retry, Canceled, In progress).
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. (Note: if the text in the Message field is cut off, hold the cursor over the Message to view the rest of the text.)
)
About Command
The Command view displays the command to be executed by the subsystem during the current job step run.
Note: 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.
About the Job Step Run entity Association area
...
The following table describes the information displayed in the Job Step Run table.
Table 11-8 Job 8 Job Step Run table
Column Description
...
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.
About the Operators entity
The Operator entity displays information on the selected Operator, such as operator name, E-mail address, and pager address.
About getting an overview of a selected Operator
The Overview displays the following general details on the Operator. The following information is displayed:
Details 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 Net Sent Date—displays the date the operator last received a network message.
About the Operators entity Association area
...
The following table describes the information displayed in the Operators table.
Table 11-9 Operators 9 Operators table
Column Description
...
Last Net Sent Date Displays the date the operator last received a network message.
About the Alert Entity
The Alert entity displays information on the selected SQL Server Alert, such as alert name, error number, and E-mail notification.
About getting general information on an SQL Server Alert
The Overview displays general information on the Alert. The following information is displayed:
Details Alert—displays 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. Occurrences—number of occurrences for this alert.
Last Occurred—displays the last occurrence of this alert.
Count Reset Date—displays the date the occurrence count will be initialized to 0.
Performance Condition—the condition that is defined in the performance counter that triggers the alert. For example, SQLServer: Access Methods|Extent Deallocations/sec||<|15.
Notification 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.
About the SQL Server Alert Association area
...
The following table describes the information displayed in the Details table.
Table 11-10 Details 10 Details table
Column Description
...
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-11 Notifications 11 Notifications table
Column Description
...
Net Send Notify Indicates whether or not a network message will be sent (YES/NO).
Table 11-11 Notifications table
Column DescriptionYES/NO).
Last Notification Displays the alert's last notification date.
Notification Message Displays additional information sent with the alert.
About the Database Maintenance Plan entity
...
The following views are available:
- Overview
...
- History
■ History
■ Performance
- Performance
About getting an overview of the database maintenance plan
The Overview displays general information on the maintenance plan. The following information is displayed:
Details 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.
About viewing a maintenance plan's history
The History view displays a list of the actions performed by the selected maintenance plan. The following information is displayed:
Action Activity performed by the selected maintenance plan. For example, Backup database, and Verify Backup. Start Time Displays the date and time the action started.
End Time Displays the date and time the action ended.
Duration The amount of time that elapsed time during the execution of the action. Status Status of the action (Failed, Succeeded).
Message Displays a message regarding the action. For example, the message displayed for the “Backup database" action is: "Backup Destination [path]". (Note: if the text in the Message field is cut off, hold the cursor over the Message to view the rest of the text.)
Error Number Displays the error number that is reported if the action failed.
About Performance
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” on page 32.
About the Database Maintenance Plan entity Association area
...
The following table describes the information displayed in the DB Maintenance Plan table.
Table 11-12 DB 12 DB Maintenance Plan table
Column Description
...
Actions Displays a list of the actions to be performed by the selected DB maintenance plan.
How the Maintenance tab can help you identify performance problems
You can identify a performance problem by doing one or more of the following:
...
- Examining job performance
...
- Examining how job changes affect performance
...
- Examining the last execution of a job
...
- Monitoring the occurrences of alerts
...
- Monitoring a database (DB) maintenance plan
...
...
Examining job performance
...
To examine job performance
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the item you want to analyze.
...
- On the Association controls, in the Association area, click Jobs.
...
- On the Details tab, drill down to the job you want to analyze.
...
- On the View controls in the Main area, click Overview.
...
- In the Main area, observe how the job ran and compare the number of times it ran with the amount of time it ran.
Compare this information to the information displayed the In MS-SQL overtime graph to see a breakdown of its performance in the SQL Server.
...
- Locate the run with the highest duration and drill down to it to view additional information.
Examining how job changes affect performance
...
The jobs that were changed are indicated by an icon in the Association area. To determine how job changes affect performance1 In
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the item you want to analyze.
...
- On the Association controls, in the Association area, click Jobs.
...
- In the Association area, locate the jobs that have changed (indicated by a Change icon). Drill down to one of these jobs.
...
- On the View controls in the Main area, click Job Changes.
Observe which changes were made to the job or to its steps and schedules. Determine if the job change has affected performance.
...
...
- For example, if you notice that a job's schedule was changed do the following:
...
- On the View controls in the Main area, click Overview.
...
- Check the In MS-SQL graph to see how the selected job behaved in the SQL Server over the specified time period.
...
Examining the last execution of a job
...
To examine the last execution of a job
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the item you want to analyze.
...
- On the Association controls, in the Association area, click Jobs.
...
- On the Last Run tab, drill down to the job you want to analyze.
...
- On the View controls in the Main area, click Overview.
...
- View the steps chain and the status and duration of each step
Monitoring the occurrences of alerts
...
To monitor the occurrences of alerts
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the item you want to analyze.
...
- On the Association controls, in the Association area, click Alerts.
...
- On the Details tab view when the alert last occurred.
...
- Drill down to an alert to observe the actions performed when the alert was triggered and whether those actions were responsible for bottlenecks.
...
...
Monitoring a database (DB) maintenance plan
...
To monitor a database (DB) maintenance plan
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the item you want to analyze.
...
- On the Association controls, in the Association area, click DB Maintenance Plan.
...
- Drill down to a DB Maintenance Plan entity.
...
- On the View controls in the Main area, click Performance. Analyze how long the plan ran, during the selected time period, and try to detect any problematic periods.
...
- On the View controls in the Main area, click History. Observe the actions performed in the specified plan.
...
- Check the jobs listed in the Association area to observe how each job contributed to the entire plan and drill down to a specific job to focus on a performance problem.
...
Precise. Performance intelligence from click to storage. Learn more > >
...