SQL Server automated partition management design scheme

I. Design Description

The purpose of this automation is to use fixed partitions (partition numbers 01 ~ 05) to save the data. When the last partition is full, we will clear the data in the oldest partition and use the old partition space for the new data.

There are some restrictions on the environment where partitions are automatically managed by the application. One is that the partition data is displayed progressively, for example, the partition field is the auto-increment Id value, or the date is used as the partition. The other is: we can accept the problems caused by the removal of partition tables from historical data. Third, the number of database entries in a day should not be greater than the difference between Part_Value and Change_Value in the partition management table PartitionManage. Because the execution frequency of our jobs is one day, you can adjust the Change_Value or job execution frequency;

For specific scripts, refer to: SQL Server 2005 automatic table partition deletion Design Scheme

2. view pictures and talk

(Figure 1: Overall Concept diagram)

The data stream is allocated to different partitions through the partitioning scheme. It can be seen that partitions can be reused. A job called automatic partition switching is running in the background, the purpose is to reuse these partitions. The purpose of PRIMARY here is to describe a level-1 Relationship between it and other file groups, and we will also use PRIMARY when performing swap partitions, so we need to allocate enough space in advance.

(Figure 2: Automated Design Drawing)

This is the logic processing for automated partition jobs. The design of partition management tables is important, and its flexibility is related to the overall automation effect. This logic has the following features:

1. The storage location alignment of the index of the partition. Other Indexes use the partition scheme when they are created, and the index data is stored in the partition along with the partition data;

2. The partition management table contains the partition Record Number warning design. After the Id reaches this value, the partition is switched;

3. In the partition management table, data in the FileGroup_String field can be automatically generated using an SQL script, provided that the partition file group name needs to be regular;

4. A temporary table is created on the PRIMARY partition and uses the same partition scheme as the original table. You need to allocate a space greater than or equal to the size of a partition file to the PRIMARY in advance, in this way, you do not need to incrementally allocate data space to the primary partition during partition swap;

5. to exchange old data to a temporary table, use the following statement to exchange the data to the same partition number. In this way, the temporary table is a historical table, the benefit is that the history table also uses partitions.

Alter table [tb] switch partition @ PARTITION_num TO [Temp_tb] PARTITION @ PARTITION_num

6. You need to modify the partition scheme before modifying the partition function. This is in the opposite order of creating the Partition Function and the partition scheme.

(Figure 3: partition management table PartitionManage)

Field Description: Change_Value (warning 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 determines the field after several versions. Now it is quite complete and can cope with many situations:

1. For example, we can modify the warning value (Change_Value) so that the data enters the swap partition early;

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

3. For example, you can modify the partition file group name (FileGroup_String) to skip the file group. You can modify the partition management table to set the correspondence between the partition value and the partition file group;

4. for another example, if we modify the partition scheme and partition function at one time and have already reached the very next partition value, we only need to set the status (IsDone) of these partition values to 1 (True) you can solve the problem.

5. records the time (UpdateTime) of the SWAp partition for convenient query;

(Figure 4: record distribution chart with the partition Id field)

This is a partition situation in practice. This partition feature is that the number of records in the partition is basically the same, and there are obviously many more records in the records with Partition_num = 20, this is because we did not swap partitions in time.

(Figure 5: record distribution chart with the partition as the ClassId field)

This is also the real data in another production environment. This partitioning method is characterized by the fact that the number of records in the partition is not equal, what we need to do in the early stage is to divide the value of ClassId in each partition to balance the number of records in the partition as much as possible, so we can see that the difference between the minimum and maximum spans is relatively large.

(Source: blog Author: Listening to wind and rain)