Effective use of the automatic management function of SQL Server

To allow automatic database management, the administrator needs to pre-define predictable management tasks and conditions for sending these 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.

When is the CPU idle? Idle is a relative standard. Sometimes the CPU usage below 30% can be defined as idle; sometimes the CPU usage is less than 60%, that is, idle. This should be taken into consideration based on the application already under the Department of the server configuration. Therefore, before using the CPU idle plan, the Administrator must observe the server for a certain period of time and use Performance Monitor and other tools to monitor server traffic and collect relevant statistics. Then, the collected information is used to analyze the CPU idle standards and perform relevant settings in the database. The idle CPU condition is defined as a percentage. The CPU usage must last for a specified period of time below this percentage. Then, set the duration. If the CPU usage is lower than the specified percentage within the specified time, the SQL Server Agent starts all jobs with a CPU idle time schedule. For example, the Administrator thinks that the re-indexing job can run at a CPU usage of less than 35%, that is, when the re-indexing job is running, the CPU usage is within the tolerable range, in this case, you can set the CPU idle rate to 35%. And the CPU usage lower than 35% must last for a period of time. For example, if the CPU below 35% lasts for a short period of time, the system still does not consider the CPU as idle. It is best to refer to the execution time of a specific job for this duration. The time required for re-indexing.

3. How to stop Automatic Execution of a plan?

Some time may not require automatic execution of the plan. For example, the Administrator does not need these automatic execution plans during database design. To do this, we need to temporarily stop them. What methods can be used to achieve this?

First, the plan and the job can be separated. Planning and homework are two independent contents. For example, full database backup and scheduled time are independent of each other. If you have set a full backup policy for the database, but do not associate the scheduled time with this job, the full backup of the database will not be automatically executed. If you need to execute this job, you need to start it manually. Therefore, if the Administrator does not want a plan for the time being, he or she can remove the plan from the plan.

The second is to disable the job or plan. When the Administrator does not need a job to run automatically, the plan or job can be used out. Disabling or disabling a job will stop a job automatically, but they are still slightly different. If the plan is disabled, the job stops automatically, but the administrator can still execute it manually. However, if a job is disabled, the system does not run automatically, and the administrator cannot manually execute the job. This is like setting a life imprisonment for this assignment. To avoid this problem, we recommend that you disable the plan instead of the job. In this case, the administrator can manually start the job to respond to emergencies when necessary. In addition, if this plan still needs to be used in the future, I do not recommend that you detach the plan from the job to stop the automatic execution of the plan. Instead, we recommend that you disable the plan. Otherwise, you still need to associate the plan with the job when you need it next time, which will increase the workload.

In addition, if this test plan is no longer used, there are two solutions. First, delete the plan directly. This is the most thorough method, but it does not leave any historical records. The second is to first put the plan and work glass, and then disable the plan. This is a little more troublesome, And it will leave junk data in the database. However, historical records can be retained. This may be helpful for subsequent database maintenance. There is no unified standard for removing or disabling unnecessary plans. You still need to judge based on the Operation habits of the database administrator.

No matter which method is used, the Administrator must pay attention to two points. First, the disabled plan must be restarted. If the Administrator only changes the disabled plan policy and does not restart the plan, the administrator needs to manually start the plan. Second, 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. SQL Server 2005 FAQs
2. Three key factors affecting SQL Server performance
3. Tips for creating a local temporary table in SQL Server