Ayrılmış havuzdaki columnstore dizinleri için rowgroup kalitesini SQL geliştirme

Satır grubu kalitesi, bir satır grubu içinde satır sayısına göre belirlenir. Kullanılabilir belleği artırmak, columnstore dizininin her satır grubu içine sıkıştıran satır sayısını en üst düzeye çıkarabilirsiniz. Columnstore dizinleri için sıkıştırma oranlarını ve sorgu performansını geliştirmek üzere bu yöntemleri kullanın.

Rowgroup boyutunun önemi

Columnstore dizini tek tek satır gruplarının sütun kesimlerini taraarak tabloyu tarar ve her satır grubu içinde satır sayısını en üst düzeye çıkarmak sorgu performansını artırır.

Satır grupları çok sayıda satıra sahip olduğunda veri sıkıştırması, diskten okunan verinin daha az olduğu anlamına gelir.

Satır grupları hakkında daha fazla bilgi için bkz. Columnstore Dizinleri Kılavuzu.

Satır grupları için hedef boyut

En iyi sorgu performansı için amaç, columnstore dizininde satır grubu başına satır sayısını en üst düzeye çıkarmaktır. Bir satır grubu en fazla 1.048.576 satıra sahip olabilir.

Satır grubu başına en fazla satır sayısına sahip olmak sorun değildir. Columnstore dizinleri, satır gruplarının en az 100.000 satırı olduğunda iyi performans gösterir.

Satır grupları sıkıştırma sırasında kırpılır

Toplu yükleme veya columnstore dizini yeniden oluşturma sırasında bazen her satır grubu için belirlenen tüm satırları sıkıştırmak için yeterli bellek yok olabilir. Bellek baskısı mevcut olduğunda columnstore dizinleri rowgroup boyutlarını kırparak columnstore'da sıkıştırmanın başarılı olmasını sağlar.

Her satır grubu için en az 10.000 satırı sıkıştırmak için yetersiz bellek olduğunda bir hata oluşturulur.

Toplu yükleme hakkında daha fazla bilgi için bkz. Kümelenmiş columnstore dizinine toplu yükleme.

Rowgroup kalitesini izleme

