Partitioning - Phân vùng Bảng

Tại sao cần Partitioning?

Khi bảng có hàng chục triệu đến hàng tỷ rows (Very Large Tables), các vấn đề thường gặp:

• Query performance chậm: full scan cả bảng dù chỉ cần 1 tháng data
• Maintenance operations tốn thời gian: index rebuild/reorganize trên toàn bảng mất hàng giờ
• Archiving phức tạp: xóa dữ liệu cũ bằng DELETE tốn kém, lock toàn bảng
• Backup/restore chậm: không thể backup từng phần

Partitioning giải quyết:

• Partition Elimination: optimizer chỉ scan partitions liên quan
• Partition-level maintenance: rebuild index chỉ trên 1 partition
• Fast archiving: ALTER TABLE...SWITCH — metadata-only operation, gần như instant
• Filegroup management: phân vùng dữ liệu ra nhiều filegroup/disk

Lưu ý quan trọng: Partitioning KHÔNG thay thế indexes. Phải có proper indexes trên partitioned table.

Partition Function

Định nghĩa cách chia dữ liệu dựa trên một cột (partition key).

RANGE LEFT vs RANGE RIGHT

-- RANGE RIGHT: boundary là đầu của period
-- P1: < 2022-01-01
-- P2: 2022-01-01 ≤ x < 2023-01-01
-- P3: 2023-01-01 ≤ x < 2024-01-01
-- P4: ≥ 2024-01-01
CREATE PARTITION FUNCTION pf_OrderDate (DATE)
AS RANGE RIGHT FOR VALUES (
    '2022-01-01',
    '2023-01-01',
    '2024-01-01'
);

-- RANGE LEFT: boundary là cuối của period
-- P1: ≤ 2021-12-31
-- P2: 2022-01-01 ≤ x ≤ 2022-12-31
-- P3: 2023-01-01 ≤ x ≤ 2023-12-31
-- P4: > 2023-12-31
CREATE PARTITION FUNCTION pf_OrderDate_Left (DATE)
AS RANGE LEFT FOR VALUES (
    '2021-12-31',
    '2022-12-31',
    '2023-12-31'
);

Kiểm tra Partition Function

-- Xem partition functions
SELECT * FROM sys.partition_functions;

-- Xem boundary values
SELECT 
    pf.name AS partition_function,
    pf.type_desc,
    prv.boundary_id,
    prv.value AS boundary_value
FROM sys.partition_functions pf
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
ORDER BY pf.name, prv.boundary_id;

Partition Scheme

Map từng partition trong Partition Function sang một Filegroup cụ thể.

-- Dùng [PRIMARY] filegroup cho tất cả partitions (đơn giản nhất)
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
ALL TO ([PRIMARY]);

-- Phân chia ra nhiều filegroups (best practice cho performance)
-- Thêm filegroups trước
ALTER DATABASE SalesDB ADD FILEGROUP FG_2022;
ALTER DATABASE SalesDB ADD FILEGROUP FG_2023;
ALTER DATABASE SalesDB ADD FILEGROUP FG_2024;
ALTER DATABASE SalesDB ADD FILEGROUP FG_Current;

ALTER DATABASE SalesDB ADD FILE (
    NAME = 'SalesDB_2022',
    FILENAME = 'D:\SQL\SalesDB_2022.ndf'
) TO FILEGROUP FG_2022;

-- Tạo partition scheme map sang filegroups
CREATE PARTITION SCHEME ps_OrderDate_MultiFG
AS PARTITION pf_OrderDate
TO (FG_2022, FG_2023, FG_2024, FG_Current);
-- Số filegroup = số partition + 1 (NEXT USED)

Partitioned Table

-- Tạo bảng partitioned - chỉ thêm ON [partition_scheme]([partition_key])
CREATE TABLE Sales.Orders (
    OrderID     INT NOT NULL,
    OrderDate   DATE NOT NULL,
    CustomerID  INT NOT NULL,
    TotalAmount DECIMAL(18, 2) NOT NULL,
    Status      VARCHAR(20) NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID, OrderDate)
    -- Lưu ý: Partition key (OrderDate) phải trong clustered index key
)
ON ps_OrderDate (OrderDate);  -- Partition theo OrderDate

-- Kiểm tra phân phối dữ liệu theo partition
SELECT 
    p.partition_number,
    p.rows,
    prv.value AS boundary_value,
    fg.name AS filegroup_name
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values prv 
    ON pf.function_id = prv.function_id 
    AND prv.boundary_id = p.partition_number
