在关系型 DB中，分区表经常使用DateKey（int 数据类型）作为Partition Column，每个月的数据填充到同一个Partition中，由于在Fore-End呈现的报表大多数是基于Month的查询，按照Month分区的设计能够提高查询性能，但是，如果，前任DBA没有创建Schedule来维护Partition Function，不能增加新的Partition，所有新增加的数据都会插入到最后一个Partition中，导致最后一个Partition填充的数据量十分大，降低了分区表的查询性能。

 

一，最佳实践（Best Practices ）

 

微软建议，避免对已填充的分区执行split或merge操作。在分区表的两端都保持空的分区（Empty Partition），第一个分区和最后一个分区是Empty Partition。通过对尾端的Empty Partition进行Split操作，就能在尾端增加分区，并且不会产生数据移动；当将数据从前端的第二个分区中归档后，第一个分区和第二个分区都是Empty Partition，Empty Partition进行Merge操作，不会产生数据的移动。

 

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

 

因此，受此Best Practices的影响，DB开发人员在创建分区表时，一般的做法是只创建特定数量的分区，并且只保持两端是Empty Partition，例如：

 

复制代码

CREATE PARTITION FUNCTION [funcPartition_DateKey](int) 

AS RANGE RIGHT 

FOR VALUES (20100701, 20100801, <.....> , 20150501, 20150601)

GO

 

CREATE PARTITION SCHEME [schePartition_DataKey] 

AS PARTITION [funcPartition_DateKey] 

TO ([PRIMARY], <....>, [PRIMARY])

GO

复制代码

改进建议：在SQL Server 2012中，在一个Table或索引上，最多能够创建15 000个分区（In SQL Server 2012, a table or index can have a maximum of 15,000 partitions），未雨绸缪，我们可以按照月份，一次性创建360个分区，每月一个分区，每年12个分区，服务30年，一劳永逸，当然，也可以创建Schedule，前提是，你必须保证Schedule按时执行成功。

 

如果，必须要对已填充的分区表，增加分区，要怎么做？

 

方法1，直接修改Partition Function，通过拆分末端Partition来增加分区

 

由于很多 Big Table 使用相同的Partition Schema进行分区，简单地从末端Partition为起点，逐个增加分区，在短时间内会产生海量的Disk IO操作，对系统产生极大的影响，例如

 

复制代码

declare @DateKey int 

set @DateKey=20150701

 

while @DateKey<20200101

begin

　　alter partition scheme [schePartition_DataKey]

　　Next Used [primary];

 

　　alter partition function [funcPartition_DateKey]()

　　split range(@DateKey);

 

　　set @DateKey=cast(convert(nvarchar(8),dateadd(month,1,cast(cast(@DateKey as nvarchar(8)) as date)),112) as int);

end 

复制代码

方法2，更改分区表的Partition Schema

 

创建新的Partition function 和 Partition Schema，逐个Table修改其Partition Schema，这个方法（Workaround），虽然实现过程比较繁琐，但是对系统性能的副作用最小，将影响控制在当前操作的Target Table。

 

Script1，创建新的Partition设计

 

复制代码

--create Partition function

declare @DateKeyList varchar(max)

declare @DateKey int 

--set initial DateKey

set @DateKey=20140701;

 

while @DateKey<20200101

begin 

    set @DateKeyList=coalesce(@DateKeyList+','+cast(@DateKey as varchar(8)),cast(@DateKey as varchar(8)))

    --Increase iterator

    set @DateKey=cast(convert(nvarchar(8),dateadd(month,1,cast(cast(@DateKey as nvarchar(8)) as date)),112) as int);

end

--print DateKey List

--select @DateKeyList

declare @sql nvarchar(max)

set @sql=N'

CREATE PARTITION FUNCTION [funcPartition_new_DateKey](int) 

AS RANGE RIGHT 

FOR VALUES ('+@DateKeyList+N');'

 

EXEC sys.sp_executesql @sql

GO

--create partition schema

CREATE PARTITION SCHEME [schePartition_new_DataKey] 

AS PARTITION [funcPartition_new_DateKey] 

all TO ([PRIMARY]);

GO

复制代码

Script2，逐个更新Table的Patition Schema

 

