Havuzdaki ayrılmış SQL tablolarında dizinler Azure Synapse Analytics

Öneriler ayrılmış havuzdaki tabloları dizine eklemeye SQL örnekler Azure Synapse Analytics.

Dizin türleri

Ayrılmış SQL havuzu kümelenmiş columnstoredizinleri, kümelenmiş dizinler ve kümelenmiş olmayan dizinler ve yığın olarak da bilinen dizin dışı bir seçenek de dahil olmak üzere çeşitli dizinleme seçenekleri sunar.

Dizine sahip bir tablo oluşturmak için CREATE TABLE (ayrılmış SQL havuzu) belgelerine bakın.

Kümelenmiş columnstore dizinleri

Varsayılan olarak, ayrılmış SQL bir tabloda dizin seçeneği belirtilmedinde kümelenmiş bir columnstore dizini oluşturur. Kümelenmiş columnstore tabloları hem en yüksek veri sıkıştırma düzeyini hem de en iyi genel sorgu performansını sunar. Kümelenmiş columnstore tabloları genellikle kümelenmiş dizin veya yığın tablolardan daha iyi performans gösterir ve genellikle büyük tablolar için en iyi seçenektir. Bu nedenlerden dolayı tabloyu dizinleme konusunda emin olmadığınız kümelenmiş columnstore en iyi başlangıç yerdir.

Kümelenmiş columnstore tablosu oluşturmak için WITH yan tümcesinde belirtin CLUSTERED COLUMNSTORE INDEX veya WITH yan tümcesini kapalı bırakın:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX );

Kümelenmiş columnstore'ın iyi bir seçenek olmadığını birkaç senaryo vardır:

• Columnstore tabloları varchar(max), nvarchar(max) ve varbinary(max) tablolarını desteklemez. Bunun yerine yığın veya kümelenmiş dizini göz önünde bulundurabilirsiniz.
• Columnstore tabloları geçici veriler için daha az verimli olabilir. Yığın ve belki de geçici tabloları göz önünde bulundurabilirsiniz.
• 60 milyondan az satıra sahip küçük tablolar. Yığın tablolarını göz önünde bulundurabilirsiniz.

Yığın tabloları

Verileri geçici olarak ayrılmış bir havuza SQL, yığın tablosu kullanmanın genel işlemi daha hızlı tamamlar. Bunun nedeni, yığınlara yapılan yüklemelerin tabloları dizine eklemeden daha hızlı olması ve bazı durumlarda önbellekten sonraki okumanın yapılab olmasıdır. Daha fazla dönüştürme çalıştırmadan önce verileri yalnızca aşamaya yüklerken, tabloyu yığın tablosuna yükleme, verileri kümelenmiş columnstore tablosuna yüklemeye göre çok daha hızlıdır. Ayrıca, geçici bir tabloya veri yükleme, tabloyu kalıcı depolama alanına yüklemeye göre daha hızlı yüklenir. Veri yükledikten sonra daha hızlı sorgu performansı için tabloda dizinler oluşturabilirsiniz.

Küme columnstore tabloları, 60 milyondan fazla satır olduktan sonra en iyi sıkıştırmayı elde etmek için başlar. 60 milyondan az satır içeren küçük arama tabloları için, daha hızlı sorgu performansı için HEAP veya kümelenmiş dizin kullanmayı göz önünde bulundurabilirsiniz.

Yığın tablosu oluşturmak için WITH yan tümcesinde HEAP belirtmeniz gerekir:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( HEAP );

Kümelenmiş ve kümelenmiş olmayan dizinler

Kümelenmiş dizinler, tek bir satırın hızla alınarak kümelenmiş columnstore tablolarına göre daha iyi performans gösterir. Çok hızlı bir şekilde gerçekleştirmek için tek veya çok az satır aramanın gerekli olduğu sorgular için kümelenmiş bir dizin veya kümelenmiş olmayan ikincil dizini göz önünde bulundurabilirsiniz. Kümelenmiş dizin kullanmanın dezavantajı, yalnızca kümelenmiş dizin sütununda yüksek oranda seçmeli filtre kullanan sorgular olmasıdır. Diğer sütunlarda filtreyi geliştirmek için, diğer sütunlara ekli olmayan bir dizin eklenebilir. Ancak, bir tabloya eklenen her dizin hem alan hem de işlem süresi yükler.

Kümelenmiş dizin tablosu oluşturmak için WITH yan tümcesinde CLUSTERED INDEX belirtmeniz gerekir:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED INDEX (id) );

Bir tabloya kümelenmiş olmayan bir dizin eklemek için aşağıdaki sözdizimini kullanın:

CREATE INDEX zipCodeIndex ON myTable (zipCode);

Kümelenmiş columnstore dizinlerini iyileştirme

