如何透過 Partition Table 進行資料分散與維護作業並以年度為例

        先前一篇針對 Partition Table 進行詳細的介紹，近日再重新的檢示，其實我覺得自已當初提出的資料平均分配的方式很好，而且其實到現在還是很適用，但由於有很多的客戶需求，希望可以透過日期的方式來進行資料分散與切割，所以今天我就透過這篇來特別介紹一下。

如何在 AlwaysOn 建立 Partition Table 與自動進行資料平均分配http://caryhsu.blogspot.com/2016/01/alwaysonpartition-table.html

此篇我們透過 AdventureWorks 的資料庫來進行，並且也會說明在建立後，如果遇到新年度時，大佬與預算較緊的使用者們，針對透過年月的方式進行資料分散的方式，該如何維護，這篇也會詳細的介紹。

本篇在 Partition Table 建立後，其實維護是最麻煩的，由其在 Merge 與 Split 的部份，我其實也是在測試機上測試了好多次，才摸清最好與最佳的方式，希望二篇加起來後，可以更完整的幫助到大家。

1。 加入 file group 與 files，這邊建議將 file 分散至不同的卷上面，最好的情況是有四台實體磁碟，各自放一個 file。

USE [master]
GO

ALTER DATABASE [AdventureWorks2019] ADD FILEGROUP [ag_group_1]
GO

ALTER DATABASE [AdventureWorks2019] ADD FILEGROUP [ag_group_2]
GO

ALTER DATABASE [AdventureWorks2019] ADD FILEGROUP [ag_group_3]
GO

ALTER DATABASE [AdventureWorks2019] ADD FILEGROUP [ag_group_4]
GO

ALTER DATABASE [AdventureWorks2019] ADD FILE ( NAME = N'fg_1', FILENAME = N'C:\sql_data\fg_1.ndf' , SIZE = 51200KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ag_group_1]
GO

ALTER DATABASE [AdventureWorks2019] ADD FILE ( NAME = N'fg_2', FILENAME = N'C:\sql_data\fg_2.ndf' , SIZE = 51200KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ag_group_2]
GO

ALTER DATABASE [AdventureWorks2019] ADD FILE ( NAME = N'fg_3', FILENAME = N'C:\sql_data\fg_3.ndf' , SIZE = 51200KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ag_group_3]
GO

ALTER DATABASE [AdventureWorks2019] ADD FILE ( NAME = N'fg_4', FILENAME = N'C:\sql_data\fg_4.ndf' , SIZE = 51200KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ag_group_4]
GO

2. 由於目前有四個資料群組，所以我們先統計一下資料量，然後再來規畫一下如何分散資料，目前如下圖，主要有2013年7月至2014年8月的資料。

--統計資料分佈

SELECT left(convert(varchar,TransactionDate,112),6) [tdhyear], count(*) nums
FROM [AdventureWorks2019].[Production].[TransactionHistory]
group by  left(convert(varchar,TransactionDate,112),6)
order by 1,2

3. 建立 Partition Function 與 Partition Schema，這邊會建立三個區域與對應至四個 File Group，如下列所示。

1900-01-01 00:00:00.000 ~ 2013-10-01 00:00:00.000 -> 儲存至 ag-group_1
2013-10-01 00:00:00.000 ~ 2014-02-01 00:00:00.000 -> 儲存至 ag-group_2
2014-02-01 00:00:00.000 ~ 2014-04-01 00:00:00.000 -> 儲存至 ag-group_3
2014-04-01 00:00:00.000 ~ 日後所有 -> 儲存至 ag-group_4

--將資料平均分散在多個不同區間

use [AdventureWorks2019];
CREATE PARTITION FUNCTION [PF-cary-test](datetime) AS RANGE RIGHT FOR VALUES (N'2013-10-01T00:00:00', N'2014-02-01T00:00:00', N'2014-04-01T00:00:00')

PS: 這裡建議採用 RANGE RIGHT 的方式，而不是用 RANGE Left, 因為我在 SQL Server 2019 與 2022 上測試，當使用 RANGE Left 的方式時，如果進行 SPLIT RANGE時，你會發現 ALTER PARTITION SCHEME 中的 NEXT USED 當合併資料，並將新加入的 file 與前一個 file 互換，雖然這樣也是沒問題，但還是覺得怪怪的，所以這邊也再請注意一下。

