SQL Server Automated management partition design scheme

First, design instructions

The purpose of this automation is to use a fixed number of partitions (partition number 01~05) to save data alternately and repeatedly, and when the last partition is full, we will empty out the partition of the oldest data partition, and the new data can use the old partition space.

The environment in which this automated management partition is applied is limited by the fact that the partition's data is incremented, such as a partition field is an ID value, or a date as a partition; second, you can accept the problem of historical data being removed from partitioned tables. Third: The number of days into the library should not be greater than the Part_value and Change_value in the Partition management table Partitionmanage, because the frequency of our job execution is 1 days, but you can adjust the change_value or the execution frequency of the job;

Specific scripts can refer to: SQL Server 2005 Automated Delete table partition design scheme

Two, look at the picture to speak

(Figure 1: Overall concept map)

Data flow through the partition scheme, is assigned to different partitions, from the figure can be seen, partitions can be reused, the background has a so-called automated switching of the partition of the job running, the purpose is to reuse these partitions. The primary purpose here is to show that it has a lateral relationship with other filegroups, and we also use primary when making swap partitions, and we need to allocate enough space beforehand.

(Figure 2: Automated design diagram)

This is the logical processing of automatic switching partitioning, in which the design of the partition management table is more important, and its flexibility is related to the effect of the whole automation; This logic has the following characteristics:

1. The index of the partition is aligned to the storage location, and the other indexes are created with the partitioning scheme, and the index data is stored in the partition along with the partition data;

2. partition management table, including the number of zoning records early warning design, after the ID reached this value will be exchanged partition;

3. The Partition management table, the data of the Filegroup_string field can be generated by SQL script Automation, the condition is that the partition file group name needs to have the rule;

4. Temporary tables are created on the primary primary partition, using the same partitioning scheme as the original table, requiring that the primary be allocated a space that is greater than or equal to the size of a partition file, so that when exchanging partitions, the data space is not allocated as the primary partition;

5. Exchange old data to a temporary table, use the following statement to exchange data to the same partition number, so that the temporary table is a history table, and the advantage is that the history table also uses the partition.

ALTER TABLE [TB] SWITCH PARTITION @PARTITION_num to [TEMP_TB] Partition@partition_num

6. You need to modify the partitioning scheme before you can modify the partition function, which is exactly the opposite of the order in which you create the partition function and the partitioning scheme.

(Figure 3: Partition management table Partitionmanage)

Field Description: Change_value (Alert ID value) Part_value (partition function value) filegroup_string (partition file group name) Isdone (status) updatetime (update time);

This is the Partition management table (Partitionmanage), it is after a few versions to determine the field, now it is more perfect, can deal with a lot of situations:

1. For example, we can modify the early warning value (change_value), so that the data into the Exchange zone early;

2. For example, we can modify the partition value (Part_value) to adjust the partition interval;

3. For example, we can modify the partition file group name (filegroup_string), to achieve the purpose of the skip file group, by modifying the partition management table to set the corresponding relationship between the partition value and the partition file group;

4. For example, once we have modified the partitioning scheme and partition function, we have gone to the very later partition value, then we can only set the status of these partition values (Isdone) is 1 (True) to resolve.

5. Record the time of the Exchange partition (UpdateTime), convenient to inquire;

(Figure 4: The record distribution for the partition ID field)

This is a real-life partitioning situation, such zoning characteristics is that the number of records in the partition is basically flat, in the partition_num=20 record significantly more records, this is because we did not make a timely exchange of partitions caused.

(Figure 5: The record distribution of the partition to the ClassID (category) field)

This is also the real data in another production environment, the feature of this zoning is that the number of records in the partition is not very equal, and we need to do is by dividing the classid values in each partition to try to balance the number of records in the partition, so you can see that the difference between the minimum and maximum span is relatively large.

(Source: Blog Park Author: Listen to the wind blowing rain)