SQL Server performance optimization (Partition creation)

Compared with Compression, database partitioning is more complex and cumbersome. In addition, unlike Compression, a partition is an operation that requires long-term maintenance cycle change. Partitioning is to physically cut big data into several independent parts, so that only one or more of them can be retrieved during query.

Compared with Compression, database partitioning is more complex and cumbersome. In addition, unlike Compression, a partition is an operation that requires long-term maintenance cycle change. Partitioning is to physically cut big data into several independent parts, so that only one or more of them can be retrieved during query.

Compared with Compression, database partitioning is more complex and cumbersome. In addition, unlike Compression, a partition is an operation that requires long-term maintenance cycle change.

Partitioning is to physically cut big data into several independent small parts, so that only one or several partitions can be retrieved during the query to reduce the impact of data; in addition, for partitions placed in different file groupsPerformanceIt is also higher than the query for the entire table.Performance.

In fact, SQL Server 2005 already contains the partition function. Even before SQL Server 2005, a function called "Partitioned Views" exists, you can Union a table with the same structure in a View to achieve the same effect as the current partition table. In SQL Server 2008, the partition function has been significantly enhanced, so that we can not only partition tables and indexes,You can also lock the partition instead of the entire table..

Partition Column

Like Compression, SQL Server 2008 also provides a partition wizard interface. In the Enterprise Manager, right-click the table to be partitioned and choose Storage-> Create Partition:

All fields in the table are listed, including the field type, length, precision, and decimal places. You can select any one Column as the partition Column ), it is not just a number or date type. Even string columns can be partitioned alphabetically. The following types of Columns cannot be used for partitioning:Text, ntext, image, xml, timestamp, varchar (max), nvarchar (max), varbinary (max), alias, hierarchyid, spatial index, or CLR user-defined data type. In addition, if you use a calculated column as a partition column, you mustSet as persistent Column(Persisit ).

Below the list, two options are provided:

1. Distribute to available Partition Table:
   This requires that there be another partition table in the same database, and the partition column of the table and the type of the currently selected columnCompletely consistent.
   The advantage is that when two tables are associated in the query and their associated columns are partition columns, using the same partition policy will be more efficient.
2. Change the storage space of Non-unique indexes and unique indexes to be consistent with the index partition column.:
   In this way, all indexes in the table are partitioned together to achieve "alignment ". This is an important and troublesome option. For specific requirements, see MSDN (special guidelines for partitioned indexes ).
   The advantage is that the table and the index partition are consistent. On the one hand, it is more efficient to use the index during queries, andMove into/out PartitionIt will also be more efficient.

Note: It is recommended thatUse clustered index columns as partition Columns. On the one hand, the index structure should be related to the query, so the consistency between the partition column and the index will ensure the maximum efficiency of the query; on the other hand, ensuring index alignment and clustered index alignment is a prerequisite for smooth migration and removal of partitions. Otherwise, migration may fail, migration and removal of partitions are also an important strategy for managing big data --Sliding Window(SlideWindow) basic operation of the policy.

Partitioning functions and partitioning Solutions

After the partition column is selected, if the option "distribute to available Partition Table" is not applied, select \CreatePartition functionsAndPartition Scheme. The partition function specifies the partition boundary, while the partition scheme plans the file groups stored by each partition.

The wizard interface is as follows:

Left boundary indicates that the boundary value of each partition is included in the Left-side partition of the boundary value, that is, the data constraint in each partition is<= Specified Boundary ValueRight boundary indicates that the boundary value of each partition is included in the partition on the Right of the boundary value. <指定的边界值< strong> .

The list below lists the existing partitions under the current partition scheme. The Filegroup specifies the location where each partition is stored. If the partition is placed in different file groups on different disks, the read/write operations on different disks do not affect each other, this improves the efficiency of parallel processing of Partitioned Tables. Generally, it is safer to place all partitions in the same file group. For more information about file groups, see SQL Server Filegroups.

Note: HereThe last partition has no specified boundary.Is used to save all> (Left Boundary) or> = (Right boundary) data of the last partition Boundary.

If you select a time field as the partition column, you can use the Set button to implement conditional grouping:

This makes it easy to set the start time and End Time to automatically partition the table according to the specified time period, but you still need to manually specify the file group for each partition.

After creating a partition scheme, you can use Estimate sotrage to Estimate the number of rows and space usage of each partition. However, unless you need to plan your partition policy based on the occupied space or number of rows, generallyIt is not recommended to estimate hereBecause for empty tables, the estimated results are of course 0, and if the table already contains a large amount of data, the estimation takes a long time.

CreatePartition

Through the above settings, the partition has been basically completed. At the end of the wizard, you can selectCreateThe script still executes the partition operation immediately.

We can see that in different situationsCreatePartition script:

1. If the table does not have an index:

BEGIN TRANSACTIONCREATE PARTITION FUNCTION [TestFunction](datetime) AS RANGE LEFT FOR VALUES (N'2010-01-01T00:00:00', N'2010-02-01T00:00:00', 
N'2010-03-01T00:00:00', N'2010-04-01T00:00:00', N'2010-05-01T00:00:00', N'2010-06-01T00:00:00')CREATE PARTITION SCHEME [TestScheme] AS PARTITION [TestFunction] TO ([PRIMARY], [PRIMARY], [PRIMARY], 
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])CREATE CLUSTERED INDEX [ClusteredIndex_on_TestScheme_634025264502439124] ON [dbo].[Account] (    [birthday])WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [TestScheme]([birthday])DROP INDEX [ClusteredIndex_on_TestScheme_634025264502439124] ON [dbo].[Account] WITH ( ONLINE = OFF )COMMIT TRANSACTION

Here firstCreatePartition Function and Partition Scheme, and then in the Partition ColumnCreateClustered indexes are partitioned according to the partitioning scheme, and the index is deleted.

2. If the table has an index:

If no clustered index exists:

CREATE CLUSTERED INDEX [ClusteredIndex_on_TestScheme_634025229911990663] ON [dbo].[Account] (    [birthday])WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [TestScheme]([birthday])DROP INDEX [ClusteredIndex_on_TestScheme_634025229911990663] ON [dbo].[Account] WITH ( ONLINE = OFF )

This is the same as the case where no index exists. If a clustered index exists in the table, the script changes:

CREATE CLUSTERED INDEX [IX_id] ON [dbo].[Account] (    [id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [TestScheme]([birthday])

We can see that the original clustered index (IX_id) is rebuilt in the Partition Scheme.

If you select"Alignment Index"Option, partitions will be applied to all indexes:

CREATE CLUSTERED INDEX [IX_id] ON [dbo].[Account] (    [id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [TestScheme]([birthday])CREATE NONCLUSTERED INDEX [UIX_birthday] ON [dbo].[Account] (    [birthday] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [TestScheme]([birthday])CREATE NONCLUSTERED INDEX [UIX_name] ON [dbo].[Account] (    [name] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

The clustered index IX_id is partitioned as well as non-clustered index UIX_name and UIX_birthday.

Notes

1. After a table is partitioned, it cannot be partitioned again.No method for directly canceling table partitions.
2. If you want to view the partition status of a partitioned table and the number of rows and occupied space in each partition, you can use Storage-> Management Compression to view the status. You can also specify the compression mode for each partition here.
3. If the Partition Table index is not alignedThe table cannot be switched in/out, And the Sliding Window operation cannot be performed..
4. Partitions are actually added with constraints in each partition table and corresponding insert operationsPerformanceIt will also be affected.
5. Even if a partition is performed, if the query condition field is not associated with the partition column,PerformanceIt may not be upgraded.