由于Patition Table中，可能存在不止一个Index，在rebuild table时，使用 drop_existing=on 能够减少分区表上nonclustered indexes的重建时间。

 

复制代码

--rebuild table

create unique clustered index [PK__SchemaName_TableName_KeyColumn]

on SchemaName.TableName([KeyColumn],[CreatedDateKey])

with(data_compression=page,drop_existing=on)

on [schePartition_new_DataKey]([CreatedDateKey]);

 

--rebuild columnstore index

CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_ColumnStore_SchemaName_TableName] 

ON [SchemaName].[TableName]

(

    column list....

)

复制代码

三，在同一个文件组中创建分区

 

在一个文件组中创建表的所有分区，每个分区在物理上都是独立的存储对象，只不过这些独立的存储对象位于同一个FileGroup。

 

1，创建Patition Schema时，使用 ALL 关键字指定只能指定一个FileGroup，所有的Partition 都创建在同一个FileGroup上；在Patition Schema创建成功之后，默认会将该FileGroup标记为Next Used

 

ALL Specifies that all partitions map to the filegroup provided in file_group_name, or to the primary filegroup if [PRIMARY] is specified. If ALL is specified, only one file_group_name can be specified.

 

If [PRIMARY] is specified, the partition is stored on the primary filegroup. If ALL is specified, only one file_group_name can be specified. Partitions are assigned to filegroups, starting with partition 1, in the order in which the filegroups are listed in [,...n]. The same file_group_name can be specified more than one time in [,...n].

 

2，在Patition schema中，只有一个FileGroup会被指定（Mark）为Next Used

 

如果FileGroup被指定为Next Used，意味着分区表的下一个分区将会创建在该FileGroup上。在创建Patition Schema时，指定 ALL关键字，不仅指定将表的所有分区都创建在同一个FileGroup上，而且，还将该FileGroup指定为Next Used。

 

If ALL is specified, the sole file_group_name maintains its NEXT USED property for this partition_function_name. The NEXT USED filegroup will receive an additional partition if one is created in an ALTER PARTITION FUNCTION statement. 

 

3，在执行Patition Split 操作时，必须存在一个FileGroup被指定为Next Used，否则，Split 操作失败

 

在指定Next Used时，要注意：

 

在创建Partition Scheme时，指定ALL关键字，只会将一个FileGroup指定为Next Used；

可以多次指定Next Used，最后一次指定的FileGroup是Next Used；

一个FileGroup可以被多次指定为Next Used；即使该File Group已经用于存储（Hold）分区的数据； A filegroup that already holds partitions can be designated to hold additional partitions. 

在完成一次Partition Split操作之后，之前的Next Used 已被使用，当前不存在被指定为Next Used的FileGroup，必须显式指定一个FileGroup作为新的Next Used，才能继续执行Partition Split操作；

If you create all the partitions in the same filegroup, that filegroup is initially assigned to be the NEXT USED filegroup automatically. However, after a split operation is performed, there is no longer a designated NEXT USED filegroup. You must explicitly assign the filegroup to be the NEXT USED filegroup by using ALTER PARITION SCHEME or a subsequent split operation will fail.

 

显式将一个FileGroup 指定为 Next Used

 

ALTER PARTITION SCHEME partition_scheme_name 

NEXT USED [ filegroup_name ] [ ; ]

 

 

Appendix

 

使用Alter Partition Function 命令执行拆分或合并分区的操作，每次操作，只能拆分一个，或合并一个分区：

 

ALTER PARTITION FUNCTION partition_function_name()

{ 

    SPLIT RANGE ( boundary_value )

  | MERGE RANGE ( boundary_value ) 

} [ ; ]

ALTER PARTITION FUNCTION repartitions any tables and indexes that use the function in a single atomic operation. However, this operation occurs offline, and depending on the extent of repartitioning, may be resource-intensive.

 

 

 

参考doc：

 

Rebuilding Existing Partitioned Tables to a New Partition Scheme

 

ALTER PARTITION FUNCTION (Transact-SQL)

 

ALTER PARTITION SCHEME (Transact-SQL)

 

作者：悦光阴

出处：http://www.cnblogs.com/ljhdo/

本文版权归作者和博客园所有，欢迎转载，但未经作者同意，必须保留此段声明，且在文章页面醒目位置显示原文连接，否则保留追究法律责任的权利。