Kümelenmiş columnstore tabloları, verilerde segmentler olarak düzenlenmiştir. Yüksek segment kalitesine sahip olmak, columnstore tablosunda en iyi sorgu performansını elde etmek için kritik öneme sahip. Segment kalitesi, sıkıştırılmış satır grubunda yer alan satır sayısına göre ölçülebilir. Segment kalitesi, sıkıştırılmış satır grubu başına en az 100 bin satır olduğu ve satır grubu başına satır sayısı 1.048.576 satır yaklaşımıyla performansta kazanç elde etmek için en uygun olandır. Bu, bir satır grubunun içereceği en çok satırdır.

Aşağıdaki görünüm, sisteminiz üzerinde satır grubu başına ortalama satırları hesaplamak ve en iyi durumdaki tüm columnstore dizinlerini tanımlamak için oluşturulabilir ve kullanılabilir. Bu görünümde son sütun, dizinlerinizi yeniden SQL için kullanılan bir SQL deyimini üretir.

CREATE VIEW dbo.vColumnstoreDensity
AS
SELECT
        GETDATE()                                                               AS [execution_date]
,       DB_Name()                                                               AS [database_name]
,       s.name                                                                  AS [schema_name]
,       t.name                                                                  AS [table_name]
,    COUNT(DISTINCT rg.[partition_number])                    AS [table_partition_count]
,       SUM(rg.[total_rows])                                                    AS [row_count_total]
,       SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id])               AS [row_count_per_distribution_MAX]
,    CEILING    ((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX]
,       SUM(CASE WHEN rg.[State] = 0 THEN 1                   ELSE 0    END)    AS [INVISIBLE_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE 0    END)    AS [INVISIBLE_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 1 THEN 1                   ELSE 0    END)    AS [OPEN_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE 0    END)    AS [OPEN_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 2 THEN 1                   ELSE 0    END)    AS [CLOSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE 0    END)    AS [CLOSED_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 3 THEN 1                   ELSE 0    END)    AS [COMPRESSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE 0    END)    AS [COMPRESSED_rowgroup_rows]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows]   ELSE 0    END)    AS [COMPRESSED_rowgroup_rows_DELETED]
,       MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_AVG]
,       'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;'             AS [Rebuild_Index_SQL]
FROM    sys.[pdw_nodes_column_store_row_groups] rg
JOIN    sys.[pdw_nodes_tables] nt                   ON  rg.[object_id]          = nt.[object_id]
                                                    AND rg.[pdw_node_id]        = nt.[pdw_node_id]
                                                    AND rg.[distribution_id]    = nt.[distribution_id]
JOIN    sys.[pdw_table_mappings] mp                 ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[tables] t                              ON  mp.[object_id]          = t.[object_id]
JOIN    sys.[schemas] s                             ON t.[schema_id]            = s.[schema_id]
GROUP BY
        s.[name]
,       t.[name];

Görünümü oluşturduğunuza göre, 100.000'den az satırı olan satır gruplarına sahip tabloları tanımlamak için bu sorguyu çalıştırın. Daha uygun bir segment kalitesi arıyorsanız 100 K eşiğini artırmak istemeyebilirsiniz.

SELECT    *
FROM    [dbo].[vColumnstoreDensity]
WHERE    COMPRESSED_rowgroup_rows_AVG < 100000
        OR INVISIBLE_rowgroup_rows_AVG < 100000;

Sorguyu çalıştırdıktan sonra verilere göz atarak sonuçları analiz etmenize başlayabilirsiniz. Bu tabloda, satır grubu analizinde nelerin karşınıza bakılası açıkmektedir.

Dizin bakımının etkisi

Görünümde Rebuild_Index_SQL vColumnstoreDensity sütun, dizinlerinizi ALTER INDEX REBUILD yeniden oluşturmada kullanılan bir deyimi içerir. Dizinlerinizi yeniden oluşturma sırasında, dizininizi yeniden yapılandıran oturuma yeterli bellek ayıranın emin olun. Bunu yapmak için, bu tablodaki dizini yeniden oluşturma izni olan bir kullanıcının kaynak sınıfını önerilen en düşük değere kadar artırabilirsiniz. Örnek için bu makalenin devamlarında segment kalitesini artırmak için dizinleri yeniden oluşturma makalesine bakın.

Sıralanmış kümelenmiş columnstore dizini olan bir tablo için ALTER INDEX REBUILD tempdb kullanarak verileri yeniden sıralar. Yeniden oluşturma işlemleri sırasında tempdb'yi izleme. Daha fazla tempdb alanı gerekirse veritabanı havuzunun ölçeğini ölçeklendirin. Dizin yeniden oluşturma işlemi tamamlandıktan sonra ölçeği yeniden aşağı doğru ölçeklendirin.

