自动管理分区

表分区的一个好处：能够避免Deadlock，分区之间是相互独立的，对一个分区加X锁，不会对其他分区产生contention。

在项目中，有如下 Partition Function 和 Partition Scheme

CREATE PARTITION FUNCTION [funcPartition_int_DataSourceID](int) 
AS RANGE LEFT 
FOR VALUES (1, 2, 3)


CREATE PARTITION SCHEME [schePartition_int_DataSourceID] 
AS PARTITION [funcPartition_DataSourceID] 
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])


create table dbo.dt_test
(
...More column definition

DataSourceID int
)
on [schePartition_int_DataSourceID](DataSourceID)

查看ETL的execution log，有时会发现 Deadlock Issue，对相关package Troubleshooting发现，发生deadlock的root cause是：同时更新表的两条语句产生contention，导致deadlock。仔细check代码，更新的两条查询语句都使用Partition Column（DataSourceID） 作为过滤条件。我推测，可能是这两个DataSourceID位于同一个Partition。

1，验证boundary value

select prv.function_id,pf.name,pf.boundary_value_on_right,prv.value as BoundaryValue
from sys.partition_range_values prv
inner join sys.partition_functions pf
    on prv.function_id=pf.function_id
where pf.name='funcPartition_int_DataSourceID'

BoundaryValue的值小于当前 DataSourceID的最大值，产生 contention的两个DataSourceID 在最右边的partition中。

随着项目数据的增加和人员的更替，缺少合理的管理计划，导致额外增加的DataSourceID都被分配到同一个partition中。
2，创建Job，自动分区

最佳实践，如果一个partition是non-empty，那么split range会导致data movement，这可能是一个非常耗费IO的一个process，为了避免extensive data movement，最好是预留一个empty partition，每次都从empty partition 中split range。

use db_study
go

declare @CurrentMaxBoundaryValue int
declare @ExistingMaxDataSourceID int
declare @BoudaryValue int

select @ExistingMaxDataSourceID = max(dds.DataSourceID)
from dbo.dt_DataSource dds with(nolock)

select @CurrentMaxBoundaryValue= max(cast(prv.value as int))
from sys.partition_functions pf 
inner join sys.partition_range_values prv
    on pf.function_id=prv.function_id
where pf.name='funcPartition_int_DataSourceID'

-- add new boundary value
if @CurrentMaxBoundaryValue<@ExistingMaxDataSourceID+1
begin
    set @BoudaryValue=@CurrentMaxBoundaryValue+1

    DECLARE @SQL NVARCHAR(MAX)=N'
ALTER PARTITION SCHEME [schePartition_int_DataSourceID]
NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION [funcPartition_int_DataSourceID]()
SPLIT RANGE ('
    declare @ExecSql nvarchar(max)
    set @ExecSql=''

    while @BoudaryValue<=@ExistingMaxDataSourceID+1
    BEGIN
        
        SELECT @ExecSql = @SQL+ cast(@BoudaryValue as varchar(10))+ N')'
        EXEC(@ExecSql)

        set @BoudaryValue=@BoudaryValue+1
    end
end

本例将分区全部存放在Primary FileGroup， 如果需要将不同的Partition存储在不同的FileGroup，那么可以增加Create filegroup的代码。

3，在Job执行时，Issue an error

Executed as user: NT SERVICE\SQLSERVERAGENT. UNKNOWN TOKEN failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed.

QUOTED_IDENTIFIER设置错误，添加下面的script，即可

SET QUOTED_IDENTIFIER  ON

参考：SET QUOTED_IDENTIFIER (Transact-SQL)

1. SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
2. SET QUOTED_IDENTIFIER must be ON when you are creating a filtered index.
3. SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.