Page History
...
- 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
| Anchor | ||||
|---|---|---|---|---|
|
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?"
| Anchor | ||||
|---|---|---|---|---|
|
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
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.
...
See “About Precise for SQL Server tabs” on page 22.
| Anchor | ||||
|---|---|---|---|---|
|
The Maintenance tab displays information on different entities. This section provides an overview of all entities, their meaning, and their views.
...
- 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 the Instance Entity
The Instance entity displays DBA-related information on the monitored SQL Server instance, in the following views:
- 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 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.
...
- 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.
...
- Overview
- 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 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.
...
- 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).
- Notifications
- E-mail Notify. Indicates whether a notification e-mail should be sent, when the job is completed. The following values are available:
- Never. Notification will never be sent after the job is completed.
When the job succeeds.
When the job fails.
Whenever the job completes (regardless of the job outcome).
- Never. Notification will never be sent after the job is completed.
- Page Notify. Indicates whether a notification page should be sent, when the job is completed. The following values are available:
- Never. Notification should never be sent after the job is completed.
When the job succeeds.
When the job fails.
Whenever the job completes (regardless of the job outcome).
- Never. Notification should never be sent after the job is completed.
- Net Send Notify. Indicates whether a network message should be sent, when the job is completed. The following values are available:
- Never. Notification should never be sent after the job is completed.
When the job succeeds.
When the job fails.
Whenever the job completes (regardless of the job outcome).
- Never. Notification should never be sent after the job is completed.
- Write Notify. Indicates whether a notification event should be logged to the Microsoft Windows NT application log, when the job is completed. The following values are available:
- Never. The job should never be deleted.
When the job succeeds.
When the job fails.
Whenever the job completes (regardless of the job outcome).
- Never. The job should never be deleted.
- Delete Notify. Indicates whether or not the job should be deleted when the job is completed.
- E-mail Notify. Indicates whether a notification e-mail should be sent, when the job is completed. The following values are available:
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:
...
- Enabled
- Category name
- 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
About the Job entity Association area
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.
...
| 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. |
About the Job Step entity
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.
...
- Overview
- 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.
- Status. Displays the status of the last run. 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, 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 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:
...
- 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
About the Job Step entity Association area
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.
...
| 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. |
About the Text tab
The following table displays the job step command to be executed by the subsystem.
...
| 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. |
About the Job Schedule entity
The Job Schedule entity displays scheduling information for the selected Job, such as schedule details, and next run date.
...
- 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:
...
The following job schedule details are monitored by the Collect Schema Changes process:
- Enabled
- Schedule Details
About the Job Schedule entity Association area
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.
...
| 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. |
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
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.
...
| 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.
|
About the Job Step Run entity
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:
- Overview
- Command
About getting an overview of a job step run
The Overview displays general information on the Job Step Run, as follows:
...
- 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.
Info 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.
| Info |
|---|
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
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.
...
| 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. |
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 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.
About the Operators entity Association area
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.
...
| 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. |
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
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.
...
| 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. |
About the Database Maintenance Plan entity
The DB Maintenance Plan entity displays information on the selected DB Maintenance Plan such as, owner, databases, and actions.
...
- Overview
- History
- 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]".
Info 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
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.
...
| 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. |
| Anchor | ||||
|---|---|---|---|---|
|
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
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...