LEFT JOIN sys.destination_data_spaces dds 
    ON ps.data_space_id = dds.partition_scheme_id 
    AND dds.destination_id = p.partition_number
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
WHERE OBJECT_NAME(p.object_id) = 'Orders'
    AND i.index_id IN (0, 1)  -- Heap hoặc clustered index
ORDER BY p.partition_number;

Partitioned Index

Aligned vs Non-Aligned Index

-- Aligned nonclustered index (partition key PHẢI có trong index key hoặc INCLUDE)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON Sales.Orders (CustomerID, OrderDate)  -- OrderDate là partition key
ON ps_OrderDate (OrderDate);  -- Cùng partition scheme

-- Rebuild index chỉ một partition (aligned index)
ALTER INDEX IX_Orders_CustomerID ON Sales.Orders
REBUILD PARTITION = 3;  -- Chỉ rebuild partition 3 (năm 2023)

-- Rebuild tất cả partitions
ALTER INDEX IX_Orders_CustomerID ON Sales.Orders
REBUILD PARTITION = ALL;

Partition Elimination

Optimizer tự động bỏ qua các partitions không cần thiết dựa trên filter condition.

-- Query này sẽ chỉ scan partition của năm 2023
SELECT OrderID, CustomerID, TotalAmount
FROM Sales.Orders
WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01';

-- Verify partition elimination qua execution plan
-- Trong execution plan: xem "Actual Partition Count" vs "Estimated Partition Count"

-- $PARTITION function: xác định row thuộc partition nào
SELECT 
    OrderID,
    OrderDate,
    $PARTITION.pf_OrderDate(OrderDate) AS PartitionNumber
FROM Sales.Orders
WHERE OrderID = 12345;

-- Đếm rows theo từng partition
SELECT 
    $PARTITION.pf_OrderDate(OrderDate) AS PartitionNumber,
    COUNT(*) AS RowCount
FROM Sales.Orders
GROUP BY $PARTITION.pf_OrderDate(OrderDate)
ORDER BY PartitionNumber;

Điều kiện để Partition Elimination hoạt động

• WHERE clause phải filter trực tiếp trên partition key column
• Không được dùng function bọc partition key: WHERE YEAR(OrderDate) = 2023 → không elimination; WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01' → có elimination
• Parameter sniffing có thể ảnh hưởng → dùng OPTION (OPTIMIZE FOR UNKNOWN) nếu cần

Partition Switching

Operation metadata-only (không di chuyển data vật lý) — cực nhanh (milliseconds dù table có tỷ rows).

Pattern: Loading dữ liệu mới (Switch IN)

-- 1. Tạo staging table với cùng schema, cùng partition scheme
CREATE TABLE Sales.Orders_Staging (
    OrderID     INT NOT NULL,
    OrderDate   DATE NOT NULL,
    CustomerID  INT NOT NULL,
    TotalAmount DECIMAL(18, 2) NOT NULL,
    Status      VARCHAR(20) NOT NULL,
    CONSTRAINT PK_Orders_Staging PRIMARY KEY CLUSTERED (OrderID, OrderDate)
)
ON ps_OrderDate (OrderDate);  -- Staging cũng phải partitioned

-- 2. Load dữ liệu vào staging (không lock production table)
INSERT INTO Sales.Orders_Staging
SELECT ... FROM ExternalSource;

-- 3. Build indexes trên staging
CREATE NONCLUSTERED INDEX IX_Staging_CustomerID
ON Sales.Orders_Staging (CustomerID, OrderDate)
ON ps_OrderDate (OrderDate);

-- 4. Switch IN: staging partition 4 → Orders partition 4 (instant!)
ALTER TABLE Sales.Orders_Staging 
SWITCH PARTITION 4 TO Sales.Orders PARTITION 4;

Pattern: Archiving dữ liệu cũ (Switch OUT)

-- 1. Tạo archive table (cùng schema, không cần partitioned)
CREATE TABLE Sales.Orders_Archive (
    OrderID     INT NOT NULL,
    OrderDate   DATE NOT NULL,
    CustomerID  INT NOT NULL,
    TotalAmount DECIMAL(18, 2) NOT NULL,
    Status      VARCHAR(20) NOT NULL,
    CONSTRAINT PK_Orders_Archive PRIMARY KEY CLUSTERED (OrderID, OrderDate)
)
ON FG_Archive;  -- Có thể là filegroup riêng, read-only

