Ayrılmış SQL havuzundaki tabloları bölümleme
Ayrılmış SQL havuzunda tablo bölümlerini kullanmaya yönelik öneriler ve örnekler.
Tablo bölümleri nedir?
Tablo bölümleri, verilerinizi daha küçük veri gruplarına bölmenizi sağlar. Çoğu durumda tablo bölümleri bir tarih sütununda oluşturulur. Bölümleme tüm ayrılmış SQL havuzu tablo türlerinde desteklenir; kümelenmiş columnstore, kümelenmiş dizin ve yığın dahil. Bölümleme hem karma hem de hepsini bir kez deneme dağıtılmış da dahil olmak üzere tüm dağıtım türlerinde de desteklenir.
Bölümleme, veri bakımı ve sorgu performansından yararlanabilir. Hem fayda sağlar hem de yalnızca bir tane, verilerin nasıl yüklendiğine ve aynı sütunun her iki amaç için de kullanılıp kullanılamayabileceğine bağlıdır, çünkü bölümleme yalnızca bir sütunda yapılabilir.
Yüklerin avantajları
Ayrılmış SQL havuzunda bölümlemenin birincil avantajı, bölüm silme, değiştirme ve birleştirmeyi kullanarak veri yükleme verimliliğini ve performansını artırmaktır. Çoğu durumda veriler, verilerin SQL havuzuna yüklenme sırasına yakından bağlı bir tarih sütununda bölümlenmiştir. Verileri korumak için bölümleri kullanmanın en büyük avantajlarından biri, işlem günlüğünden kaçınmadır. Verileri eklemek, güncelleştirmek veya silmek en basit yaklaşım olsa da, biraz düşünmek ve çabayla, yükleme işlemi sırasında bölümleme kullanmak performansı önemli ölçüde artırabilir.
Bölüm değiştirme, tablonun bir bölümünü hızla kaldırmak veya değiştirmek için kullanılabilir. Örneğin, bir satış olgu tablosu yalnızca son 36 aya ilişkin verileri içerebilir. Her ayın sonunda, satış verilerinin en eski ayı tablodan silinir. Bu veriler, en eski aya ilişkin verileri silmek için delete deyimi kullanılarak silinebilir.
Bununla birlikte, delete deyimiyle satır satır büyük miktarda verinin silinmesi çok fazla zaman alabilir ve bir sorun olması durumunda geri alınması uzun süren büyük işlemler riski oluşturabilir. En iyi yaklaşım, verilerin en eski bölümünü bırakmaktır. Tek tek satırları silme işleminin saatler sürebileceği durumlarda, bölümün tamamının silinmesi saniyeler sürebilir.
Sorguların avantajları
Bölümleme, sorgu performansını geliştirmek için de kullanılabilir. Bölümlenmiş verilere filtre uygulayan bir sorgu, taramayı yalnızca uygun bölümlerle sınırlayabilir. Bu filtreleme yöntemi tam tablo taramasını önleyebilir ve yalnızca daha küçük bir veri alt kümesini tarar. Kümelenmiş columnstore dizinlerinin kullanıma sunulmasıyla, koşul ortadan kaldırma performansı avantajları daha az faydalıdır, ancak bazı durumlarda sorgular için bir avantaj olabilir.
Örneğin, satış olgu tablosu satış tarihi alanı kullanılarak 36 aya bölünmüşse, satış tarihine göre filtreleyen sorgular filtreyle eşleşmeyen bölümlerde aramayı atlayabilir.
Bölüm boyutlandırma
Bölümleme bazı senaryolarda performansı geliştirmek için kullanılabilir ancak çok fazla bölüm içeren bir tablo oluşturmak bazı durumlarda performansı etkileyebilir. Bu endişeler özellikle kümelenmiş columnstore tabloları için geçerlidir.
Bölümlemenin yararlı olması için, bölümlemenin ne zaman kullanılacağını ve oluşturulacak bölüm sayısını anlamak önemlidir. Kaç bölümün çok fazla olduğu konusunda hızlı bir kural yoktur, verilerinize ve aynı anda kaç bölüm yüklediğinize bağlıdır. Başarılı bir bölümleme şemasında genellikle binlerce bölüm değil, onlarca ile yüzlerce bölüm bulunur.
Kümelenmiş columnstore tablolarında bölümler oluştururken, her bölüme ait olan satır sayısını göz önünde bulundurmak önemlidir. Kümelenmiş columnstore tablolarının en iyi sıkıştırması ve performansı için dağıtım ve bölüm başına en az 1 milyon satır gerekir. Bölümler oluşturulmadan önce ayrılmış SQL havuzu zaten her tabloyu 60 dağıtıma böler.
Tabloya eklenen tüm bölümlemeler, arka planda oluşturulan dağıtımlara ek olarak yapılır. Bu örneği kullanarak, satış olgu tablosunda aylık 36 bölüm varsa ve ayrılmış bir SQL havuzunun 60 dağıtımı olduğu düşünüldüğünde, satış olgu tablosu ayda 60 milyon satır veya tüm aylar doldurulduğunda 2,1 milyar satır içermelidir. Bir tabloda bölüm başına önerilen en az satır sayısından daha az satır varsa, bölüm başına satır sayısını artırmak için daha az bölüm kullanmayı göz önünde bulundurun.
Daha fazla bilgi için küme columnstore dizinlerinin kalitesini değerlendirebilen sorguları içeren Dizin oluşturma makalesine bakın.
SQL Server söz dizimi farklılıkları
Ayrılmış SQL havuzu, SQL Server daha basit bölümleri tanımlamanın bir yolunu sağlar. Bölümleme işlevleri ve düzenleri, SQL Server oldukları için ayrılmış SQL havuzunda kullanılmaz. Bunun yerine tek yapmanız gereken bölümlenmiş sütunu ve sınır noktalarını belirlemektir.
Bölümlemenin söz dizimi SQL Server biraz farklı olsa da temel kavramlar aynıdır. SQL Server ve ayrılmış SQL havuzu tablo başına bir bölüm sütununu destekler ve bu sütun bölüm aralığına eklenebilir. Bölümleme hakkında daha fazla bilgi edinmek için bkz . Bölümlenmiş Tablolar ve Dizinler.
Aşağıdaki örnek, tabloyu sütunda bölümlendirmek FactInternetSales için CREATE TABLE deyimini OrderDateKey kullanır:
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101,20020101
,20030101,20040101,20050101
)
)
);
Bölümleri SQL Server'den geçirme
SQL Server bölüm tanımlarını ayrılmış SQL havuzuna geçirmek için:
- SQL Server bölüm düzenini ortadan kaldırın.
- CREATE TABLE'nıza bölüm işlevi tanımını ekleyin.
Bölümlenmiş tabloyu bir SQL Server örneğinden geçiriyorsanız, aşağıdaki SQL her bölümdeki satır sayısını öğrenmenize yardımcı olabilir. Ayrılmış SQL havuzunda aynı bölümleme ayrıntı düzeyi kullanılırsa bölüm başına satır sayısının 60 kat azaldığını unutmayın.
-- Partition information for a SQL Server Database
SELECT s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, p.[partition_number] AS [partition_number]
, SUM(a.[used_pages]*8.0) AS [partition_size_kb]
, SUM(a.[used_pages]*8.0)/1024 AS [partition_size_mb]
, SUM(a.[used_pages]*8.0)/1048576 AS [partition_size_gb]
, p.[rows] AS [partition_row_count]
, rv.[value] AS [partition_boundary_value]
, p.[data_compression_desc] AS [partition_compression_desc]
FROM sys.schemas s
JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
JOIN sys.partitions p ON p.[object_id] = t.[object_id]
JOIN sys.allocation_units a ON a.[container_id] = p.[partition_id]
JOIN sys.indexes i ON i.[object_id] = p.[object_id]
AND i.[index_id] = p.[index_id]
JOIN sys.data_spaces ds ON ds.[data_space_id] = i.[data_space_id]
LEFT JOIN sys.partition_schemes ps ON ps.[data_space_id] = ds.[data_space_id]
LEFT JOIN sys.partition_functions pf ON pf.[function_id] = ps.[function_id]
LEFT JOIN sys.partition_range_values rv ON rv.[function_id] = pf.[function_id]
AND rv.[boundary_id] = p.[partition_number]
WHERE p.[index_id] <=1
GROUP BY s.[name]
, t.[name]
, i.[name]
, p.[partition_number]
, p.[rows]
, rv.[value]
, p.[data_compression_desc];
Bölüm değiştirme
Ayrılmış SQL havuzu bölüm bölmeyi, birleştirmeyi ve değiştirmeyi destekler. Bu işlevlerin her biri ALTER TABLE deyimi kullanılarak yürütülür.
bölümleri iki tablo arasında değiştirmek için bölümlerin ilgili sınırlarına uygun olduğundan ve tablo tanımlarının eşleştiğinden emin olmanız gerekir. Tablodaki değer aralığını zorlamak için denetim kısıtlamaları kullanılamadığından, kaynak tablo hedef tabloyla aynı bölüm sınırlarını içermelidir. Bölüm sınırları aynı değilse, bölüm meta verileri eşitlenmediğinden bölüm anahtarı başarısız olur.
Kümelenmiş columnstore dizini (CCI) varsa bölüm bölme için ilgili bölümün (tablonun tamamının olması gerekmez) boş olması gerekir. Aynı tablodaki diğer bölümler veri içerebilir. Veri içeren bir bölüm bölünemez, hataya neden olur: ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete. Veri içeren bir bölümü bölmeye yönelik geçici bir çözüm olarak bkz. Veri içeren bir bölümü bölme.
Veri içeren bir bölümü bölme
Zaten veri içeren bir bölümü bölmenin en verimli yöntemi deyimini CTAS kullanmaktır. Bölümlenmiş tablo kümelenmiş bir columnstore ise, bölmeden önce tablo bölümünün boş olması gerekir.
Aşağıdaki örnek bölümlenmiş bir columnstore tablosu oluşturur. Her bölüme bir satır ekler:
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
);
INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);
INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);
Aşağıdaki sorgu, katalog görünümünü kullanarak sys.partitions satır sayısını bulur:
SELECT QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
, i.[name] as Index_name
, p.partition_number as Partition_nmbr
, p.[rows] as Row_count
, p.[data_compression_desc] as Data_Compression_desc
FROM sys.partitions p
JOIN sys.tables t ON p.[object_id] = t.[object_id]
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
JOIN sys.indexes i ON p.[object_id] = i.[object_Id]
AND p.[index_Id] = i.[index_Id]
WHERE t.[name] = 'FactInternetSales';
Aşağıdaki bölünmüş komut bir hata iletisi alır:
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Msg 35346, Level 15, State 1, Line 44
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.
Ancak, verileri barındıracak yeni bir tablo oluşturmak için kullanabilirsiniz CTAS .
CREATE TABLE dbo.FactInternetSales_20000101
WITH ( DISTRIBUTION = HASH(ProductKey)
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
)
AS
SELECT *
FROM FactInternetSales
WHERE 1=2;
Bölüm sınırları hizalandığında bir anahtara izin verilir. Bu, kaynak tabloda daha sonra bölebileceğiniz boş bir bölüm bırakır.
ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Geriye kalan tek şey, kullanarak CTASverileri yeni bölüm sınırlarına hizalamak ve ardından verileri ana tabloya geri döndürmektir.
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales_20000101]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101;
ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;
Verilerin hareketini tamamladıktan sonra hedef tablodaki istatistikleri yenilemek iyi bir fikirdir. İstatistiklerin güncelleştirilmesi, istatistiklerin ilgili bölümlerdeki verilerin yeni dağılımını doğru bir şekilde yansıtmasını sağlar.
UPDATE STATISTICS [dbo].[FactInternetSales];
Son olarak, verileri taşımak için tek seferlik bir bölüm anahtarı söz konusu olduğunda, bölüm anahtarı FactInternetSales_20000101_20010101 için oluşturulan tabloları ve FactInternetSales_20000101bırakabilirsiniz. Alternatif olarak, normal, otomatik bölüm anahtarları için boş tablolar tutmak isteyebilirsiniz.
Yeni verileri tek adımda veri içeren bölümlere yükleme
Bölüm değiştirme ile verileri bölümlere yüklemek, yeni verileri kullanıcılara görünmeyen bir tabloda hazırlamanın kullanışlı bir yoludur. Bölüm değiştirme ile ilişkili kilitleme çekişmesiyle başa çıkmak meşgul sistemlerde zor olabilir.
Bir bölümdeki mevcut verileri temizlemek için, ALTER TABLE verileri değiştirmek için kullanılır. Daha sonra yeni verilerde geçiş yapmak için başka bir ALTER TABLE gereklilik vardı.
Ayrılmış SQL havuzunda seçeneği TRUNCATE_TARGET komutunda ALTER TABLE desteklenir. komutuyla TRUNCATE_TARGETALTER TABLE bölümdeki mevcut verilerin üzerine yeni veri yazar. Aşağıda, mevcut verilerle yeni bir tablo oluşturmak, yeni veriler eklemek, ardından tüm verileri hedef tabloya geri geçirmek ve var olan verilerin üzerine yazmak için kullanan CTAS bir örnek verilmiştir.
CREATE TABLE [dbo].[FactInternetSales_NewSales]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101
;
INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);
ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);
Tablo bölümleme kaynak denetimi
Not
Kaynak denetim aracınız bölüm şemalarını yoksayacak şekilde yapılandırılmamışsa, bir tablonun şemasını bölümleri güncelleştirecek şekilde değiştirmek, dağıtımın bir parçası olarak bir tablonun bırakılmasına ve yeniden oluşturulmasına neden olabilir ve bu mümkün olmayabilir. Aşağıda açıklandığı gibi, böyle bir değişikliği uygulamak için özel bir çözüm gerekebilir. Sürekli tümleştirme/sürekli dağıtım (CI/CD) aracının buna izin verdiğinden denetleyin. SQL Server Veri Araçları'de (SSDT), bir tablonun bırakılmasına ve yeniden oluşturulmasına neden olan oluşturulan bir betiği önlemek için Gelişmiş Yayımlama Ayarları "Bölüm şemalarını yoksay" seçeneğini arayın.
Bu örnek, boş bir tablonun bölüm şemalarını güncelleştirirken yararlıdır. Bölüm değişikliklerini verileri içeren bir tabloya sürekli olarak dağıtmak için, bölüm SPLIT RANGE uygulamadan önce verileri geçici olarak her bölümden dışarı taşımak için dağıtımla birlikte veri içeren bir bölümü bölme başlığındaki adımları izleyin. CI/CD aracı hangi bölümlerin veriye sahip olduğunu bilmiyor olduğundan bu gereklidir.
Tablo tanımınızın kaynak denetim sisteminizde paslanmasını önlemek için aşağıdaki yaklaşımı göz önünde bulundurmak isteyebilirsiniz:
Tabloyu bölümlenmiş tablo olarak oluşturun ancak bölüm değerleri olmadan
CREATE TABLE [dbo].[FactInternetSales] ( [ProductKey] int NOT NULL , [OrderDateKey] int NOT NULL , [CustomerKey] int NOT NULL , [PromotionKey] int NOT NULL , [SalesOrderNumber] nvarchar(20) NOT NULL , [OrderQuantity] smallint NOT NULL , [UnitPrice] money NOT NULL , [SalesAmount] money NOT NULL ) WITH ( CLUSTERED COLUMNSTORE INDEX , DISTRIBUTION = HASH([ProductKey]) , PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES () ) );SPLITdağıtım işleminin bir parçası olarak tablo:-- Create a table containing the partition boundaries CREATE TABLE #partitions WITH ( LOCATION = USER_DB , DISTRIBUTION = HASH(ptn_no) ) AS SELECT ptn_no , ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no FROM ( SELECT CAST(20000101 AS INT) ptn_no UNION ALL SELECT CAST(20010101 AS INT) UNION ALL SELECT CAST(20020101 AS INT) UNION ALL SELECT CAST(20030101 AS INT) UNION ALL SELECT CAST(20040101 AS INT) ) a; -- Iterate over the partition boundaries and split the table DECLARE @c INT = (SELECT COUNT(*) FROM #partitions) , @i INT = 1 --iterator for while loop , @q NVARCHAR(4000) --query , @p NVARCHAR(20) = N'' --partition_number , @s NVARCHAR(128) = N'dbo' --schema , @t NVARCHAR(128) = N'FactInternetSales' --table; WHILE @i <= @c BEGIN SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i); SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');'); -- PRINT @q; EXECUTE sp_executesql @q; SET @i+=1; END -- Code clean-up DROP TABLE #partitions;
Bu yaklaşımla, kaynak denetimindeki kod statik kalır ve bölümleme sınırı değerlerinin dinamik olması sağlanır; zaman içinde SQL havuzuyla gelişiyor.
Sonraki adımlar
Tablo geliştirme hakkında daha fazla bilgi için Tabloya Genel Bakış makalelerine bakın.