Examining SQL Agent activities and other SQL Server activities

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.

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.

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.

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” 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 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.

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
• 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.

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).
• 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).
  • 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).
  • 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).
  • 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).
  • 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 Type. Type of change. The following types of changes are reported—Dropped, Created, Changed.
• Data Changed. The data that was changed (such as, schedule details, command text.)
• 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 details are monitored by the Collect Schema Changes process:

• 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.

The following table describes the details displayed for each job.

Table 1 Job details table

The following table shows information on the job's last run.

Table 2 Last Run table

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.

The following views are available:

• 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:

• 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

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.

The following table shows the general job step information displayed in the Details tab.

Table 3 General job step details

The following table displays details of the job step's last run.

Table 4 Last run tab

About the Text tab

The following table displays the job step command to be executed by the subsystem.

Table 5 Job Step command

About the Job Schedule entity

The Job Schedule entity displays scheduling information for the selected Job, such as schedule details, and next run date.

The following views are available:

• 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

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.

The following table describes the information displayed on job schedule changes.

Table 6 Job Schedule Changes table

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.

The following table describes information displayed in the Job Run table.

Table 7 Job Run table

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:

• 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.

    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.

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.

The following table describes the information displayed in the Job Step Run table.

Table 8 Job Step Run table

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.

The following table describes the information displayed in the Operators table.

Table 9 Operators table

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.

The following table describes the information displayed in the Details table.

Table 10 Details table

The following table describes the information displayed in the Notifications table

Table 11 Notifications table

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.

The following views are available:

• 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]".

  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.

The following table describes the information displayed in the DB Maintenance Plan table.

Table 12 DB Maintenance Plan table

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

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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls, in the Association area, click Jobs.
4. On the Details tab, drill down to the job you want to analyze.
5. On the View controls in the Main area, click Overview.
6. 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.
7. Locate the run with the highest duration and drill down to it to view additional information.

Examining how job changes affect 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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls, in the Association area, click Jobs.
4. In the Association area, locate the jobs that have changed (indicated by a Change icon). Drill down to one of these jobs.
5. 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

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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls, in the Association area, click Jobs.
4. On the Last Run tab, drill down to the job you want to analyze.
5. On the View controls in the Main area, click Overview.
6. View the steps chain and the status and duration of each step

Monitoring the occurrences of alerts

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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls, in the Association area, click Alerts.
4. On the Details tab view when the alert last occurred.
5. 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

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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls, in the Association area, click DB Maintenance Plan.
4. Drill down to a DB Maintenance Plan entity.
5. 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.
6. On the View controls in the Main area, click History. Observe the actions performed in the specified plan.
7. 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.