你的位置:首页 > 数据库

[数据库]Partition:Partiton Scheme是否指定Next Used?


在SQL Server中,为Partition Scheme多次指定Next Used,不会出错,最后一次指定的FileGroup是Partition Scheme的Next Used,建议,在执行Partition Split操作之前,都要为Partition Scheme指定Next Used。

但是,SQL Server是否提供metadata,查看Partiton Scheme是否指定Next Used FileGroup?答案是系统视图:sys.destination_data_spaces。如果存在FileGroup被指定为Next Used ,那么视图返回的Partition的个数会比Partition Function划分的分区数量多1个。

一,分析视图:sys.destination_data_spaces

该视图返回三列,表示Partition Scheme的每个Partition和FileGroup之间的关系:

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

从表的存储结构来分析这三列的意义:

  • partition_scheme_id 是数据表存储的空间,该空间不是具体的某个FileGroup。普通的表只有一个分区,只能存储在单个FileGroup中,但是,通过Partition Scheme,将表数据分割成多个分区,每个分区存储到指定的FileGroup中,在物理存储上,每个分区都是分开(separate)存储的。
  • destination_id:是Partition Number,每个分区的编号
  • data_space_id:是FileGroupID,分区存储的FileGroup。

二,测试用例

1,创建分区函数

-- create parition functionCREATE PARTITION FUNCTION pf_int_Left (int)AS RANGE LEFT FOR VALUES (10,20);

2,创建分区scheme

--create partition schemeCREATE PARTITION SCHEME PS_int_LeftAS PARTITION pf_int_LeftTO ([primary], [primary], [primary]);

3,在split partition之前,必须使用alter partition scheme 指定一个Next Used FileGroup。如果Partiton Scheme没有指定 next used filegroup,那么alter partition function split range command 执行失败,不改变partition scheme。

--split range and add new one boudary valueALTER PARTITION FUNCTION pf_int_Left ()split range (30);

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.

4,如果检查 Partiton Scheme是否指定Next Used FileGroup?

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

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 FileGroupIDfrom sys.partition_schemes psinner join sys.destination_data_spaces dds  on ps.data_space_id=dds.partition_scheme_idinner join sys.partition_functions pf  on ps.function_id=pf.function_idwhere ps.name='PS_int_Left'

上述脚本返回3个partition,说明没有next used filegroup。

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

--alter partition scheme to mark next used filegroupALTER PARTITION SCHEME PS_int_Left NEXT USED [db_fg1];

查看分区个数

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 FileGroupIDfrom sys.partition_schemes psinner join sys.destination_data_spaces dds  on ps.data_space_id=dds.partition_scheme_idinner join sys.partition_functions pf  on ps.function_id=pf.function_idwhere ps.name='PS_int_Left'

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

6,将 FileGroup 取消标记为 next used filegroup

--alter partition scheme to cancel next used filegroupALTER PARTITION SCHEME PS_int_Left NEXT USED;

7,Merge Range移除FileGroup

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

查看Partition Function指定的Boundary Value

select pf.name as PartitionFunctionName,  pf.function_id,  pf.type,  pf.type_desc,  pf.boundary_value_on_right,  pf.fanout,  prv.boundary_id,  prv.valuefrom sys.partition_functions pfinner join sys.partition_range_values prv  on pf.function_id=prv.function_idwhere pf.name='pf_int_Left'

绑定到Partition Scheme的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 FileGroupIDfrom sys.partition_schemes psinner join sys.destination_data_spaces dds  on ps.data_space_id=dds.partition_scheme_idinner join sys.partition_functions pf  on ps.function_id=pf.function_idwhere ps.name='PS_int_Left'

参考文档:

How to Remember the Next Used Filegroup in a Partition Scheme