SQL Server databases automatically execute management tasks

When the specified conditions are met, the database automatically runs the operations specified by the Administrator. When automatic management is used to process daily transactions, administrators can take time to focus on other things, such as database optimization and other more valuable jobs.

A scheduler is a member of the automated management component. The Schedule specifies the job running time. Multiple jobs can be run according to one plan, and multiple plans can also be applied to one job. However, this time is quite different from what we usually do. This time not only indicates a specific date or time, but also indicates a specific time. For example, when the SQL Server Agent is started or the CPU usage of the server is defined as idle. That is to say, the time schedule mentioned here can also implement certain conditional state control. In short, a scheduled job is a condition that allows the job to start running without interaction, including the specific date or specific condition. You can set a job plan to run automatically by creating a new plan for a job or adding an existing plan to a job. However, when setting an automatic execution plan, you must pay attention to one problem. That is, only one job instance can be run at a time. If you attempt to manually run the job as scheduled, the SQL Server Agent rejects the request. In addition, even if the plan is not enabled, the job can still run in response to the alarm, or manually run by the user. If a job plan is not enabled, no job that uses the plan will enable the plan.

1. Scheduled trigger time and events.

As mentioned above, the plan can be triggered based on specific time and events. So what are the specific time and events? I usually use the following situations most.

First, run the task on a specific date and time. This scheduled time is particularly useful in database backup. As we all know, database backup will occupy a large amount of system resources. If you back up the database server during peak usage periods (such as daytime working hours), the user access will be greatly affected. For this reason, I put the database backup time after. In this period, few users access the database. In this case, the administrator can use the scheduler function to back up the database after every day. This is a typical application based on specific date and time plans.

The second is the execution of the recurring plan. This is similar to running a specific date and time. It is only run once in the above case. This execution plan is repeated multiple times. For example, database backup is performed every 12 o'clock every night. In actual work, a specific date and time is usually only for some special situations. Today, tiangang has made major changes to the database structure. For example, if several tables or views are added, a full backup is required for the database. If you are not in a rush to back up the database, you can create a full backup plan for the database running on a specific date and time so that it can back up the database in the afternoon. However, for regular database backup, you need to run it according to the repeated execution plan.

Third, when the CPU usage of a computer is in the defined idle state. That is to say, the current CPU usage of the database server is relatively low, and other complicated management tasks can be executed. For example, some database systems have an inventory replenishment point during design. When the inventory is lower than the safety stock, the system will automatically generate a material requisition to meet the safety stock requirements. Because this table is designed with many materials, it will occupy a large amount of system resources. Therefore, you can set to run once a day, and the running time is determined when the CPU usage is low. This reduces the negative impact on other jobs.

Ii. Typical applications of CPU idle plans.

Many database administrators are familiar with other types of plans. What I want to talk about today is the application of the CPU idle plan. This plan can greatly improve the database performance if the application is good. If you want to make good use of this function, you need to answer the following two questions.

First, what jobs need to use CPU idle schedule? This question is hard to answer. Theoretically, any job can be automatically executed using the CPU idle schedule. But they have different effects. Some jobs cannot greatly improve performance even if they adopt a CPU idle schedule. Simply put, some jobs that occupy a large amount of CPU resources use the CPU space planning rules for automatic execution, which can achieve better results. If the database sometimes needs to re-generate the index, this operation will occupy a large amount of CPU resources. As the data volume increases, the CPU usage increases. To this end, if you can re-create an index job in the idle time of the CPU, the performance of the database will be improved significantly. In short, when a job is complex and may require a large amount of CPU or memory resources, you can use this CPU idle plan.