为Partition美洲杯招聘 Scheme多次指定Next

美洲杯招聘 1

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

参考文档:

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

--create partition scheme
CREATE PARTITION SCHEME PS_int_Left
AS 
PARTITION pf_int_Left
TO ([primary], [primary], [primary]);
  • **partition_scheme_id :**是数据表存储的空间,该空间不是具体的某个FileGroup。普通的表只有一个分区,只能存储在单个FileGroup中,但是,通过Partition
    Scheme,将表数据分割成多个分区,每个分区存储到指定的FileGroup中,在物理存储上,每个分区都是分开(separate)存储的。
  • destination_id:是Partition
    Number,每个分区的编号
  • data_space_id:是FileGroupID,分区存储的FileGroup。

1,创建分区函数

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'

2,创建分区scheme

  • 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.
-- create parition function
CREATE PARTITION FUNCTION pf_int_Left (int)
AS 
RANGE LEFT 
FOR VALUES (10,20);

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

--merge range
ALTER PARTITION FUNCTION pf_int_Left ()
merge range (20);
--alter partition scheme to cancel next used filegroup
ALTER PARTITION SCHEME PS_int_Left 
NEXT USED;

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

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.

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 value
ALTER PARTITION FUNCTION pf_int_Left ()
split range (30);

How to Remember the Next Used Filegroup in a Partition
Scheme

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

美洲杯招聘 2

查看分区个数

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'

查看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.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'

美洲杯招聘 3

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

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

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'

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

美洲杯招聘 4

7,Merge Range移除FileGroup

一,分析视图:sys.destination_data_spaces

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

绑定到Partition Scheme的Filegroup如下

二,测试用例

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

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

相关文章