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）
           

四.    部署

将自動化新增分區，及分區合并的腳本分别部署到作業中，指定業務空閑時間，每天自動執行腳本即可。