-- 2. Switch OUT: Orders partition 1 → archive table (instant!)
ALTER TABLE Sales.Orders 
SWITCH PARTITION 1 TO Sales.Orders_Archive;

-- 3. Archive table giờ có dữ liệu của partition 1
-- Có thể backup filegroup FG_2022 riêng lẻ, hoặc xóa data từ archive

Điều kiện cho Partition Switch

-- Kiểm tra điều kiện switch (sẽ báo lỗi nếu không thỏa)
-- 1. Source và target phải cùng schema (columns, data types, constraints)
-- 2. Aligned indexes phải tương đương
-- 3. Source partition phải là subset của target check constraints
-- 4. Nếu switch vào partitioned table: check constraint trên staging phải match
-- Thêm check constraint để SQL Server biết data chỉ thuộc partition đó
ALTER TABLE Sales.Orders_Staging
ADD CONSTRAINT CK_Staging_2024
CHECK (OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01');

Split và Merge Partitions

Split: Thêm partition mới

-- Trước khi split: cần NEXT USED filegroup trong partition scheme
ALTER PARTITION SCHEME ps_OrderDate
NEXT USED FG_2025;

-- Split để thêm boundary cho năm 2025
ALTER PARTITION FUNCTION pf_OrderDate()
SPLIT RANGE ('2025-01-01');
-- Partition cũ bị chia làm 2: [2024-01-01, 2025-01-01) và [2025-01-01, ...)

Merge: Xóa partition (gộp 2 partitions lại)

-- Merge: gộp 2 partitions (dữ liệu sẽ nằm trong partition còn lại)
-- Điều kiện: partition bị merge phải EMPTY hoặc chấp nhận data move (chậm nếu có data)
ALTER PARTITION FUNCTION pf_OrderDate()
MERGE RANGE ('2022-01-01');
-- Boundary 2022-01-01 bị xóa, partition trước và sau gộp lại

Best Practice: Luôn SWITCH OUT data trước, rồi mới MERGE để tránh data movement.

Partition Maintenance - Sliding Window Pattern

Pattern phổ biến: thêm partition mới ở cuối, xóa partition cũ ở đầu.

-- Stored procedure: thêm partition mới cho tháng mới
CREATE OR ALTER PROCEDURE dbo.usp_AddNewMonthPartition
    @NewBoundary DATE  -- Ví dụ: '2025-01-01' cho tháng 1/2025
AS
BEGIN
    -- 1. Tạo filegroup và file mới
    DECLARE @FGName VARCHAR(50) = 'FG_' + CONVERT(VARCHAR, @NewBoundary, 112);
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = N'ALTER DATABASE [SalesDB] ADD FILEGROUP [' + @FGName + N'];';
    EXEC sp_executesql @SQL;
    
    -- 2. Set NEXT USED
    SET @SQL = N'ALTER PARTITION SCHEME ps_OrderDate NEXT USED [' + @FGName + N'];';
    EXEC sp_executesql @SQL;
    
    -- 3. Split partition function
    SET @SQL = N'ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE (''' + 
               CONVERT(VARCHAR, @NewBoundary, 120) + N''');';
    EXEC sp_executesql @SQL;
    
    PRINT 'New partition added for boundary: ' + CONVERT(VARCHAR, @NewBoundary);
END;

Statistics per Partition

-- Cập nhật statistics cho từng partition riêng lẻ (SQL Server 2014+)
UPDATE STATISTICS Sales.Orders (IX_Orders_CustomerID)
WITH ROWCOUNT = 1000000, PAGECOUNT = 5000;  -- Không thực tế

-- Incremental statistics (SQL Server 2014+, Enterprise)
-- Chỉ update stats cho partitions có thay đổi
CREATE STATISTICS stats_OrderDate ON Sales.Orders(OrderDate)
WITH INCREMENTAL = ON;

-- Update chỉ partition 4
UPDATE STATISTICS Sales.Orders(stats_OrderDate)
WITH RESAMPLE ON PARTITIONS(4);

Khi nào KHÔNG nên dùng Partitioning

• Bảng nhỏ (< 1 triệu rows): overhead nhiều hơn lợi ích
• Thay thế index: partitioning có partition elimination nhưng không thay thế index seeks
• OLTP đơn giản: queries trên nhiều partitions có thể chậm hơn nếu không có good indexes
• Không có clear partition key: dùng date column thì phải có date trong WHERE clause
• Development/Staging environments: tăng complexity không cần thiết

Horizontal vs Vertical Sharding (Khái niệm)

Q&A - Phỏng vấn Partitioning

Junior Level

Q1: Table partitioning trong SQL Server là gì?

Partitioning chia dữ liệu của một table thành các phần nhỏ hơn (partitions) dựa trên giá trị của một cột (partition key). Về mặt logical, vẫn là một table duy nhất; về mặt vật lý, dữ liệu được lưu trong các partition riêng biệt trên các filegroups.

Q2: Partition Function và Partition Scheme khác nhau như thế nào?

• Partition Function: định nghĩa cách chia — boundary values và RANGE LEFT/RIGHT
• Partition Scheme: định nghĩa lưu ở đâu — map từng partition sang filegroup

Q3: RANGE LEFT và RANGE RIGHT khác nhau như thế nào?

Quyết định boundary value thuộc về partition nào:

• RANGE LEFT: boundary value thuộc partition bên trái (≤)
• RANGE RIGHT: boundary value thuộc partition bên phải (≥)

Ví dụ: boundary 2024-01-01 với RANGE RIGHT → partition trước chứa < 2024-01-01, partition sau chứa ≥ 2024-01-01.

Mid Level

Q4: Partition Elimination là gì? Điều kiện để xảy ra?

Partition elimination là khi SQL Server optimizer bỏ qua các partitions không chứa dữ liệu thỏa mãn WHERE clause, giảm I/O đáng kể.

Điều kiện:

• WHERE clause filter thẳng trên partition key column
• Không wrap partition key trong function: YEAR(OrderDate) = 2023 không có elimination; OrderDate BETWEEN '2023-01-01' AND '2023-12-31' có elimination

Q5: Partition switching hoạt động thế nào? Tại sao lại nhanh?

ALTER TABLE...SWITCH là metadata-only operation: SQL Server chỉ cập nhật system catalog để thay đổi “ownership” của partition, không di chuyển data pages vật lý. Vì vậy chỉ mất milliseconds dù table có tỷ rows.

Điều kiện: source và target phải cùng schema, indexes, constraints phải compatible, data phải thực sự chỉ thuộc partition target (enforce bằng CHECK constraint).

Q6: Tại sao phải có partition key trong clustered index?

Partition key phải là một phần của clustered index key để đảm bảo aligned partitioning — data rows thuộc partition nào thì index rows cũng nằm trên cùng partition đó. Nếu không aligned, partition switching sẽ không hoạt động.

Senior Level

Q7: Thiết kế sliding window pattern cho bảng Orders 5 năm dữ liệu, archive hàng tháng?

Pattern:

1. Partition function: boundary hàng tháng (60+ boundaries cho 5 năm)
2. Mỗi tháng: tạo partition mới cho tháng tới (SPLIT RANGE với NEXT USED filegroup)
3. Archive tháng cũ:
   • Chuẩn bị staging/archive table với correct check constraint
   • SWITCH OUT partition cũ nhất vào archive table (instant)
   • MERGE RANGE để gộp empty partition (không còn data)
   • Backup filegroup cũ, đánh read_only
4. Automate bằng SQL Agent job chạy đầu tháng

Q8: Incremental statistics khác gì với statistics thông thường trên partitioned table?

Statistics thông thường: khi data thay đổi ở bất kỳ partition nào, cần update statistics cho toàn bộ index → tốn kém với bảng lớn.

Incremental statistics (SQL Server 2014+, Enterprise): statistics được maintain per-partition. Khi UPDATE STATISTICS, chỉ cần update partitions có thay đổi. Với sliding window pattern (chỉ partition mới nhất có writes), chỉ cần update 1 partition thay vì toàn bộ bảng.

Q9: Aligned vs non-aligned index — khi nào dùng non-aligned?

Non-aligned index hiếm khi cần thiết. Có thể dùng khi:

• Query patterns cần access data across partitions dựa trên column khác (không phải partition key)
• Không cần partition switch cho index này
• Biết rõ query sẽ range scan across partitions và global index nhanh hơn

Nhưng nhược điểm lớn: không switch được partition nếu bảng có non-aligned index → phải drop index trước, switch, rồi create lại → mất nhiều thời gian và ảnh hưởng hiệu năng.