Ayrılmış SQL havuzundaki tabloları bölümleme

Ayrılmış SQL havuzunda tablo bölümlerini kullanmaya yönelik öneriler ve örnekler.

What are table partitions?

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, kümelenmiş sütun deposu, kümelenmiş dizin ve yığın dahil olmak üzere tüm ayrılmış SQL havuzu tablo türleri arasında desteklenir. Bölümleme, hem hash hem de round robin dağıtımlı olmak üzere tüm dağıtım türlerinde desteklenir.

Bölümleme, veri bakımı ve sorgu performansından yararlanabilir. Hem fayda sağlayıp sağlamadığı hem de yalnızca bir tane olması, verilerin nasıl yüklendiğine ve bölümleme yalnızca bir sütunda yapılabildiğinden aynı sütunun her iki amaçla da kullanılıp kullanılamayabileceğine bağlıdır.

Yüklere sağlanan faydalar

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üğünün engellenmiş olmasıdır. Verileri eklemek, güncelleştirmek veya silmek en basit yaklaşım olsa da, biraz düşünmek ve çabayla, yükleme işleminiz 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, en eski satış verileri tablosundan silinir. Bu veriler, en eski ayın verilerini silmek için delete deyimi kullanılarak silinebilir.

Ancak, delete deyimiyle büyük miktarda veriyi satır satır silmek çok fazla zaman alabilir ve bir sorun oluştuğunda geri alınması uzun süren büyük işlemler riski oluşturabilir. En uygun yaklaşım, en eski veri bölümünü bırakmaktır. Tek tek satırları silme işleminin saatler sürebileceği durumlarda, bir bölümün tamamının silinmesi saniyeler sürebilir.

Benefits to queries

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 yararlı olur, 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ış tarihini 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; bu, 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 değil, onlarca ile yüzlerce bölüm bulunur.

Kümelenmiş columnstore tablolarında bölümler oluştururken, her bölüme kaç satır ait olduğunu göz önünde bulundurmanız ö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 sağlanı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 tablo, bölüm başına önerilen en az satır sayısından daha az satır içeriyorsa, 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.

Syntax differences from SQL Server

Ayrılmış SQL havuzu, SQL Server'dan daha basit bölümleri tanımlamanın bir yolunu tanıtır. Bölümleme işlevleri ve düzenleri, SQL Server'da olduğu gibi ayrılmış SQL havuzunda kullanılmaz. Bunun yerine tek yapmanız gereken bölümlenmiş sütunu ve sınır noktalarını tanımlamaktır.

Bölümlemenin söz dizimi SQL Server'dan biraz farklı olsa da, temel kavramlar aynıdır. SQL Server ve ayrılmış SQL havuzu her tablo için bir bölümleme sütununu destekler ve bu sütun aralık bölümlemesi olabilir. Bölümleme hakkında daha fazla bilgi edinmek için bkz . Bölümlenmiş Tablolar ve Dizinler.

Aşağıdaki örnekte, CREATE TABLE deyimi FactInternetSales tablosunu OrderDateKey sütununda bölümlemek için kullanılı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'dan 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ş bir tabloyu 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ı zorunlu kılmak 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şitlenmeyeceği için bölüm değişimi başarısız olur.

Tablonun kümelenmiş columnstore dizini (CCI) varsa, bir bölüm bölme işlemi, yalnızca ilgili bölümün boş olmasını gerektirir (tablonun tamamının boş olması gerekmez). Aynı tablodaki diğer bölümler veri içerebilir. Bir veri bölümü bölünemez, bu bir hataya neden olur. Veri içeren bir bölümü bölmeye yönelik bir geçici çözüm için 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. kısmına bakın.

Veri içeren bir bölümü bölme

Zaten veri içeren bir bölümü bölmenin en verimli yöntemi bir CTAS deyimi 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 partisyona 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;

As the partition boundaries are aligned, a switch is permitted. Bu, kaynak tabloyu daha sonra bölebileceğiniz boş bir bölümle bırakır.

ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

Tek kalan, kullanarak CTASverileri yeni bölüm sınırlarına hizalamak ve ardından verileri yeniden ana tabloya değiştirmektir.

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ı geçişi söz konusu olduğunda, FactInternetSales_20000101_20010101 ve FactInternetSales_20000101 için oluşturulan tabloları bırakabilirsiniz. Alternatif olarak, normal, otomatik bölüm anahtarları için boş tablolar tutmak isteyebilirsiniz.

Bir adımda veri içeren bölümlere yeni veri yükleme

Bölüm değiştirme ile bölümlere veri yüklemek, kullanıcılar tarafından görülmeyecek bir tabloda yeni veriler 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.

To clear out the existing data in a partition, an ALTER TABLE used to be required to switch out the data. Sonra yeni verilere geçiş yapmak için başka bir ALTER TABLE gerekiyordu.

Ayrılmış SQL havuzunda TRUNCATE_TARGET seçeneği, ALTER TABLE komutunda desteklenir. TRUNCATE_TARGET ALTER TABLE komutuyla bölmedeki mevcut verilerin üzerine yeni veri yazar. Aşağıda, mevcut verilerle yeni bir tablo oluşturmak, yeni veriler eklemek ve ardından tüm verileri hedef tabloya geri geçirerek 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);  

Table partitioning source control

Note

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 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 gerekli olabilir. Sürekli tümleştirme/sürekli dağıtım (CI/CD) aracının buna izin verdiğinden denetleyin. In SQL Server Data Tools (SSDT), look for the Advanced Publish Settings "Ignore partition schemes" to avoid a generated script that causes a table to be dropped and recreated.

Bu örnek, boş bir tablonun bölüm şemalarını güncelleştirirken yararlıdır. Verileri içeren bir tablodaki bölüm değişikliklerini sürekli dağıtmak için, Veri içeren bir bölümü nasıl bölersiniz başlıklı adımları izleyin. Bölüm SPLIT RANGE'yi uygulamadan önce her bölümden verileri geçici olarak dışarı taşımak için bu adımlar, dağıtımla birlikte gerçekleştirilmelidir. Bu, CI/CD aracı hangi bölümlerin veriye sahip olduğunu bilmediği için 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:

1. 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 () )
   );
   

2. SPLIT the table as part of the deployment process:

    -- 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.

İlgili içerik

Tablo geliştirme hakkında daha fazla bilgi için bkz. Tabloya Genel Bakış.