SQL Server Database automates administrative tasks _mssql

When these specified conditions are met, the database automatically runs the actions specified by the administrator. Using automated management to handle day-to-day transactions allows administrators to take the time to focus on other things, such as database optimization, and more valuable jobs.

A plan is a member of an automation management component. The schedule specifies when the job runs. Multiple jobs can be run according to one schedule, and multiple schedules can be applied to one job. But this time is a little bit different from what we normally do. This time not only refers to a specific date or time, but also represents a specific time. such as when the SQL Server Agent is started or when the server's CPU usage is at a defined idle state level. In other words, the time plan referred to here can also achieve a certain condition state control. In short, a planning job is defined as a condition that allows a job to start running without the ability to interact with each other, including specific dates or specific situations. You can set the job schedule to run automatically by creating a new schedule for the job or by adding an existing plan to a job. However, when you set up an automatic execution plan, you need to be aware of a problem. That is, only one job instance can be run at a time. If you try to run the job manually when the job is scheduled to run, the SQL Server Agent rejects the request. Also, a job can be run in response to an alert, or run manually by a user, even if the schedule is not enabled. If the job schedule is not enabled, any jobs that use the schedule will not have the plan enabled.

time and event that the plan triggers.

As I said above, the plan can be triggered based on a specific time and event. So what are these specific times and events? The author usually uses the most is the following several situations.

One is a specific date and time to run once. This scheduling time is especially useful when backing up a database. As you all know, database backups can consume more system resources. If you back up your database server during peak hours, such as daytime hours, you will have a significant impact on your users ' access. For this reason, I put the database backup time after 12 o'clock in the evening. This time period is relatively rare for users to access the database. At this point, the administrator can take advantage of the scheduling feature to make a database backup after 12 points per day. This is a typical application based on a specific date and time plan.

The second is a repetitive execution of the plan run. This is similar to running at a specific date and time as mentioned above. Only the above situation runs only once. This run on a recurring schedule means that it will run repeatedly. such as a database backup every night 12 o'clock, and so on. In actual work, a particular date and time is often a new one only for special situations. Today, days have changed the structure of the database, such as the addition of a few tables or views, this time need to make a full backup of the database. As long as you are not in a hurry to backup, you can establish a specific date and time to run the database full backup plan, so that the database backup in the afternoon. However, for regular database backups, this is required to run as a recurring execution plan.

The third is when the CPU usage of the computer is at a defined idle state level. That is, the database server is currently running low on CPU usage and can perform other more complex administrative tasks. If some database system in the design, there is an inventory replenishment point operation. When the inventory is lower than the safe stock, the system will automatically generate a purchase order to meet the needs of safety inventory. Since this table is designed to have more materials, it will consume more system resources. It can be set to run once a day, and the running time is set at a lower CPU utilization rate. This can reduce the negative impact on other operations.

second, the typical application of CPU idle plan.

Several other types of planning are already well known to many database administrators. The author wants to talk about the application of CPU idle plan today. This program, if applied well, can greatly improve the performance of the database. If you want to take advantage of this feature, you need to answer the following two questions.

What do I need to use CPU idle plan for? This question is more difficult to answer. Theoretically, any job can be performed automatically using a CPU idle schedule. It's just that they bring different effects. Some jobs do not have a significant performance improvement, even if they use CPU idle planning. In simple terms, some of the CPU-intensive operations, the use of CPU space planning rules to automate, can achieve better results. If the database sometimes needs to regenerate the index, this work will take up more CPU resources. And as the volume of data increases, this CPU usage will become more and more high. To do this, if you can put the regeneration index of the job, in the CPU idle time to do, then to improve the performance of the database is more obvious. In short, when a job is more complex, you may need to occupy more CPU or memory resources, you can use this CPU idle plan.