Azure Synapse Analytics'te sıralı kümelenmiş columnstore diziniyle performans ayarlama

Şunlar için geçerlidir: Azure Synapse Analytics ayrılmış SQL havuzları

Kullanıcılar ayrılmış SQL havuzundaki bir columnstore tablosunu sorguladığında, iyileştirici her segmentte depolanan en düşük ve en yüksek değerleri denetler. Sorgu koşulunun sınırlarının dışında olan kesimler diskten belleğe okunmuyor. Okunacak kesim sayısı ve toplam boyutları küçükse sorgu daha hızlı tamamlanabilir.

Not

Bu makale, Azure Synapse Analytics ayrılmış SQL havuzları için geçerlidir. SQL Server ve diğer SQL platformlarındaki sıralı columnstore dizinleri hakkında bilgi için bkz . Sıralı kümelenmiş columnstore dizinleriyle performans ayarlama.

Sıralı ve sıralı olmayan kümelenmiş columnstore dizini karşılaştırması

Varsayılan olarak, dizin seçeneği olmadan oluşturulan her tablo için bir iç bileşen (dizin oluşturucusu) üzerinde sıralı olmayan bir kümelenmiş columnstore dizini (CCI) oluşturur. Her sütundaki veriler ayrı bir CCI satır grubu kesiminde sıkıştırılır. Her kesimin değer aralığında meta veriler vardır, bu nedenle sorgu koşulunun sınırlarının dışında olan kesimler sorgu yürütme sırasında diskten okunamaz. CCI, en yüksek veri sıkıştırma düzeyini sunar ve sorguların daha hızlı çalışabilmesi için okunacak kesimlerin boyutunu azaltır. Ancak dizin oluşturucu verileri segmentlere sıkıştırmadan önce sıralamadığından, çakışan değer aralıklarına sahip segmentler oluşabilir ve bu da sorguların diskten daha fazla kesim okumasına ve tamamlanmasının daha uzun sürmesine neden olabilir.

Sıralı kümelenmiş columnstore dizinleri, segmentlerin verimli bir şekilde ortadan kaldırılmasını etkinleştirerek, sorgu koşuluyla eşleşmeyen büyük miktarlardaki sıralı verileri atlayarak çok daha hızlı bir performans sağlar. Sıralı bir CCI oluştururken, ayrılmış SQL havuzu altyapısı, dizin oluşturucusu bunları dizin kesimlerine sıkıştırmadan önce bellekteki mevcut verileri sıralı anahtarlara göre sıralar. Sıralanmış verilerle, segment çakışması azaltılarak sorguların daha verimli bir segment eleme ve böylece diskten okunacak kesim sayısı daha az olduğundan daha hızlı performans elde edilmesi sağlanır. Tüm veriler aynı anda bellekte sıralanabilirse, segment çakışması önlenebilir. Veri ambarlarındaki büyük tablolar nedeniyle bu senaryo sık gerçekleşmez.

Bir sütunun segment aralıklarını denetlemek için tablonuzun adı ve sütun adıyla aşağıdaki komutu çalıştırın:

SELECT o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
pnp.pdw_node_id, pnp.distribution_id, cls.segment_id,
cls.column_id,
cls.min_data_id, cls.max_data_id,
cls.max_data_id-cls.min_data_id as difference
FROM sys.pdw_nodes_partitions AS pnp
   JOIN sys.pdw_nodes_tables AS Ntables ON pnp.object_id = NTables.object_id AND pnp.pdw_node_id = NTables.pdw_node_id
   JOIN sys.pdw_table_mappings AS Tmap  ON NTables.name = TMap.physical_name AND substring(TMap.physical_name,40, 10) = pnp.distribution_id
   JOIN sys.objects AS o ON TMap.object_id = o.object_id
   JOIN sys.pdw_nodes_column_store_segments AS cls ON pnp.partition_id = cls.partition_id AND pnp.distribution_id  = cls.distribution_id
JOIN sys.columns as cols ON o.object_id = cols.object_id AND cls.column_id = cols.column_id
WHERE o.name = '<Table Name>' and cols.name = '<Column Name>'  and TMap.physical_name  not like '%HdTable%'
ORDER BY o.name, pnp.distribution_id, cls.min_data_id;

Uyarı

Sıralı bir CCI tablosunda, aynı DML toplu işleminden veya veri yükleme işlemlerinden kaynaklanan yeni veriler bu toplu iş içinde sıralanır; tablodaki tüm verilerde genel sıralama yoktur. Kullanıcılar, tablodaki tüm verileri sıralamak için sıralı CCI'yi YENIDEN DERLEYEBILIR. Ayrılmış SQL havuzunda columnstore dizini REBUILD çevrimdışı bir işlemdir. Bölümlenmiş bir tablo için, REBUILD işlemi her seferinde bir bölüm yeniden oluşturularak yapılır. Yeniden derlenen bölümdeki veriler "çevrimdışı" olur ve bu bölüm için YENIDEN DERLE işlemi tamamlanana kadar kullanılamaz.

Sorgu performansı

Sorgunun sıralı bir CCI'den elde ettiği performans, sorgu desenlerine, verilerin boyutuna, verilerin ne kadar iyi sıralandığına, segmentlerin fiziksel yapısına ve sorgu yürütme için seçilen DWU ve kaynak sınıfına bağlıdır. Kullanıcılar, sıralı bir CCI tablosu tasarlarken sıralama sütunlarını seçmeden önce tüm bu faktörleri gözden geçirmelidir.

Tüm bu desenlere sahip sorgular genellikle sıralı CCI ile daha hızlı çalışır.

1. Sorguların eşitlik, eşitsizlik veya aralık koşulları vardır
2. Koşul sütunları ve sıralı CCI sütunları aynıdır.

Bu örnekte, T1 tablosunda Col_C, Col_B ve Col_A sırasıyla düzenlenmiş küme sıralı bir columnstore dizini vardır.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON  T1
ORDER (Col_C, Col_B, Col_A);

Tüm sıralı CCI sütunlarına başvuran sorgu 1 ve sorgu 2'nin performansı diğer sorgulardan daha fazla sıralı CCI'den yararlanabilir.

-- Query #1:

SELECT * FROM T1 WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- Query #2

SELECT * FROM T1 WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- Query #3
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_A = 'a';

-- Query #4
SELECT * FROM T1 WHERE Col_A = 'a' AND Col_C = 'c';

Veri yükleme performansı

Sıralı bir CCI tablosuna veri yükleme performansı, bölümlenmiş tabloya benzer. Verileri sıralı bir CCI tablosuna yüklemek, veri sıralama işlemi nedeniyle sıralı olmayan bir CCI tablosundan daha uzun sürebilir, ancak sorgular daha sonra sıralı CCI ile daha hızlı çalıştırılabilir.

Verileri farklı şemalara sahip tablolara yüklemenin performans karşılaştırması aşağıda verilmiştir.

Aşağıda CCI ile sıralı CCI arasındaki sorgu performansı karşılaştırması örneği verilmiştir.

Segment çakışmalarını azaltma

Çakışan kesimlerin sayısı sıralanacak verilerin boyutuna, kullanılabilir belleğe ve sıralı CCI oluşturma sırasında en yüksek paralellik derecesi (MAXDOP) ayarına bağlıdır. Aşağıdaki stratejiler, sıralı CCI oluşturulurken segment çakışmasını azaltır.

• Dizin oluşturucu verileri segmentlere sıkıştırmadan önce veri sıralama için daha fazla bellek sağlamak üzere daha yüksek bir DWU'da kaynak sınıfını kullanın xlargerc . Dizin kesimine girdikten sonra verilerin fiziksel konumu değiştirilemez. Bir segment içinde veya segmentler arasında veri sıralama yoktur.

• OPTION (MAXDOP = 1) ile sıralı CCI oluşturun. Sıralı CCI oluşturma için kullanılan her iş parçacığı bir veri alt kümesi üzerinde çalışır ve yerel olarak sıralar. Farklı iş parçacıklarına göre sıralanmış veriler arasında genel sıralama yoktur. Paralel iş parçacıklarının kullanılması, sıralı bir CCI oluşturma süresini kısaltabilir, ancak tek bir iş parçacığından daha fazla çakışan kesim oluşturur. Tek bir iş parçacıklı işlem kullanmak en yüksek sıkıştırma kalitesini sağlar. Örneğin:

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

Not

Şu anda Azure Synapse Analytics'teki ayrılmış SQL havuzlarında MAXDOP seçeneği yalnızca komutu kullanarak CREATE TABLE AS SELECT sıralı bir CCI tablosu oluşturmada desteklenmektedir. CREATE INDEX veya CREATE TABLE komutları aracılığıyla sıralı bir CCI oluşturmak, MAXDOP seçeneğini desteklemez. Bu sınırlama, SQL Server 2022 ve sonraki sürümlerde, CREATE INDEX veya CREATE TABLE komutlarıyla MAXDOP belirtebileceğiniz için geçerli değildir.

