Posted in Partition, sqlserver

MSSQL Server Aylık Function ve Scheme Yapısı Oluşturma

   28 Nisan 2025  Leave a Comment on MSSQL Server Aylık Function ve Scheme Yapısı Oluşturma

Microsoft SQL Server’ın sunduğu partitioning yeteneği, büyük tabloları parçalara ayırarak hem performansı artırır hem de veri yönetimini kolaylaştırır. Bu makalede, zaman bazlı (aylık) bir partition yapısının nasıl kurgulanacağını, 2020–2026 yıllarını kapsayan, her yıl için ayrı filegroup kullanılan bir mimari ile detaylıca ele alacağız. Ayrıca yıl sonunda eski verileri SWITCH yöntemi ile arşivleyip, kullanılmayan bölümleri sistemden kaldırarak Sliding Window modelini nasıl uygulayabileceğimizi inceleyeceğiz.

İlk olarak, her yıl için birer filegroup ve data file oluşturuyoruz:

USE [master]
GO
ALTER DATABASE [TEST] ADD FILEGROUP [FG_2020]
GO
ALTER DATABASE [TEST] ADD FILE ( NAME = N'FG_2020', FILENAME = N'C:\PART\FG_2020.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_2020]
GO
ALTER DATABASE [TEST] ADD FILEGROUP [FG_2021]
GO
ALTER DATABASE [TEST] ADD FILE ( NAME = N'FG_2021', FILENAME = N'C:\PART\FG_2021.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_2021]
GO
ALTER DATABASE [TEST] ADD FILEGROUP [FG_2022]
GO
ALTER DATABASE [TEST] ADD FILE ( NAME = N'FG_2022', FILENAME = N'C:\PART\FG_2022.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_2022]
GO
ALTER DATABASE [TEST] ADD FILEGROUP [FG_2023]
GO
ALTER DATABASE [TEST] ADD FILE ( NAME = N'FG_2023', FILENAME = N'C:\PART\FG_2023.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_2023]
GO
ALTER DATABASE [TEST] ADD FILEGROUP [FG_2024]
GO
ALTER DATABASE [TEST] ADD FILE ( NAME = N'FG_2024', FILENAME = N'C:\PART\FG_2024.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_2024]
GO
ALTER DATABASE [TEST] ADD FILEGROUP [FG_2025]
GO
ALTER DATABASE [TEST] ADD FILE ( NAME = N'FG_2025', FILENAME = N'C:\PART\FG_2025.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_2025]
GO
ALTER DATABASE [TEST] ADD FILEGROUP [FG_2026]
GO
ALTER DATABASE [TEST] ADD FILE ( NAME = N'FG_2026', FILENAME = N'C:\PART\FG_2026.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_2026]
GO

File group ve Data file yapılarımızı oluşturduktan sonra şimdi veritabanı altında Function ve scheme yapımızı oluşturalım. Range RIGHT ifadesi kullanacağız. Her ayın son günü sınır olacak şekilde tanımlanır.

-- Partition Function
CREATE PARTITION FUNCTION pf_Tarih (DATETIME)
AS RANGE RIGHT FOR VALUES (
    '2020-01-31','2020-02-29','2020-03-31','2020-04-30','2020-05-31','2020-06-30',
    '2020-07-31','2020-08-31','2020-09-30','2020-10-31','2020-11-30','2020-12-31',
    '2021-01-31','2021-02-28','2021-03-31','2021-04-30','2021-05-31','2021-06-30',
    '2021-07-31','2021-08-31','2021-09-30','2021-10-31','2021-11-30','2021-12-31',
    '2022-01-31','2022-02-28','2022-03-31','2022-04-30','2022-05-31','2022-06-30',
    '2022-07-31','2022-08-31','2022-09-30','2022-10-31','2022-11-30','2022-12-31',
    '2023-01-31','2023-02-28','2023-03-31','2023-04-30','2023-05-31','2023-06-30',
    '2023-07-31','2023-08-31','2023-09-30','2023-10-31','2023-11-30','2023-12-31',
    '2024-01-31','2024-02-29','2024-03-31','2024-04-30','2024-05-31','2024-06-30',
    '2024-07-31','2024-08-31','2024-09-30','2024-10-31','2024-11-30','2024-12-31',
    '2025-01-31','2025-02-28','2025-03-31','2025-04-30','2025-05-31','2025-06-30',
    '2025-07-31','2025-08-31','2025-09-30','2025-10-31','2025-11-30','2025-12-31',
    '2026-01-31','2026-02-28','2026-03-31','2026-04-30','2026-05-31','2026-06-30',
    '2026-07-31','2026-08-31','2026-09-30','2026-10-31','2026-11-30','2026-12-31'
);

Scheme yapımızı oluşturmak için aşağıdaki script kullanılmaktadır. n+1 kuralına uyacak şekilde yapılandırma yapılabilir.

-- Partition Scheme
CREATE PARTITION SCHEME ps_Tarih
AS PARTITION pf_Tarih TO (
    FG_2020, FG_2020, FG_2020, FG_2020, FG_2020, FG_2020, FG_2020, FG_2020, FG_2020, FG_2020, FG_2020, FG_2020,
    FG_2021, FG_2021, FG_2021, FG_2021, FG_2021, FG_2021, FG_2021, FG_2021, FG_2021, FG_2021, FG_2021, FG_2021,
    FG_2022, FG_2022, FG_2022, FG_2022, FG_2022, FG_2022, FG_2022, FG_2022, FG_2022, FG_2022, FG_2022, FG_2022,
    FG_2023, FG_2023, FG_2023, FG_2023, FG_2023, FG_2023, FG_2023, FG_2023, FG_2023, FG_2023, FG_2023, FG_2023,
    FG_2024, FG_2024, FG_2024, FG_2024, FG_2024, FG_2024, FG_2024, FG_2024, FG_2024, FG_2024, FG_2024, FG_2024,
    FG_2025, FG_2025, FG_2025, FG_2025, FG_2025, FG_2025, FG_2025, FG_2025, FG_2025, FG_2025, FG_2025, FG_2025,
    FG_2026, FG_2026, FG_2026, FG_2026, FG_2026, FG_2026, FG_2026, FG_2026, FG_2026, FG_2026, FG_2026, FG_2026,
	FG_2027, FG_2027, FG_2027, FG_2027, FG_2027, FG_2027, FG_2027, FG_2027, FG_2027, FG_2027, FG_2027, FG_2027

Yukarıdaki işlemlerden sonra scheme yapımızı kullanacak bir şekilde bir tablo oluşturulur.

CREATE TABLE dbo.PartTable (
    ID INT IDENTITY(1,1) NOT NULL,
    AD VARCHAR(50) NULL,
    SOYAD VARCHAR(50) NULL,
    TARIH DATETIME NOT NULL
)
ON ps_Tarih(TARIH);

Aşağıdaki komutla kontrol işlemi yapılır.

use TEST
SELECT
OBJECT_NAME(si.object_id) AS object_name
,pf.NAME AS pf_name
,ps.NAME AS partition_scheme_name
,p.partition_number
,ds.NAME AS partition_filegroup
,rv.value AS range_value
,(
CASE pf.boundary_value_on_right
WHEN 0
THEN 'RAGE_LEFT'
ELSE 'RANGE_RIGHT'
END
) AS range_direction
,SUM(CASE
WHEN si.index_id IN (
1
,0
)
THEN p.rows
ELSE 0
END) AS num_rows
FROM sys.destination_data_spaces AS dds
INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
AND dds.destination_id = CASE pf.boundary_value_on_right
WHEN 0
THEN rv.boundary_id
ELSE rv.boundary_id + 1
END
LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
AND si.index_id = p.index_id
AND  dds.destination_id = p.partition_number
LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id 
AND p.partition_id= dbps.partition_id
WHERE si.object_id = OBJECT_ID('[dbo].[PartTable]')
GROUP BY ds.NAME
,p.partition_number
,pf.NAME
,pf.type_desc
,pf.fanout
,pf.boundary_value_on_right
,ps.NAME
,si.object_id 
,rv.value
 
ORDER BY p.partition_number

İlk partition’da NULL gözükmesi, Partition Function’da en küçük sınır değeri vermediğimiz için değil,
en büyük sınırdan (2026-12-31) sonraki veriler için yeni bir partition gerektiği içindir. Başta değerin null olması sıkıntıya sebep vermez.

Bunu önlemek istiyorsan:
• Ya 2027 için de ay ay boundary değerleri eklersin (2027-01-31, 2027-02-28, vs.)
• Ya da sistemin en son partition’a atanmasına izin verirsin (şu anki gibi).

Tablomuza insert cümleleri ekliyoruz.

INSERT INTO dbo.PartTable (AD, SOYAD, TARIH) VALUES ('Ahmet', 'Kaya', '2020-07-31');
INSERT INTO dbo.PartTable (AD, SOYAD, TARIH) VALUES ('Ayşe', 'Demir', '2020-08-31');
INSERT INTO dbo.PartTable (AD, SOYAD, TARIH) VALUES ('Mehmet', 'Çelik', '2020-09-30');
INSERT INTO dbo.PartTable (AD, SOYAD, TARIH) VALUES ('Fatma', 'Şahin', '2020-10-31');
INSERT INTO dbo.PartTable (AD, SOYAD, TARIH) VALUES ('Ali', 'Aydın', '2020-11-30');
INSERT INTO dbo.PartTable (AD, SOYAD, TARIH) VALUES ('Zeynep', 'Yılmaz', '2020-12-31');

INSERT INTO dbo.PartTable (AD, SOYAD, TARIH) VALUES ('Ahmet', 'Yılmaz', '2021-01-31');
INSERT INTO dbo.PartTable (AD, SOYAD, TARIH) VALUES ('Ayşe', 'Kaya', '2021-02-28');
INSERT INTO dbo.PartTable (AD, SOYAD, TARIH) VALUES ('Mehmet', 'Demir', '2021-03-31');

Gerçek veritabanımızla aynı yapıda arşiv tablosu oluşturuyorum.

CREATE TABLE dbo.PartTable_Arsiv (
    ID INT NOT NULL,
    AD VARCHAR(50) NULL,
    SOYAD VARCHAR(50) NULL,
    TARIH DATETIME NOT NULL
)
ON ps_Tarih(TARIH);

Not: PartTable_Arsiv tablosunda IDENTITY özelliği yok çünkü SWITCH işlemi yapılacak. Aynı yapıda ve aynı index’lerle olması gerekir.

Veri partition’ları zamanla artacak ve her ay sonu yeni bir veri eklemesi yapıldıkça, her bir partition için SPLIT işlemi gerçekleştirilmesi gerekecektir. Bu işlemin her ay düzenli bir şekilde yapılabilmesi için SQL Server Agent Job kullanarak otomatik bir işlem kurmak oldukça faydalıdır.

Aşağıdaki script, her ay sonunda mevcut partition scheme üzerinde yeni bir ay için SPLIT işlemi yapacak şekilde hazırlanmıştır:

-- Partition Function ve Scheme adlarını kullanarak SPLIT işlemi
DECLARE @splitDate DATETIME;

-- Sonraki ayın ilk günü
SET @splitDate = DATEADD(MONTH, 1, GETDATE());
SET @splitDate = DATEFROMPARTS(YEAR(@splitDate), MONTH(@splitDate), 1);

-- SPLIT işlemi
ALTER PARTITION FUNCTION pf_Tarih()
SPLIT RANGE (@splitDate);

-- Mesajla işlemin başarılı olduğunu bildir
PRINT 'Partition successfully split at: ' + CONVERT(VARCHAR(10), @splitDate, 120);

Bu script, her ay sonunda yeni bir partition oluşturulmasını ve veri eklenmesini sağlar. SQL Server Agent Job içerisinde bu script’i her ay başı tetikleyecek şekilde bir zamanlayıcı ayarlayarak otomatik hale getirebilirsiniz.

Yukarıdaki kodun doğru olup olmadığını anlamak için kontrol ederek manuel bir şekilde split işlemi yapalım. Split işlemi yapmadan önceki ekran resmi:

Ocak ayı için manuel bu değeri çalıştırdığımda başarılı bir şekilde split işlemi yapılmaktadır.

-- Partition Function ve Scheme adlarını kullanarak SPLIT işlemi
DECLARE @splitDate DATETIME;

SET @splitDate = '2027-02-31';  -- 2027 yılı Ocak ayının son günü

-- SPLIT işlemi Ocak 2027
ALTER PARTITION FUNCTION pf_Tarih()
SPLIT RANGE (@splitDate);

-- İşlemin başarılı olduğunu bildir
PRINT 'Partition successfully split at: ' + CONVERT(VARCHAR(10), @splitDate, 120);

Warning: The partition scheme ‘ps_Tarih’ does not have any next used filegroup. Partition scheme has not been changed.

Eğer partition scheme’in next used filegroup olmadığına dair bir uyarı alıyorsanız, yeni bir filegroup eklemeniz gerekebilir. Bu, partition fonksiyonunuzun doğru çalışabilmesi için gerekli olabilir.

ALTER DATABASE [TEST] ADD FILEGROUP [FG_2028]
GO
ALTER DATABASE [TEST] ADD FILE ( NAME = N'FG_2028', FILENAME = N'C:\PART\FG_2028.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_2028]
GO

Şuna dikkat edilmesi lazım manuel split işlemi yapılmadan önce İlgili partition scheme’ya oluşturduğumuz file group’un eklenmesi gerekmektedir.

ALTER PARTITION SCHEME ps_Tarih
NEXT USED FG_2027;

Manuel 2027 yılı şubat ayı için tekrar gerçekleştirelim.

-- Partition Function ve Scheme adlarını kullanarak SPLIT işlemi
DECLARE @splitDate DATETIME;

-- Geçerli tarih: 2027 yılı Şubat ayının son günü (28 Şubat)
SET @splitDate = '2027-02-28';  

-- SPLIT işlemi Şubat 2027
ALTER PARTITION FUNCTION pf_Tarih()
SPLIT RANGE (@splitDate);

-- İşlemin başarılı olduğunu bildir
PRINT 'Partition successfully split at: ' + CONVERT(VARCHAR(10), @splitDate, 120);

Yıl sonunda veriler, sliding window yöntemiyle bir tabloya aktarılmalıdır. Bu işlemde, geçerli yılın verileri başka bir tabloya aktarılacak, ve daha sonra MERGE işlemiyle eski partition silinecektir. Bu işlemi her yıl sonunda gerçekleştirmek için aşağıdaki SQL kodunu kullanabiliriz:

Yıl Sonu SWITCH İşlemi

Bu işlem, mevcut yılın tüm verilerini PartTable tablosundan alıp, bir yıl sonu arşiv tablosuna aktarır. Aşağıda bir örnek bulunmaktadır:

ALTER TABLE dbo.PartTable
SWITCH PARTITION 12 TO dbo.PartTable_Arsiv PARTITION 12; -- 12 numaralı partition, 2020 yılının sonunu temsil eder.
DECLARE @YearEnd DATETIME = '2020-12-31';
ALTER PARTITION FUNCTION pf_Tarih()
MERGE RANGE (@YearEnd); -- Yıl sonu range'ini birleştiriyoruz.

Taşınma işleminden sonra ilgili partition silinmektedir.

Bu makalede adım adım oluşturduğumuz yapı ile SQL Server üzerinde hem yüksek performanslı bir veri okuma/yazma ortamı yaratmış olduk, hem de veri yaşam döngüsünü otomatik yönetebileceğimiz sürdürülebilir bir sistem yapı oluşturduk.

Partition Function, Partition Scheme, Filegroup yönetimi ve SWITCH/MERGE operasyonları ile verilerimizi yıllara ve aylara göre bölerek hem sistem kaynaklarını daha verimli kullanmayı hem de arşivleme gibi uzun vadeli operasyonları otomatikleştirmeyi yapmış olduk.

Zaman bazlı partitioning ve sliding window mantığı, özellikle log, işlem veya günlük gibi büyüyen verilerde SQL Server’ın sunduğu en güçlü araçlardan biridir. Bu yapıyı kendi sistemlerinize uyarlayarak, hem performansınızı artırabilir hem de bakım süreçlerinizi sadeleştirebilirsiniz.

Başka makalede görüşmek dileğiyle..

*Göklerde ve yerde ne varsa hepsi Allah’ındır. Allah’ın ilmi ve kudreti her şeyi kuşatmıştır. Nisa-126*

Görüntüleme: 54
Author: Yunus YÜCEL

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir