SQL Server 自动化管理——分区表自动化管理

背景：

生产库上，交易日志表每天以1000万左右的记录在增长，统计报表基本可以以日报表为统计基础。为兼顾性能和业务的需要，日志表每天做一个分区，并且数据仅保留最近32天，超过32天的数据清除。

一.     解决方案：

1.  停机维护时，一次性增加n个分区。优点是在增加分区的时候，不会对性能造成影响；不足的地方是，分区总数有限，SQLServer 2016 以前，最多只能有1000个分区，即使到SQLServer 2016版本，分区数提供到1.5万个，也不可能做到一劳永逸。况且随着分区数的增加，分区带来的性能也会逐渐被扫描分区给抵消。且一旦分区用完，并忘记增加分区，再增加分区的成本将会大幅提高。该方案仅仅适用于服务器定期维护（如30天停机维护一次），每次维护增加比维护周期略多个分区（如31个分区）。

2.  每天自动增加一个分区，也是本文将重点叙述的方案

二.    自动化增加分区

2.1  创建分区表

创建测分区方案，并创建在分区方案上的测试表tradeLog，并对分区表的每个分区上采用页压缩，提高空间使用率。

alterdatabase test
  addfilegroup day_20180427
alterdatabase test
  addfilegroup day_20180428
alterdatabase test
  addfilegroup day_20180429
alterdatabase test
  addfilegroup day_20180430
alterdatabase test
  addfilegroup day_20180501
alterdatabase test
  addfilegroup day_20180502
alterdatabase test
addfile(name=N'day_20180427',filename='D:\DB\test_partition\day_20180427.ndf'
  ,size=5mb, filegrowth=5mb)
tofilegroup day_20180427
alterdatabase test
addfile(name=N'day_20180428',filename='D:\DB\test_partition\day_20180428.ndf'
  ,size=5mb, filegrowth=5mb)
tofilegroup day_20180428
alterdatabase test
addfile(name=N'day_20180429',filename='D:\DB\test_partition\day_20180429.ndf'
  ,size=5mb, filegrowth=5mb)
tofilegroup day_20180429
alterdatabase test
  addfile(name=N'day_20180430',filename='D:\DB\test_partition\day_20180430.ndf'
      ,size=5mb, filegrowth=5mb)
  tofilegroup day_20180430
 
alterdatabase test
  addfile(name=N'day_20180501',filename='D:\DB\test_partition\day_20180501.ndf'
  ,size=5mb, filegrowth=5mb)
  tofilegroup day_20180501
 
alterdatabase test
  addfile(name=N'day_20180502',filename='D:\DB\test_partition\day_20180502.ndf'
  ,size=5mb, filegrowth=5mb)
  tofilegroup day_20180502
 
createpartitionfunction F_Date_Day(datetime)
asrangerightforvalues('2018-04-28','2018-04-29','2018-04-30','2018-05-01','2018-05-02')
 
createpartition scheme P_Date_Day
aspartition [F_Date_Day]
to(day_20180427,day_20180428,day_20180429,day_20180430,day_20180501,day_20180502)
--drop table  tradelog
--go
--drop partition function F_Date_Day
--go
--drop partition scheme P_Date_Day
--go
--在分区方案上创建表
CREATETABLE [dbo].[tradelog](
  [ID] [int] identity(1,1),
  [productID] [int] NULL,
  [tradedate] [datetime] NULL
)on P_Date_Day(tradedate)
--在分区方案上创建表索引
CREATECLUSTEREDINDEX [ClI_tradelog_tradedate] ON [dbo].[tradelog]
(
  [tradedate] ASC
)on P_Date_Day(tradedate)
GO
--对表数据进行页压缩
USE [test]
ALTERTABLE [dbo].[tradelog] REBUILDPARTITION= 1 WITH(DATA_COMPRESSION=PAGE)
USE [test]
ALTERTABLE [dbo].[tradelog] REBUILDPARTITION= 2 WITH(DATA_COMPRESSION=PAGE)
USE [test]
ALTERTABLE [dbo].[tradelog] REBUILDPARTITION= 3 WITH(DATA_COMPRESSION=PAGE)
USE [test]
ALTERTABLE [dbo].[tradelog] REBUILDPARTITION= 4 WITH(DATA_COMPRESSION=PAGE)
USE [test]
ALTERTABLE [dbo].[tradelog] REBUILDPARTITION= 5 WITH(DATA_COMPRESSION=PAGE)
USE [test]
ALTERTABLE [dbo].[tradelog] REBUILDPARTITION= 6 WITH(DATA_COMPRESSION=PAGE)
 