• Verileri tablolara yüklemeden önce sıralama anahtarlarına göre önceden sıralayın.

Yukarıdaki önerilere göre segmanların hiç çakışmadığı sıralı bir CCI tablo dağıtımı örneği aşağıda verilmiştir. Sıralı CCI tablosu, MAXDOP 1 ve kullanılarak 20 GB yığın tablosundan CTASxlargercbir DWU1000c veritabanında oluşturulur. CCI, yineleme içermeyen bir BIGINT sütununda sıralanır.

Büyük tablolarda sıralı CCI oluşturma

Sıralı CCI oluşturmak çevrimdışı bir işlemdir. Bölüm içermeyen tablolarda, sıralı CCI oluşturma işlemi tamamlanana kadar verilere kullanıcılar erişemez. Bölümlenmiş tablolar için, altyapı bölüme göre sıralı CCI bölümünü oluşturduğundan, kullanıcılar sıralı CCI oluşturma işleminin devam etmediği bölümlerdeki verilere erişmeye devam edebilir. Büyük tablolarda sıralı CCI oluşturma sırasında kesinti süresini en aza indirmek için bu seçeneği kullanabilirsiniz.

1. Hedef büyük tabloda Table_A (olarak adlandırılır) bölümler oluşturun.
2. Table_B olarak adlandırılan, Table_A ile aynı tablo ve bölüm şemasına sahip boş bir sıralı CCI tablosu oluşturun.
3. Bir bölümü Table_A'den Table_B'e değiştirin.
4. ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> komutunu çalıştırarak Table_B üzerindeki anahtarlanmış bölümü yeniden derleyin.
5. Table_A içindeki her bölüm için 3. ve 4. adımları yineleyin.
6. Tüm bölümler c0'dan c1'e değiştirildikten ve yeniden oluşturulduktan sonra, c2'yi bırakın ve c3'ü c4 olarak yeniden adlandırın.

İpucu

Sıralı CCI içeren ayrılmış bir SQL havuzu tablosu için ALTER INDEX REBUILD kullanarak tempdbverileri yeniden sıralar. Yeniden oluşturma işlemleri sırasında tempdb'yi izleyin. Daha fazla tempdb alana ihtiyacınız varsa havuzun ölçeğini büyütün. Dizin yeniden oluşturma işlemi tamamlandıktan sonra ölçeği yeniden azaltma.

Sıralı CCI içeren ayrılmış bir SQL havuzu tablosu için ALTER INDEX REORGANIZE verileri yeniden sıralamaz. Verilere başvurmak için ALTER INDEX REBUILD kullanın.

Sıralı CCI bakımı hakkında daha fazla bilgi için Kümelenmiş columnstore dizinlerini iyileştirme bölümüne bakın.

SQL Server 2022 özelliklerindeki özellik farklılıkları

SQL Server 2022 (16.x), Azure Synapse ayrılmış SQL havuzlarındaki özelliğe benzer sıralı kümelenmiş columnstore dizinlerini kullanıma sunar.

• Şu anda, yalnızca SQL Server 2022 (16.x) ve sonraki sürümleri, dize, ikili ve guid veri türleri ile ölçeği ikiden büyük olan datetimeoffset veri türü için gelişmiş kümelenmiş columnstore segment eleme yeteneklerini desteklemektedir. Daha önce, bu segment eleme sayısal, tarih ve saat veri türlerine ve ikiden küçük veya buna eşit ölçekle datetimeoffset veri türüne uygulanır.
• Şu anda yalnızca SQL Server 2022 (16.x) ve sonraki sürümler, LIKE koşul öneki için kümelenmiş sütun deposu satır grubu elemeyi destekler, örneğin column LIKE 'string%'. LIKE ifadesinin ön ek kullanılmadan column LIKE '%string' şekilde kullanımı için segment kaldırma desteklenmez.

Daha fazla bilgi için bkz. Columnstore Dizinlerindeki Yenilikler.

Örnekler

A. Sıralı sütunları ve sıralı sırayı denetlemek için:

SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal
FROM sys.index_columns i
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0;

B. Sütun sırasını değiştirmek için, sıra listesinden sütun ekleyin veya kaldırın ya da CCI'den sıralı CCI'ye geçmek için:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

Sonraki adımlar

• Daha fazla geliştirme ipucu için bkz . geliştirmeye genel bakış.
• Columnstore dizinleri: Genel bakış
• Columnstore dizinlerindeki yenilikler
• Columnstore dizinleri - Tasarım Kılavuzu
• Columnstore dizinleri - Sorgu performansı