[SQL Server][資料封存]Truncate指定Partition Number的資料(SQL Server 2016)

想移除歷史交易資料時,很直覺想到串DML Delete;但如果刪除交易量大,怕影響線上交易時,辛苦的攻城師需要改成預存程序分批刪除,每次刪2,000筆還放慢半秒,但鎖定物件較多,批次時間也久;如果資料表能獲得DBA大人同意設置成Partition Table,用上Switch Partition可以飛快的轉出資料;不過Switch Partition手續有些繁瑣,得先建立相同分割檔案群組、相同結構的空資料表再轉移。

SQL Server 2016更方便了,TRUNCATE TABLE支援指定分割編號,秒殺的概念,來試試。

Docs上對TRUNCATE TABLE (Transact-SQL)的解釋

移除所有資料列的資料表或指定的分割區資料表,而不記錄個別資料列刪除。

TRUNCATE TABLE 類似於不含 WHERE 子句的 DELETE 陳述式;不過,TRUNCATE TABLE 比較快,使用的系統資源和交易記錄資源也比較少。


建立測試環境

建立資料庫,檔案群組、檔案

--CREATE DataBase [PartitionTableTruncateTestDb]
GO
alter database [PartitionTableTruncateTestDb] add filegroup [PartFG01];
alter database [PartitionTableTruncateTestDb] add filegroup [PartFG02];
GO
alter database [PartitionTableTruncateTestDb] add file ( name=[PartFGFile01],filename='T:\SQL\PartFGFile01.ndf',size=10MB) TO FileGroup[PartFG01]
alter database [PartitionTableTruncateTestDb] add file ( name=[PartFGFile02],filename='T:\SQL\PartFGFile02.ndf',size=10MB) TO FileGroup[PartFG02]

 

建立分割函數及分割配置Scheme(這邊我們想用考試成績作為分割條件,60分及格的一組,不及格的一組)。

CREATE PARTITION FUNCTION PFInt(int) AS RANGE RIGHT FOR VALUES (60)
GO

CREATE PARTITION SCHEME [PSchemeInt] AS PARTITION [PFInt] TO ([PartFG01], [PartFG02])
GO

 

驗證分割函數是否符合預期

SELECT
	59 AS val,$PARTITION.PFInt(59) AS partition UNION ALL
SELECT
	60,$PARTITION.PFInt(60) UNION ALL
SELECT
	61,$PARTITION.PFInt(61)

 

59分是分割1,60分是分割2

 


建立分割資料表

新增5個人的成績,巧虎、琪琪、桃樂比、妙妙、小花,但是這次考試琪琪、桃樂比都不及格。

DROP TABLE IF EXISTS [TestPartitionTable]
CREATE TABLE [dbo].[TestPartitionTable](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nchar](300) NOT NULL,
	[Score] [int] NOT NULL,
CONSTRAINT [PK_TestPartitionTable] PRIMARY KEY CLUSTERED 
(
	[id] ASC,
	[Score] ASC
) ON [PSchemeInt](Score)
)  ON [PSchemeInt](Score)


INSERT INTO [TestPartitionTable]
	VALUES ('巧虎', 100), ('琪琪', 50), ('桃樂比', 40), ('妙妙 ', 80), ('小花 ', 70)

 

查詢分割內的筆數及資料所在的分割

--查詢分割內的資料筆數
SELECT
	OBJECT_NAME(object_id) AS table_name
	,partition_number
	,row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('TestPartitionTable')

--查詢資料所在的分割
SELECT 
%%lockres%% as lockres
,%%physloc%% as physloc
,b.file_id
,b.page_id
,b.slot_id
,$PARTITION.PFInt(score) as partition_number
,a.* from [TestPartitionTable] a 
 CROSS APPLY fn_PhysLocCracker(%%physloc%%) b

 

分割1(不及格)有2筆,分割2(及格)有3筆

 


指定Partition Number Truncate

TRUNCATE TABLE [TestPartitionTable] 
  WITH(Partitions(1))

也可以指定區間WITH (PARTITIONS (6 TO 8))或清單WITH (PARTITIONS (1, 5))

 

重新查詢資料表,只剩下分割2的資料了


刪除大型交易資料表(刪除 vs 截斷)

這樣或許不能展現他的對比效果,來把資料撐大一點,比較刪除和截斷的差異

TRUNCATE TABLE TestPartitionTable
INSERT INTO [TestPartitionTable]
	VALUES ('巧虎', 100), ('琪琪', 50), ('桃樂比', 40), ('妙妙 ', 80), ('小花 ', 70)
GO

INSERT INTO TestPartitionTable SELECT  name,score FROM  TestPartitionTable
GO 20  

EXEC sp_spaceused 'TestPartitionTable'

 

大概58秒完成,我們現在擁有一個524萬筆資料,大概3GB的資料表

查詢分割內的筆數

SELECT
	OBJECT_NAME(object_id) AS table_name
	,partition_number
	,row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('TestPartitionTable')

 

分割1: 209萬,分割2: 314萬

比較兩個分割資料的移除時間

實驗組

SET STATISTICS IO ON
SET STATISTICS TIME ON
TRUNCATE TABLE [TestPartitionTable] 
  WITH(Partitions(2))

SQL Server 執行次數:

CPU 時間 = 0 ms,經過時間 = 1 ms。

 

對照組(刪除剩下的資料表)

SET STATISTICS IO ON
SET STATISTICS TIME ON
DELETE [TestPartitionTable] 

資料表 'TestPartitionTable'。掃描計數 2,邏輯讀取 2258229,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

SQL Server 執行次數:

CPU 時間 = 6235 ms,經過時間 = 15485 ms。

 


小結:

移除資料方法

筆數

時間

刪除(Delete)

2,097,152

15,485ms

截斷(Truncate)

3,145,728

1ms

 

SQL 2016也下放有限制的Partition Table功能到Standard版。

 


參考

Docs TRUNCATE TABLE (Transact-SQL)