GO
           

2.2  自动增加新分区

--每天清掉2天之前数据，合并分区
declare @filegroupName varchar(20)
declare @sql varchar(200)
select 
		@filegroupName=groupname
from sys.destination_data_spaces dds,sys.indexes i,sys.sysfilegroups fg
where dds.partition_scheme_id=i.data_space_id
	and dds.data_space_id=fg.groupid
	and object_id=OBJECT_ID('tradelog',N'U')
	and dds.destination_id=2

--在分区文件组上创建普通表
set @sql='
CREATE TABLE [dbo].[tradelog_temp](
	[ID] [int] identity(1,1),
	[productID] [int] NULL,
	[tradedate] [datetime] NULL
)on '[email protected]

exec (@sql)
--在分区方案所在的文件组上创建表索引
set @sql='
CREATE CLUSTERED INDEX [ClI_tradelog_tradedate_temp] ON [dbo].[tradelog_temp]
(
	[tradedate] ASC
)on ' [email protected]
exec(@sql)
--分区表采用页压缩，普通表也需要进行页压缩
ALTER TABLE [dbo].[tradelog_temp] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = PAGE
)

--将分区表上对应分区数据移动到普通表中
alter table tradelog switch partition 2 to tradelog_temp
--清空临时表，删除临时表
truncate table tradelog_temp
drop table tradelog_temp
--合并分区
alter partition function F_Date_Day()
merge range(convert(varchar(10),dateadd(D,-2,getdate()),121))

--移除文件、文件组
set @sql='
alter database test remove file '+ @filename
exec (@sql)
set @sql='
	alter database test remove filegroup '[email protected]
           

脚本中新创建文件组、文件、修改分区方案都不支持参数传递，所以使用动态SQL

自动化增加新分区需要注意的地方有：

• l  第一点：也是必须要做到的一点，新增分隔点（如本文中以时间为分割点，既某时间点）后分区表没有数据，这样保证了新增分区时，没有数据移动，减少I/O时间，进而减少锁表时间，提高性能。
• l  第二点，预留一个分区到两个分区，本文中预留两个分区，即最新数据用到的分区文件是day_20180430，而day_20180501，day_20180502分区文件是预留的，2018年4月30日创建的分区文件是day_20180503。这样预留分区有两个好处，一是防止当天分区创建失败，数据不能很好的分区；二是分区不必在0点创建，可以部署在任意业务空闲时间，部署灵活，可以尽量减少对业务的影响

三.    分区自动化合并

前文提到，SQL Server 数据库的分区总个数是有限制的，并且过多的分区也会对性能造成影响，如下将给出自动化移除32天以前分区的数据，并将对应分区合并掉。

先用如下脚本查看一下tradelog的分区情况：

;with cte as
  (select
      object_id
      ,OBJECT_NAME(i.object_id) tableName
      ,i.index_id
      ,dds.partition_scheme_id
      ,dds.destination_id as partition_number
      ,fg.groupid
      ,fg.groupname
      ,f.fileid
      ,f.name
      ,f.filename
      --,p.partition_id
      --,p.rows
   fromsys.destination_data_spaces dds,sys.indexes i,sys.sysfilegroups fg,sys.sysfiles f
   where dds.partition_scheme_id=i.data_space_id
      and dds.data_space_id=fg.groupid
      and fg.groupid=f.groupid
      )