Sıralanmış kümelenmiş columnstore dizini olan bir tablo ALTER INDEX REORGANIZE için verileri yeniden sıralamaz. Verileri yeniden sıralamak için ALTER INDEX REBUILD kullanın.

Sıralenmiş kümelenmiş columnstore dizinleri hakkında daha fazla bilgi için bkz. Sıralenmiş kümelenmiş columnstore diziniyle performans ayarlama.

Columnstore dizin kalitesinin düşük olmasının nedenleri

Düşük segment kalitesine sahip tablolar tanımladıysanız, kök nedeni belirlemek istiyorsanız. Düşük segment kalitesinin diğer yaygın nedenlerinden bazıları aşağıda verilmiştir:

1. Dizin 2019'da bellek baskısı
2. Yüksek hacimli DML işlemleri
3. Küçük veya küçük yük işlemleri
4. Çok fazla bölüm var

Bu faktörler columnstore dizininin satır grubu başına en uygun 1 milyon satırdan önemli ölçüde daha aza sahip hale gelir. Ayrıca satırların sıkıştırılmış satır grubu yerine delta satır grubuna gitmelerini de sılabilir.

Dizin 2019'da bellek baskısı

Sıkıştırılmış satır grubu başına satır sayısı doğrudan satırın genişliğiyle ve satır grubunu işlemeye yönelik kullanılabilir bellek miktarıyla bağlantılıdır. Satırlar columnstore tablolarına bellek baskısı altında yazıldığında, segment kalitesi düşebilir. Bu nedenle en iyi yöntem, columnstore dizin tablolarına yazan oturuma mümkün olduğunca fazla belleğe erişim vermektir. Bellek ve eşzamanlılık arasında bir fark olduğu için, doğru bellek ayırma kılavuzu tablonun her bir satırına, sisteminize ayrılan veri ambarı birimlerine ve tablonıza veri yazan oturuma verebilirsiniz eşzamanlılık yuvalarının sayısına bağlıdır.

Yüksek hacimli DML işlemleri

Satırları güncelleştiren ve sen yüksek hacimli DML işlemleri, columnstore'da verimciye neden olabilir. Bu durum özellikle bir satır grubunda yer alan satırların çoğu değiştirildiğinde gerçekleşir.

• Sıkıştırılmış satır grubundan bir satırı silmek, satırı yalnızca mantıksal olarak silinmiş olarak işaretler. Satır, bölüm veya tablo yeniden oluşturulmuş olana kadar sıkıştırılmış satır grubunda kalır.
• Satır eklemek, satırı delta satır grubu olarak adlandırılan iç rowstore tablosuna ekler. Eklenen satır, delta satır grubu dolu ve kapalı olarak işaretlenene kadar columnstore'a dönüştürülmesin. Satır grupları 1.048.576 satırlık maksimum kapasiteye ulaştıktan sonra kapatılır.
• Columnstore biçiminde bir satırı güncelleştirmek mantıksal silme ve ekleme olarak işlenir. Eklenen satır delta depolamada depolanmış olabilir.

Bölümle hizalı dağıtım başına toplu 102.400 satırlık toplu eşiği aşan toplu güncelleştirme ve ekleme işlemleri doğrudan columnstore biçimine gider. Ancak, çift bir dağıtımı varsayarak, bunun gerçekleşmesi için tek bir işlemde 6,144 milyondan fazla satırı değiştirmeniz gerekir. Bölümle hizalı bir dağıtımın satır sayısı 102.400'den azsa satırlar delta deposuna gider ve satır grubunu kapatmak için yeterli satır eklenene veya değiştirilene veya dizin yeniden derlenmiş olana kadar orada kalır.

Küçük veya küçük yük işlemleri

Ayrılmış havuza akan SQL yükler, bazen hileli yükler olarak da bilinir. Bunlar genellikle sistem tarafından alan neredeyse sabit bir veri akışını temsil ediyor. Ancak, bu akış sürekliye yakın olduğu için satır hacmi özellikle büyük değildir. Çoğu durumda veriler, doğrudan columnstore biçimine yükleme için gereken eşiğin altında değildir.

Bu gibi durumlarda verileri azure blob depolama alanına ilk olarak indirin ve yüklemeden önce biriktirin. Bu teknik genellikle mikro toplu işlem olarak bilinir.

Çok fazla bölüm var

Dikkat gereken bir diğer şey de bölümlemenin kümelenmiş columnstore tablolarınız üzerindeki etkisidir. Bölümlemeden önce ayrılmış SQL havuzu verilerinizi zaten 60 veritabanına böler. Bölümleme, verilerinizi daha da böler. Verilerinizi bölümlemeniz halinde, kümelenmiş columnstore dizininden yararlanmak için her bölümün en az 1 milyon satıra ihtiyacı olduğunu göz önünde bulundurabilirsiniz. Tabloyu 100 bölüme bölmeniz halinde kümelenmiş columnstore dizininden (60 dağıtım 100 bölüm 1 milyon satır) yararlanmak için en az 6 milyar satır gerekir. 100 bölümlemeli tablonda 6 milyar satır yoksa bölüm sayısını azaltabilirsiniz veya bunun yerine yığın tablosu kullanmayı göz önünde bulundurabilirsiniz.

