图片 2

Partition4:扩大分区

--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
  • partition_scheme_id
    :ID of the partition-scheme that is partitioning to the data space.
                     
  • destination_id
    :ID (1-based ordinal) of the destination-mapping, unique within the
    partition scheme.     
  • data_space_id
    :ID of the data space to which data for this scheme’s destination
    is being mapped.

假若FileGroup被内定为Next
Used,意味着分区表的下八个分区将会创立在该FileGroup上。在创造Patition
Schema时,钦赐ALL关键字,不仅仅钦赐将表的具备分区都创造在同一个FileGroup上,并且,还将该FileGroup内定为Next
Used。

在SQL Server中,为Partition Scheme数十次点名Next
Used,不会出错,最终一回钦命的FileGroup是Partition Scheme的Next
Used,提出,在实施Partition Split操作早前,都要为Partition
Scheme钦命Next Used。

在三个文本组中创制表的具有分区,各样分区在情理上都以单身的仓库储存对象,只可是这一个独立的存款和储蓄对象位于同三个FileGroup。

select ps.name as PartitionSchemeName,
    ps.data_space_id as PartitionSchemeID,
    pf.name as PartitionFunctionName,
    ps.function_id as PartitionFunctionID,
    pf.boundary_value_on_right,
    dds.destination_id as PartitionNumber,
    dds.data_space_id as FileGroupID
from sys.partition_schemes ps
inner join sys.destination_data_spaces dds
    on ps.data_space_id=dds.partition_scheme_id
inner join sys.partition_functions pf
    on ps.function_id=pf.function_id
where ps.name='PS_int_Left'

风度翩翩,最好实施(BestPractices )

3,在split partition在此之前,必得选取alter partition scheme 内定一个Next
Used FileGroup。假如Partiton Scheme未有一点名 next used
filegroup,那么alter partition function split range command
实行停业,不改造partition scheme。

ALTER PARTITION FUNCTION
(Transact-SQL).aspx)

select ps.name as PartitionSchemeName,
    ps.data_space_id as PartitionSchemeID,
    pf.name as PartitionFunctionName,
    ps.function_id as PartitionFunctionID,
    pf.boundary_value_on_right,
    dds.destination_id as PartitionNumber,
    dds.data_space_id as FileGroupID
from sys.partition_schemes ps
inner join sys.destination_data_spaces dds
    on ps.data_space_id=dds.partition_scheme_id
inner join sys.partition_functions pf
    on ps.function_id=pf.function_id
where ps.name='PS_int_Left'

在关系型 DB中,分区表日常应用DateKey(int 数据类型)作为Partition
Column,每一种月的数码填充到同三个Partition中,由于在Fore-End展现的报表大非常多是依据Month的查询,依据Month分区的设计能够巩固查询质量,不过,纵然,前任DBA未有创立Schedule来保险Partition
Function,不可能增添新的Partition,全体新扩张的数量都会插入到结尾八个Partition中,导致最终三个Partition填充的数据量超级大,裁减了分区表的询问质量。

--merge range
ALTER PARTITION FUNCTION pf_int_Left ()
merge range (20);

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.

只是,SQL Server是不是提供metadata,查看Partiton Scheme是或不是钦命Next Used
FileGroup?答案是系统视图:sys.destination_data_spaces。假使存在FileGroup被钦命为Next
Used ,那么视图重临的Partition的个数会比Partition
Function划分的分区数量多1个。

三,在同二个文书组中创设分区

select pf.name as PartitionFunctionName,
    pf.function_id,
    pf.type,
    pf.type_desc,
    pf.boundary_value_on_right,
    pf.fanout,
    prv.boundary_id,
    prv.value
from sys.partition_functions pf
inner join sys.partition_range_values prv
    on pf.function_id=prv.function_id
where pf.name='pf_int_Left'

开立异的Partition function 和 Partition Schema,每个Table改过其Partition
Schema,这一个办法(Workaround),纵然实现进程比较繁琐,不过对系统质量的副功能最小,将影响决定在脚下操作的Target
Table。

能够看到,多了一个partition,partition number=4,存放的FileGroupID=2。

3,在执行Patition Split 操作时,必需存在二个FileGroup被钦定为Next
Used,不然,Split 操作失败

4,假使检查 Partiton Scheme是还是不是钦赐Next Used FileGroup?

由于许多 Big Table 使用形似的Partition
Schema举办分区,简单地从背后Partition为起源,每个扩充足区,在长时间内会发出海量的Disk
IO操作,对系统发生相当大的熏陶,例如

上述脚本再次来到3个partition,表达未有next used filegroup。

 

图片 1

微软提出,制止对已填写的分区实施split或merge操作。在分区表的两侧都保持空的分区(Empty
Partition),第二个分区和末段五个分区是Empty
Partition。通过对尾端的Empty
Partition举行Split操作,就可以在尾端扩丰硕区,而且不会生出多少移动;当将数据早先端的第贰个分区中归档后,第八个分区和第一个分区都以Empty
Partition,Empty Partition进行Merge操作,不会发生多少的运动。

  • **partition_scheme_id :**是数据表存储的半空中,该空间不是现实的有个别FileGroup。普通的表独有三个分区,只可以存款和储蓄在单个FileGroup中,然则,通过Partition
    Scheme,将表数据分割成多个分区,各样分区存款和储蓄到钦命的FileGroup中,在物理存储上,每一种分区都以分手(separate)存款和储蓄的。
  • destination_id:是Partition
    Number,每种分区的编号
  • data_space_id:是FileGroupID,分区存款和储蓄的FileGroup。

ALTER PARTITION SCHEME
(Transact-SQL).aspx)

使用sys.destination_data_spaces视图来检查,该系统视图重返Partition
和filegroup之间的Mapping关系。借使一个FileGoup被alter partition scheme
标识为next used Filegroup,那么Partition 的个数会比多Partition
function划分的分区多一个。

1,成立Patition Schema时,使用 ALL
关键字钦命只可以钦点一个FileGroup,全数的Partition
都制造在同一个FileGroup上;在Patition
Schema成立成功之后,私下认可会将该FileGroup标志为Next Used

参照文书档案:

--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....
)

Msg 7710, Level 16, State
1, Line 2
Warning: The partition
scheme ‘PS_int_Left’ does not have any next used filegroup. Partition
scheme has not been changed.

Script2,每个更新Table的Patition Schema

查看Partition Function指定的Boundary Value

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.

1,创制分区函数

2,在Patition schema中,唯有三个FileGroup会被钦命(马克)为Next Used

图片 2

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.

How to Remember the Next Used Filegroup in a Partition
Scheme

Rebuilding Existing Partitioned Tables to a New
Partition Scheme

5,使用 alter partition scheme标记 next used filegroup