,cte1 as(
  select
      ps.data_space_id as partition_scheme_id
      ,ps.name partiton_schemes_name
      ,pf.name partition_function_name
      ,pf.function_id
      --,prv.value AS BoundaryValue
  fromsys.partition_schemes ps ,sys.partition_functions pf
  where ps.function_id=pf.function_id
      --and pf.function_id=prv.function_id
)
select cte.tableName,cte.groupname,cte.name,cte.filename
  ,cte.partition_number,cte1.partiton_schemes_name,cte1.partition_function_name,p.rows
  ,prv.boundary_id,prv.value BoundaryValue
from cte
innerjoin cte1   on cte.partition_scheme_id=cte1 .partition_scheme_id
leftjoinsys.partition_range_values prv on cte1.function_id=prv.function_id and cte.partition_number=prv.boundary_id
leftjoinsys.partitions  p on cte.object_id=p.object_idand cte.index_id=p.index_id and cte.partition_number=p.partition_number
where
   cte.object_id=OBJECT_ID('dbo.tradelog','U')
orderby partition_number
           

从脚本中可以获取到tradelog分区的基本情况如下图：

适用于所有版本的SQL Server

--每天清掉2天之前数据，合并分区
declare @filegroupName varchar(20)
declare @fileName varchar(20)
declare @sql varchar(200)
select 
		@filegroupName=groupname,@fileName=f.filename
from sys.destination_data_spaces dds,sys.indexes i,sys.sysfilegroups fg,sys.sysfiles f
where dds.partition_scheme_id=i.data_space_id
	and dds.data_space_id=fg.groupid
	and fg.groupid=f.groupid
	and object_id=OBJECT_ID('tradelog',N'U')
	and dds.destination_id=2

--在分区文件组上创建普通表
set @sql='
CREATE TABLE [dbo].[tradelog_temp](
	[ID] [int] identity(1,1),
	[productID] [int] NULL,
	[tradedate] [datetime] NULL
)on '[email protected]

exec (@sql)
--在分区方案所在的文件组上创建表索引
set @sql='
CREATE CLUSTERED INDEX [ClI_tradelog_tradedate_temp] ON [dbo].[tradelog_temp]
(
	[tradedate] ASC
)on ' [email protected]
exec(@sql)

--分区表采用页压缩，普通表也需要进行页压缩
ALTER TABLE [dbo].[tradelog_temp] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = PAGE
)

--将分区表上对应分区数据移动到普通表中
alter table tradelog switch partition 2 to tradelog_temp
truncate table tradelog_temp
drop table tradelog_temp

--合并分区
alter partition function F_Date_Day()
merge range(convert(varchar(10),dateadd(D,-2,getdate()),121))

--移除文件、文件组
set @sql='
alter database test remove file '+ @filename
exec (@sql)
set @sql='
	alter database test remove filegroup '[email protected]
exec (@sql)
           

技巧：

• l  在《SQL Server 大数据管理——表分区》一文中提到合并表分区的移动方向是分割点后一个分区的数据移动到前一个分区的文件中，所以为了减少数据的移动的I/O，在合并分区时，总是保留一个最前面的分区。
• l  该文文了测试，合并2天前的分区，用在实际情形中，根据需要调整值
• l  源表（tradelog）有结构变化（包括增加或减少索引，更改表数据压缩类型，更改表架构等），该脚本也需要随之调整。

适用于SQL Server 2016及以后版本的脚本

适用于SQL Server 2016及以后版本的脚本

[sql] view plain copy
--清除第2分区数据  
truncate table tradelog with(partitions(2))  
--合并分区  
alter partition function F_Date_Day()  
merge range(convert(varchar(10),dateadd(D,-2,getdate()),121))  
  
--移除文件、文件组  
set @sql='  
alter database test remove file '+ @filename  
exec (@sql)  
set @sql='  
    alter database test remove filegroup '[email protected]  
exec （@sql）
           

四.    部署

将自动化新增分区，及分区合并的脚本分别部署到作业中，指定业务空闲时间，每天自动执行脚本即可。