Tips for using jobs in SQL Server 2016
Run the scheduled jobs during periods of low database access.
To reduce the total SQL Server overhead, try to schedule SQL Server 2016 jobs during CPU
idle time and slow production periods. For example, you can schedule the job to run every
night at midnight.
Specify the understandable job name.
Try to specify the job’s name so, that the name describes what the job makes. By using so, you
can simplify jobs administering and monitoring. The job name can be up to 128 characters.
Try to separate very large job into several small jobs.
This can improve performance, and can be useful to maintenance the jobs and locate any problems.
Specify the job description.
The job description can help to understand the purpose of the job. The job description can be up
to 512 characters.
Consider creating a single schedule and assign one or more jobs to use this schedule.
By using a single schedule, you can simplify jobs administering. You can use the sp_add_schedule
system stored procedure to create a schedule that can be used by any number of jobs.
In this case, jobs and schedules must have the same owner.
Specify the job category.
You can use the job categories to organize jobs for easy filtering and grouping. Using job
categories can simplify the jobs maintenance. The job category can be up to 128 characters.
When you schedule the job, try to set the frequency interval as large as possible.
For example, do not specify the job step to run every 1 minute if you can set this interval to
1 hour or more without any problems for your business logic.
Call sp_add_jobserver last.
Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly
force SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job
until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last.
Do not use many job steps.
Usually 3-4 steps are enough. The smaller your job will be the easy will be administering and
monitoring. Using less job steps can be useful to locate any job problems.
Try to reduce sending the ‘e-mail’ messages to the operators.
Send ‘e-mail’ messages to the operators during the job’s test or for the very important
jobs only. Because using these messages increase the job’s duration and can be very
resource effective, you should use this feature as rarely as possible.
When you test the job, consider using the output file.
The output file contains the results of the Transact-SQL or CmdExec job step. The information
from the output file can be used to debug any job’s problems.
Use the sp_help_schedule system stored procedure to get information about schedules.
By default, only members of the sysadmin fixed server role and members of the SQLAgentUserRole,
SQLAgentReaderRole, SQLAgentOperatorRole database roles can execute this stored procedure.
Members of SQLAgentUserRole can only view the schedules that they own.
When you test the job, consider turning on the “Append output to step history” checkbox.
When this checkbox is turning on, the results of the Transact-SQL job step will be added
to the history entry for this job step. This can be useful to debug and locate any job’s
problems.
Specify the “Retry attempts” and “Retry interval” job’s option.
If the job step fails and the “Retry attempts” is specified, the job step will run again. The
“Retry attempts” option specifies the number of retry attempts to be made if the step fails. If
you specify the “Retry attempts” you should also specify the “Retry interval” option. The “Retry
interval” option specifies the interval (in minutes) to wait before retrying the step. By default,
the “Retry interval” option is equal to 1 minute.
Use the sp_help_jobactivity system stored procedure to get a list of all active
SQL Server Agent jobs.
By default, only members of the sysadmin fixed server role and members of the SQLAgentUserRole,
SQLAgentReaderRole, SQLAgentOperatorRole database roles can execute this stored procedure and
only members of sysadmin can view the activity for jobs owned by other users.
Try to avoid using Microsoft ActiveX scripting job steps.
Because using ActiveX scripting can be very resource effective, you should avoid using ActiveX
scripting job steps.
Use SQL Server Profiler to monitor and identify the long running jobs.
SQL Server Profiler is a tool that captures SQL Server events. You can use SQL Profiler
to find slow running Transact-SQL statements or stored procedures, which used in a job.
For example, you can run SQL Server Profiler and choose to trace all events relating to
the TSQL and Stored Procedure event classes.