Tablolarınız bazı verilerle yüklendiktan sonra, en uygun alt kümelenmiş columnstore dizinleri içeren tabloları tanımlamak ve yeniden oluşturmanız için aşağıdaki adımları izleyin.

Segment kalitesini geliştirmek için dizinleri yeniden oluşturma

1. Adım: Doğru kaynak sınıfını kullanan kullanıcı tanımlama veya oluşturma

Segment kalitesini hemen artırmanın hızlı bir yolu, dizini yeniden oluşturmadır. Yukarıdaki SQL tarafından döndürülen örnek, dizinlerinizi yeniden oluşturmada kullanılan bir ALTER INDEX REBUILD deyimi içerir. Dizinlerinizi yeniden oluşturma sırasında, dizininizi yeniden yapılandıran oturuma yeterli bellek ayıranın emin olun. Bunu yapmak için, bu tablodaki dizini yeniden oluşturma izni olan bir kullanıcının kaynak sınıfını önerilen en düşük değere kadar artırabilirsiniz.

Aşağıda, kaynak sınıfını artırarak kullanıcıya daha fazla bellek ayırmaya yönelik bir örnek verilmiştir. Kaynak sınıfları ile çalışmak için bkz. İş yükü yönetimi için kaynak sınıfları.

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

2. Adım: Daha yüksek kaynak sınıfı kullanıcısı ile kümelenmiş columnstore dizinlerini yeniden oluşturma

Daha yüksek bir kaynak sınıfı kullanan 1. adımdan () kullanıcı olarak oturum LoadUser açma ve ALTER INDEX deyimlerini yürütme. Bu kullanıcının dizinin yeniden oluşturulmuş olduğu tablolar üzerinde ALTER iznine sahip olduğundan emin olun. Bu örnekler tüm columnstore dizinini yeniden oluşturma veya tek bir bölümü yeniden oluşturma adımları gösterir. Büyük tablolarda aynı anda tek bir bölümün dizinlerini yeniden oluşturmak daha pratik bir uygulamadır.

Alternatif olarak, dizini yeniden derle yerine CTAS kullanarak tabloyu yeni bir tabloya kopyaabilirsiniz. En iyi yol hangisidir? Büyük hacimli veriler için CTAS genellikle ALTER INDEX'den daha hızlıdır. Daha küçük hacimli veriler için ALTER INDEX'in kullanımı daha kolaydır ve tabloyu değiştirmeniz gerekli değildir.

-- Rebuild the entire clustered index
ALTER INDEX ALL ON [dbo].[DimProduct] REBUILD;

-- Rebuild a single partition
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5;

-- Rebuild a single partition with archival compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Rebuild a single partition with columnstore compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE);

Ayrılmış bir havuza dizin SQL çevrimdışı bir işlemdir. Dizinleri yeniden oluşturma hakkında daha fazla bilgi için ColumnstoreDizinlerini Birleştirme ve ALTER INDEX 'de ALTER INDEX REBUILD bölümüne bakın.

3. Adım: Kümelenmiş columnstore segment kalitesinin artmış olduğunu doğrulayın

Düşük segment kalitesine sahip olan tabloyu tespit edilen sorguyu yeniden çalıştırarak segment kalitesinin arttlı olduğunu doğrulayın. Segment kalitesi gelişmezse tablodaki satırlar fazla geniş olabilir. Dizinlerinizi yeniden inşa etmek için daha yüksek bir kaynak sınıfı veya DWU kullanmayı göz önünde bulundurarak.

CTAS ve bölüm değiştirme ile dizinleri yeniden oluşturma

Bu örnekte, CREATE TABLE bölümü yeniden oluşturmak için AS SELECT (CTAS) deyimi ve bölüm değiştirme kullanılır.

-- Step 1: Select the partition of data and write it out to a new table using CTAS
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]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

-- Step 2: Switch IN the rebuilt data with TRUNCATE_TARGET option
ALTER TABLE [dbo].[FactInternetSales_20000101_20010101] SWITCH PARTITION 2 TO  [dbo].[FactInternetSales] PARTITION 2 WITH (TRUNCATE_TARGET = ON);

CTAS kullanarak bölümleri yeniden oluşturma hakkında daha fazla bilgi için bkz. Ayrılmış havuzlarda SQL kullanma.

Sonraki adımlar

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