DMV sys.dm_pdw_nodes_db_column_store_row_group_physical_stats (sys.dm_db_column_store_row_group_physical_stats, satır gruplarında satır sayısı ve kırpma varsa kırpma nedeni gibi yararlı bilgileri ortaya çıkaran görünüm tanımını SQL DB ile eşleşen görünüm tanımını içerir.

Satır grubu kırpma hakkında bilgi almak için bu DMV'i sorgulamanın kullanışlı bir yolu olarak aşağıdaki görünümü oluşturabilirsiniz.

create view dbo.vCS_rg_physical_stats
as
with cte
as
(
select   tb.[name]                    AS [logical_table_name]
,        rg.[row_group_id]            AS [row_group_id]
,        rg.[state]                   AS [state]
,        rg.[state_desc]              AS [state_desc]
,        rg.[total_rows]              AS [total_rows]
,        rg.[trim_reason_desc]        AS trim_reason_desc
,        mp.[physical_name]           AS physical_name
FROM    sys.[schemas] sm
JOIN    sys.[tables] tb               ON  sm.[schema_id]          = tb.[schema_id]
JOIN    sys.[pdw_table_mappings] mp   ON  tb.[object_id]          = mp.[object_id]
JOIN    sys.[pdw_nodes_tables] nt     ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg      ON  rg.[object_id]     = nt.[object_id]
                                                                            AND rg.[pdw_node_id]   = nt.[pdw_node_id]
                                        AND rg.[distribution_id]    = nt.[distribution_id]
)
select *
from cte;

Bu trim_reason_desc, rowgroup'ların kırpıp kırpıla trim_reason_desc = NO_TRIM kırpma olmadığını ve satır grubunun en uygun kaliteye sahip olduğunu ifade eder. Aşağıdaki kırpma nedenleri satır grubu için erken kırpmayı gösteriyor:

  • BULKLOAD: Bu kırpma nedeni, yük için gelen satır toplu işi 1 milyon satırdan az olduğunda kullanılır. Eklenen 100.000'den fazla satır varsa (delta deposuna eklemenin aksine) altyapı sıkıştırılmış satır grupları oluştursa da kırpma nedenini BULKLOAD olarak ayarlar. Bu senaryoda, toplu iş yüklerinizi daha fazla satır içerecek şekilde artırmayı göz önünde bulundurarak. Ayrıca bölümleme düzeninizi yeniden değerlendirerek satır gruplarının bölüm sınırlarına yayılamaması için çok ayrıntılı bir düzen oluşturmaması gerekir.
  • MEMORY_LIMITATION: 1 milyon satıra sahip satır grupları oluşturmak için altyapı için belirli miktarda çalışma belleği gerekir. Yükleme oturumunun kullanılabilir belleği gerekli çalışma belleğinden düşük olduğunda satır grupları erken kırpıldı. Aşağıdaki bölümlerde, gerekli belleği tahmin etmek ve daha fazla bellek ayırmak açıklanmaktadır.
  • DICTIONARY_SIZE: Bu kırpma nedeni, geniş ve/veya yüksek kardinaliteye sahip en az bir dize sütunu olduğundan satır grubu kırpmanın meydana geldiğine işaret ediyor. Sözlük boyutu bellekte 16 MB ile sınırlıdır ve bu sınıra ulaşıldıktan sonra satır grubu sıkıştırılır. Bu durumla karşısanız sorunlu sütunu ayrı bir tabloda yalıtabilirsiniz.

Bellek gereksinimlerini tahmin etmek için

Maksimum boyuta sahip bir satır grubunu columnstore dizinine sıkıştırmak için bellek gereksinimlerine ilişkin bir tahmin görüntülemek için, bu dizinde örnek görünüm oluşturmayı dbo.vCS_mon_mem_grant. Bu sorgu, columnstore'da bir rowgroup'a sıkıştırma için gereken bellek izninin boyutunu gösterir.

Bir satır grubunu sıkıştırmak için gereken maksimum bellek yaklaşık olarak

  • 72 MB +
  • #rows * #columns * 8 bayt +
  • #rows * #short-string-columns * 32 bayt +
  • #long-string-columns * 16 MB for compression dictionary

Not

Kısa dize sütunları= 32 baytlık dize veri türlerini, uzun dize sütunları ise 32 baytlık < dize veri türlerini > kullanır.

Uzun dizeler, metin sıkıştırmak için tasarlanmış bir sıkıştırma yöntemiyle sıkıştırılır. Bu sıkıştırma yöntemi, metin desenlerini depolamak için bir sözlük kullanır. Bir sözlüğün en büyük boyutu 16 MB'tır. Rowgroup'ta her uzun dize sütunu için yalnızca bir sözlük var.

Bellek gereksinimlerini azaltmanın yolları

Satır gruplarını columnstore dizinlerine sıkıştırmak için bellek gereksinimlerini azaltmak için aşağıdaki teknikleri kullanın.

Daha az sütun kullanma

Mümkünse tabloyu daha az sütunla tasarlayabilirsiniz. Columnstore'da bir satır grubu sıkıştırılırsa columnstore dizini her sütun kesimini ayrı ayrı sıkıştırır.

Bu nedenle, sütun sayısı arttıkça rowgroup sıkıştırmak için bellek gereksinimleri artar.

Daha az dize sütunu kullanma

Dize veri türlerinin sütunları sayısal ve tarih veri türlerinden daha fazla bellek gerektirir. Bellek gereksinimlerini azaltmak için, bilgi tablolarından dize sütunlarını kaldırmayı ve bunları daha küçük boyut tablolarına koymayı göz önünde bulundurabilirsiniz.

Dize sıkıştırma için ek bellek gereksinimleri:

  • 32 karaktere kadar olan dize veri türleri, değer başına 32 ek bayt gerektirir.
  • 32 karakterden fazla karakter içeren dize veri türleri, sözlük yöntemleri kullanılarak sıkıştırılır. Rowgroup'daki her sütunun sözlüğü derlemesi için 16 MB'a kadar daha fazla işlem gerekir.

Aşırı bölümlemeden kaçının

Columnstore dizinleri bölüm başına bir veya daha fazla satır grubu oluşturur. Bu SQL ayrılmış Azure Synapse Analytics, veriler dağıtıldığından ve her dağıtım bölümlenmiş olduğundan bölüm sayısı hızla artar.

Tabloda çok fazla bölüm varsa satır gruplarını doldurmak için yeterli satırlar yok olabilir. Satırların olmaması sıkıştırma sırasında bellek baskısı oluşturmaz. Ancak, en iyi columnstore sorgu performansına ulaşmaz satır gruplarına yol gösterir.

Aşırı bölümlemeden kaçınmanın bir diğer nedeni de bölümlenmiş tablodaki bir columnstore dizinine satır yüklemenin bellek yükü olduğudur.

Yükleme sırasında birçok bölüm gelen satırları alır ve bu satırlar, her bölümün sıkıştırılabilir yeterli satırına sahip olana kadar bellekte tutularak gerçekleştirildi. Çok fazla bölüm olması ek bellek baskısı oluşturur.

Yük sorgusunu basitleştirme

Veritabanı, sorgunun bellek iznini sorgunun tüm işleçleri arasında paylaşıyor. Bir yük sorgusu karmaşık sıralamalara ve birleştirmelere sahip olduğunda, sıkıştırma için kullanılabilir bellek azalır.

Yük sorgusunu yalnızca sorguyu yüklemeye odaklanan şekilde tasarlar. Veriler üzerinde dönüştürmeler çalıştırmaya ihtiyacınız varsa bunları yük sorgusundan ayrı olarak çalıştırın. Örneğin, verileri bir yığın tablosuna hazırlar, dönüştürmeleri çalıştırın ve hazırlama tabloyu columnstore dizinine yükleme.

İpucu

Ayrıca önce verileri yükp ardından MPP sistemini kullanarak verileri dönüştürebilirsiniz.

MAXDOP'ı ayarlama

Dağıtım başına birden fazla CPU çekirdeği olduğunda her dağıtım rowgroups'ı columnstore'da paralel olarak sıkıştırır.

Paralellik için ek bellek kaynakları gerekir ve bu da bellek baskısı ve satır grubu kırpmaya yol açabilirsiniz.

Bellek baskısını azaltmak için MAXDOP sorgu ipucunu kullanarak yükleme işlemi her dağıtımda seri modda çalışır hale getirebilirsiniz.

CREATE TABLE MyFactSalesQuota
WITH (DISTRIBUTION = ROUND_ROBIN)
AS SELECT * FROM FactSalesQuota
OPTION (MAXDOP 1);

Daha fazla bellek ayırmanın yolları

DWU boyutu ve kullanıcı kaynak sınıfı, bir kullanıcı sorgusu için ne kadar bellek olduğunu belirler.

Bir yük sorgusu için bellek iznini artırmak için DWUS sayısını veya kaynak sınıfını artırabilirsiniz.

Sonraki adımlar

Ayrılmış havuz için performansı geliştirmenin daha fazla SQL için bkz. Performansa genel bakış.