CREATE PARTITION SCHEME [PS-cary-test] AS PARTITION [PF-cary-test] TO ([ag_group_1], [ag_group_2], [ag_group_3], [ag_group_4])

4. 建立完上述的動作後，資料並不會立即的進行資料的搬移，需透過移除 Clustered Index 後，再將索引重新加入後即可

此處原本 TransactionID 是 PK 與 CLUSTERED INDEX，但由於此處的 Partition Table 是根據 TransactionDate 進行分配，所以要將 CLUSTERED INDEX 換成 TransactionDate 之後，資料才會重新進行排序。

ALTER TABLE [Production].[TransactionHistory] DROP CONSTRAINT [PK_TransactionHistory_TransactionID] WITH ( ONLINE = OFF )

ALTER TABLE [Production].[TransactionHistory] ADD  CONSTRAINT [PK_TransactionHistory_TransactionID] PRIMARY KEY NONCLUSTERED
(

[TransactionID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_PS-cary-test_638351445508081513] ON [Production].[TransactionHistory]
(

[TransactionDate]

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS-cary-test]([TransactionDate])

DROP INDEX [ClusteredIndex_on_PS-cary-test_638351445508081513] ON [Production].[TransactionHistory]

5. 透過下列的語法進行查詢，並確認資料在各群組分配的情況

SELECT OBJECT_NAME(p.object_id) AS ObjectName ,
i.name AS IndexName ,p.index_id AS IndexID ,
ds.name AS PartitionScheme ,
p.partition_number AS PartitionNumber ,
fg.name AS FileGroupName ,
prv_left.value AS LowerBoundaryValue ,
prv_right.value AS UpperBoundaryValue ,
CASE pf.boundary_value_on_right
WHEN 1 THEN 'RIGHT'
ELSE 'LEFT'
END AS PartitionFunctionRange ,
p.rows AS Rows
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
INNER JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id
INNER JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number- 1
LEFT OUTER JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE p.object_id = OBJECT_ID('Production.TransactionHistory');

6。 從查詢上來看，後續未規畫的資料年度就會放置到最後一個 file group，所以當新年度到達時，通常我們會採取二種不同的作法，一時加入一個 file group，然後將後續的資料加入，另一種就是合併資料，並將新年度的資料轉向最後一個 file group中，所以也在這分別說明二種方式。

7。 課長級作法。也就是加入一個 file 並將新年度的資料轉至此新的 file 中

7-1. 加入新的 file group

use [master];
ALTER DATABASE [AdventureWorks2019] ADD FILEGROUP [ag_group_5]
GO

ALTER DATABASE [AdventureWorks2019] ADD FILE ( NAME = N'fg_5', FILENAME = N'C:\sql_data\fg_5.ndf' , SIZE = 51200KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ag_group_5]
GO

7.2 將新的 file group 加入至 schema 中，並分割出新的資料區間時區，就是將 '2015-01-01' 後的時間，儲存至新的 file 中

use [AdventureWorks2019];
ALTER PARTITION SCHEME [PS-cary-test]
NEXT USED ag_group_5;

ALTER PARTITION FUNCTION  [PF-cary-test] () 
SPLIT RANGE ('2015-01-01T00:00:00.000')

7-3 查詢資料分佈的情況

8. 微課長級的作法，如果無法增加新的 file 用來分散資料的話，可以透過資料合併的方式，然後將新的資料儲存至最後一個群組中

8-1 依照資料上的分佈，我先將 file 3 與 4 的資料先進行合併，所以這邊我將 2014-02-01 00:00:00.000 之後的資料先合併至 ag-group3

ALTER PARTITION FUNCTION [PF-cary-test] () 
MERGE RANGE ('2014-04-01 00:00:00.000');

8-2 將 2015-01-01 之後的資料存到 ag-group4 上面

ALTER PARTITION SCHEME [PS-cary-test]
NEXT USED ag_group_4;

ALTER PARTITION FUNCTION  [PF-cary-test] () 
SPLIT RANGE ('2015-01-01T00:00:00.000'); 

8-3 由於有作過資料合併的動作，所以最好也是手動更新一次統計資訊

UPDATE STATISTICS [Production].[TransactionHistory]

8-4 查詢資料分佈的情況