SQL Server partition (top)

Original: SQL Server partition (top)

Division development History

The table-based partitioning feature provides flexibility and better performance for simplifying the creation and maintenance of partitioned tables. Functions that trace back to the logical partition table and the manual partitioning table.

Two. Why do you want to partition

To improve the scalability and manageability of large tables and tables with various access patterns.

Large tables, in addition to the size measured in hundreds of GB or even terabytes, can be data tables that cannot be run as expected, running costs or maintenance costs exceeding the predetermined requirements. For example, performance issues, blocking issues, backups.

Three. The concept of partitioning Partition Range

A partition scope is defined as a partitioned boundary condition in the table to be partitioned, based on the key fields in the Business selection table.
After partitioning, the exact location of the data is critical so that only the appropriate partitions are accessed when needed.

Note Partitioning refers to the logical separation of data, not the physical location of the data on disk, the location of the data is determined by the filegroup, so it is generally recommended that a partition correspond to a filegroup.

Partition key

In my demo below, there is an inventory table, and I chose Upbymemberid (member ID) as the partition key. The first step in partitioning tables and indexes is to define the key data for the partition.

Index Partition

In addition to partitioning a table's dataset, you can partition the index, and partitioning the table and its indexes using the same function can typically optimize performance
In the sixth step below, you create a partitioned index.

Three. Create a partition implementation

Add four filegroups to the test library to store the data for each partition, where four filegroups correspond to four partitions

Multiple filegroups are designed to help optimize performance and maintenance, and data should be detached using filegroups. The number of filegroups is partly determined by the hardware resources: In general, the number of filegroups is preferably the same as the number of partitions.

And these filegroups are usually located on different disks (the demo has a limited set of logical disks to store on only one disk).

1 --Step One: Create four filegroups2 Alter DatabaseTestAddFilegroup ByIdGroup13 Alter DatabaseTestAddFilegroup ByIdGroup24 Alter DatabaseTestAddFilegroup ByIdGroup35 Alter DatabaseTestAddFilegroup BYIDGROUP4

--Step two: Create four ndf files, corresponding to each filegroup, filename file storage pathALTER DATABASETestADD FILE(NAME='File1', FILENAME='C:\Program Files\Microsoft SQL Server\mssql10. MSSQLSERVER\MSSQL\DATA\TESTFILE1.NDF', SIZE=5mb,filegrowth=5MB) toFILEGROUP ByIdGroup1ALTER DATABASETestADD FILE(NAME='File2', FILENAME='E:\TESTFILE2.NDF', SIZE=5mb,filegrowth=5MB) toFILEGROUP ByIdGroup2ALTER DATABASETestADD FILE(NAME='File3', FILENAME='E:\TESTFILE3.NDF', SIZE=5mb,filegrowth=5MB) toFILEGROUP ByIdGroup3ALTER DATABASETestADD FILE(NAME='File4', FILENAME='E:\TESTFILE4.NDF', SIZE=5mb,filegrowth=5MB) toFILEGROUP BYIDGROUP4

When the execution is complete, look as shown:

-- Step Three: Create partition functions (boundary values for each partition)

Number of products per member count

--record:126797 Partition1--primarySELECT COUNT(1) fromDbo. ProductWHEREUpbymemberid<=1740--record:90882 Partition2SELECT COUNT(1) fromDbo. ProductWHEREUpbymemberid>1740  andUpbymemberid<= the--record:4999999 Partition3SELECT COUNT(1) fromDbo. ProductWHEREUpbymemberid> the  andUpbymemberid<=9708--record:4999999 Partition4SELECT COUNT(1) fromDbo. ProductWHEREUpbymemberid>9708  andUpbymemberid<=9709--record:2018464 Partition5---ByIdGroup4SELECT COUNT(1) fromDbo. ProductWHEREUpbymemberid>9709CREATEPARTITIONFUNCTIONPf_upbymemberid (int)  asRANGE Left  for VALUES(1740, the,9708,9709)

After execution, as shown:

--Fourth step: Create a partition scheme

CREATE PARTITION SCHEME Ps_upbymemberid  as  to ([PRIMARY][ByIdGroup1],[ByIdGroup2 ],[ByIdGroup3],[ByIdGroup4])

After execution, as shown:

--Fifth step: Create a partitioned table

Right-click the table you want to partition--Save--Create partition--select partition column (here Upbymemberid)--Select partition function

--sixth step to create a partitioned index

CREATE nonclustered INDEX [Ixupbymemberid]  on [dbo].[Product] (    [Upbymemberid] ASC) INCLUDE ([Model]) with(Pad_index= OFF, Statistics_norecompute= OFF, sort_in_tempdb= OFF, Ignore_dup_key= OFF, drop_existing= OFF, ONLINE= OFF, Allow_row_locks=  on, Allow_page_locks=  on)GO

-- Finally,  see how many rows of data are in each partition select $PARTITION. Pf_upbymemberid ([upbymemberid]  as Patition,COUNT(* from dbo.productGroup by $ Partition.pf_upbymemberid ([upbymemberid])

Isolate five partitions (with primary partition), and the number of partitions

Finally see if a partitioned index is used

Benefits of SQL Server partitioning:

1. When tables and indexes become very large, partitions can divide data into smaller, easier-to-manage sections.
2. Reduce index maintenance time.
3. The common where condition field is better to partition on.
4. Parallel operations get better performance and can improve performance for large-scale operations in very large datasets, such as millions of of rows.
5. In general, the number of filegroups is preferably the same as the number of partitions. Filegroups allow you to place individual tables on different physical disks

SQL Server partition (top)