Gerçek zamanlı operasyonel analiz için columnstore dizinlerini kullanmaya başlama

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen ÖrneğiMicrosoft Fabric'te SQL veritabanı

SQL Server 2016 (13.x), gerçek zamanlı operasyonel analiz, hem analiz hem de OLTP iş yüklerini aynı veritabanı tablolarında aynı anda çalıştırma olanağı sunar. Analizi gerçek zamanlı çalıştırmanın yanı sıra ETL ve veri ambarı gereksinimini de ortadan kaldırabilirsiniz.

Gerçek zamanlı operasyonel analiz açıklandı

Geleneksel olarak işletmelerin operasyonel (OLTP) ve analiz iş yükleri için ayrı sistemleri vardır. Bu tür sistemler için Ayıklama, Dönüştürme ve Yükleme (ETL) işleri verileri işlem deposundan bir analiz deposuna düzenli olarak taşır. Analiz verileri genellikle analiz sorgularını çalıştırmaya ayrılmış bir veri ambarında veya veri reyonunda depolanır. Bu çözüm standart olsa da, şu üç önemli zorluk vardır:

• Complexity. ETL'nin uygulanması, özellikle yalnızca değiştirilen satırları yüklemek için önemli ölçüde kodlama gerektirebilir. Hangi satırların değiştirildiğini belirlemek karmaşık olabilir.
• Cost. ETL'nin uygulanması için ek donanım ve yazılım lisansları satın alma maliyeti gerekir.
• Veri Gecikme Süresi. ETL'nin uygulanması, analizi çalıştırmak için bir zaman gecikmesi ekler. Örneğin, ETL işi her iş gününün sonunda çalıştırılırsa analiz sorguları en az bir günlük veriler üzerinde çalıştırılır. Birçok işletme için bu gecikme kabul edilemez çünkü işletme verileri gerçek zamanlı olarak çözümlemeye bağımlıdır. Örneğin, sahtekarlık algılama, operasyonel veriler üzerinde gerçek zamanlı analiz gerektirir.

Gerçek zamanlı operasyonel analiz, bu zorluklara bir çözüm sunar.

Analiz ve OLTP iş yükleri aynı temel alınan tabloda çalıştırıldığında zaman gecikmesi olmaz. Gerçek zamanlı analiz kullanabilen senaryolarda, ETL gereksinimi ve ayrı bir veri ambarı satın alma ve bakım gereksinimi ortadan kaldırılarak maliyetler ve karmaşıklık büyük ölçüde azalır.

Note

Gerçek zamanlı operasyonel analiz, hem operasyonel hem de analiz iş yükünü çalıştırabileceğiniz kurumsal kaynak planlama (ERP) uygulaması gibi tek bir veri kaynağının senaryosunu hedefler. Bu, analiz iş yükünü çalıştırmadan önce birden çok kaynaktan verileri tümleştirmeniz gerektiğinde veya küpler gibi önceden toplanmış verileri kullanarak aşırı analiz performansına ihtiyaç duyduğunuzda ayrı bir veri ambarı gereksiniminin yerini almaz.

Gerçek zamanlı analiz, bir satır deposu tablosunda güncelleştirilebilir bir kümelenmemiş columnstore dizini kullanır. columnstore dizini verilerin bir kopyasını tutar, bu nedenle OLTP ve analiz iş yükleri verilerin ayrı kopyalarında çalışır. Bu, aynı anda çalışan her iki iş yükünün de performans etkisini en aza indirir. Veritabanı Altyapısı dizin değişikliklerini otomatik olarak korur, böylece OLTP değişiklikleri analiz için her zaman up-totarih olur. Bu tasarımla, up-totarih verilerinde gerçek zamanlı analiz çalıştırmak mümkün ve pratiktir. Bu, hem disk tabanlı hem de bellek için iyileştirilmiş tablolar için çalışır.

Kullanmaya başlama örneği

Gerçek zamanlı analize başlamak için:

1. İşlem şemanızda analiz için gereken verileri içeren tabloları tanımlayın.

2. Her tablo için öncelikle OLTP iş yükünüzdeki mevcut analizleri hızlandırmak için tasarlanmış tüm B ağacı dizinlerini bırakın. Bunları tek bir kümelenmemiş columnstore diziniyle değiştirin. OLTP iş yükünüzün genel performansını, bakım gerektiren dizin sayısının daha az olması nedeniyle artırabilir.

   --This example creates a nonclustered columnstore index on an existing OLTP table.
   --Create the table
   CREATE TABLE t_account (
       accountkey int PRIMARY KEY,
       accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int
   );
   
   --Create the columnstore index with a filtered condition
   CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI
   ON t_account (accountkey, accountdescription, unitsold)
   ;
   

   Bellek için iyileştirilmiş bir tablodaki columnstore dizini, hem OLTP hem de analiz iş yükleri için yüksek performans sağlamak üzere Bellek içi OLTP ve columnstore teknolojilerini tümleştirerek operasyonel analize olanak tanır. Bellek ile iyileştirilmiş bir tablodaki columnstore dizini, başka bir deyişle tüm sütunları içerecek şekilde, kümelenmiş dizin olmalıdır.

   -- This example creates a memory-optimized table with a columnstore index.
   CREATE TABLE t_account (
       accountkey int NOT NULL PRIMARY KEY NONCLUSTERED,
       Accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int,
       INDEX t_account_cci CLUSTERED COLUMNSTORE
       )
       WITH (MEMORY_OPTIMIZED = ON );
   

Artık uygulamanızda herhangi bir değişiklik yapmadan gerçek zamanlı operasyonel analiz çalıştırmaya hazırsınız. Analiz sorguları columnstore dizininde çalıştırılır ve OLTP işlemleri OLTP B ağacı dizinlerinizde çalışmaya devam eder. OLTP iş yükleri çalışmaya devam eder, ancak columnstore dizinini korumak için bazı ek yüklere neden olur. Sonraki bölümdeki performans iyileştirmelerine bakın.

Blog gönderileri

Gerçek zamanlı operasyonel analiz hakkında daha fazla bilgi edinmek için aşağıdaki blog gönderilerini okuyun. Önce blog gönderilerine bakarsanız performans ipuçları bölümlerini anlamak daha kolay olabilir.

• Gerçek zamanlı operasyonel analiz için iş örneği

• Gerçek zamanlı operasyonel analiz için kümelenmemiş columnstore dizini kullanma

• Kümelenmemiş columnstore dizini kullanan basit bir örnek

• SQL Server, işlem iş yükünde bir kümelenmemiş columnstore dizinini nasıl korur?

• Filtrelenmiş dizin kullanarak kümelenmemiş columnstore dizini bakımının etkisini en aza indirmek

• Sıkıştırma gecikmesini kullanarak kümelenmemiş columnstore dizin bakımının etkisini en aza indirme

• Sıkıştırma gecikmesini kullanarak kümelenmemiş columnstore dizini bakımının etkisini en aza indirme - performans sonuçları

• Bellek optimizasyonlu tablolarla gerçek zamanlı operasyonel analiz

• Columnstore dizini ve satır grupları için birleştirme ilkesi

Videos

The Data Exposed video serisi, bazı yetenekler ve dikkat edilmesi gereken hususlarda daha ayrıntılı inceliyor.

• 1. Bölüm: Azure SQL Gerçek Zamanlı Operasyonel Analizi (HTAP) Nasıl Etkinleştirir?
• 2. Bölüm: Operasyonel analiz ile mevcut veritabanlarını ve uygulamaları iyileştirme
• Bölüm 3: Window İşlevleri ile operasyonel analiz oluşturma.

Performans ipucu 1: Sorgu performansını geliştirmek için filtrelenmiş dizinleri kullanma

Gerçek zamanlı operasyonel analiz çalıştırmak OLTP iş yükünün performansını etkileyebilir. Bu etki en düşük düzeyde olmalıdır. Örnek A , analiz gerçek zamanlı olarak sunmaya devam ederken, kümelenmemiş columnstore dizininin işlem iş yükü üzerindeki etkisini en aza indirmek için filtrelenmiş dizinlerin nasıl kullanılacağını gösterir.

İşlemsel bir iş yükünde kümelenmemiş columnstore dizinini koruma yükünü en aza indirmek için filtrelenmiş bir koşul kullanarak yalnızca sıcak veya yavaş değişen verilerde kümelenmemiş bir columnstore dizini oluşturabilirsiniz. Örneğin, bir sipariş yönetimi uygulamasında, sevk edilmiş siparişler için kümelenmemiş bir columnstore index oluşturabilirsiniz. Sipariş gönderildikten sonra nadiren değişir ve bu nedenle sıcak veriler olarak kabul edilebilir. Filtrelenmiş bir dizinde, kümelenmemiş columnstore dizinindeki veriler daha az güncelleştirme gerektirir ve bu da işlem iş yükü üzerindeki etkiyi düşürür.

Analiz sorguları, gerçek zamanlı analiz sağlamak için gerektiğinde hem sıcak hem de ılık verilere şeffaf bir şekilde erişir. İşletimsel iş yükünün önemli bir bölümü 'sık erişimli' verilere dokunuyorsa, bu işlemler columnstore dizininde ek bakım gerektirmez. En iyi uygulama, filtrelenmiş dizin tanımında kullanılan sütunlarda bir satır deposu kümelenmiş dizin bulundurmaktır. Veritabanı Altyapısı, filtrelenmiş koşulu karşılamayan satırları hızla taramak için kümelenmiş dizini kullanır. Bu kümelenmiş dizin olmadan, bu satırları bulmak için rowstore tablosunun tam tablo taraması gerekir ve bu da analiz sorgularının performansını olumsuz etkileyebilir. Kümelenmiş dizin olmadığında, bu tür satırları tanımlamak için tamamlayıcı bir filtrelenmemiş B ağacı dizini oluşturabilirsiniz, ancak kümelenmemiş B ağacı dizinleri aracılığıyla çok sayıda satıra erişmek pahalı olduğundan bu önerilmez.

Note

Filtrelenmiş bir kümelenmemiş columnstore dizini yalnızca disk tabanlı tablolarda desteklenir. Bellek için iyileştirilmiş tablolarda desteklenmez.

Örnek A: B ağacı dizininden sıcak verilere, columnstore dizininden ılık verilere erişme

Bu örnek, columnstore dizinine hangi satırların dahil olduğunu oluşturmak için filtrelenmiş bir koşul (accountkey > 0) kullanır. Amaç, sık sık değişen "sıcak" verilere B+ ağacı dizininden erişmek ve daha kararlı "ılık" verilere sütun deposu dizininden erişmek için filtrelenmiş koşulu ve ilgili sorguları tasarlamaktır.

Note

Sorgu İyileştiricisi sorgu planı için columnstore dizinini dikkate alır ancak her zaman seçmez. Sorgu iyileştiricisi filtrelenmiş columnstore dizinini seçtiğinde, gerçek zamanlı analize izin vermek için hem columnstore dizinindeki satırları hem de filtrelenmiş koşulu karşılamayan satırları saydam bir şekilde birleştirir. Bu, yalnızca kendilerini dizinde bulunan satırlara kısıtlayan sorgularda kullanılabilen, düzenli bir kümelenmemiş filtrelenmiş dizinden farklıdır.

-- Use a filtered condition to separate hot data in a rowstore table
-- from "warm" data in a columnstore index.

-- create the table
CREATE TABLE orders (
AccountKey int not null,
CustomerName nvarchar (50),
OrderNumber bigint,
PurchasePrice decimal (9,2),
OrderStatus smallint not null,
OrderStatusDesc nvarchar (50)
);

-- OrderStatusDesc  
-- 0 => 'Order Started'  
-- 1 => 'Order Closed'  
-- 2 => 'Order Paid'  
-- 3 => 'Order Fulfillment Wait'  
-- 4 => 'Order Shipped'  
-- 5 => 'Order Received'  

CREATE CLUSTERED INDEX orders_ci ON orders(OrderStatus);

--Create the columnstore index with a filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders  (accountkey, customername, purchaseprice, orderstatus)
WHERE OrderStatus = 5;

-- The following query returns the total purchase done by customers for items > $100 .00
-- This query will pick  rows both from NCCI and from 'hot' rows that are not part of NCCI
SELECT TOP (5) CustomerName, SUM(PurchasePrice)
FROM orders
WHERE PurchasePrice > 100.0
GROUP BY CustomerName;

Analiz sorgusu aşağıdaki sorgu planıyla yürütülür. Filtrelenmiş koşulu karşılamayan satırlara kümelenmiş B ağacı dizini aracılığıyla erişildiğini görebilirsiniz.

Daha fazla bilgi için bkz . Blog: Filtrelenmiş, kümelenmemiş columnstore dizini.

Performans ipucu 2: Analizi Always On okunabilir ikincil öğesine boşaltın

Filtrelenmiş bir columnstore dizini kullanarak columnstore dizini bakımını en aza indirebilmenize rağmen analiz sorguları, işlemsel iş yükü performansını etkileyen önemli bilgi işlem kaynakları (CPU, G/Ç, bellek) gerektirebilir. Görev açısından kritik iş yüklerinin çoğunda Always On yapılandırmasını kullanmanız önerilir. Bu yapılandırmada, analiz çalıştırmanın etkisini ortadan kaldırmak için bunu okunabilir bir ikincil değere boşaltabilirsiniz.

Performans ipucu #3: Delta satır gruplarında sık erişimli verileri tutarak dizin parçalanma oranını azaltma

İş yükü sıkıştırılmış satırları günceller veya silerse, columnstore dizinli tablolar önemli ölçüde parçalanabilir (yani, satırlar silinir). Parçalanmış columnstore dizini, belleğin/depolamanın verimsiz kullanımına yol açar. Kaynakların verimsiz kullanımına ek olarak, ek G/Ç ve sonuç kümesinden silinen satırları filtreleme gereksinimi nedeniyle analiz sorgusu performansını olumsuz etkiler.

Silinen satırlar, komutla REORGANIZE dizin birleştirmeyi çalıştırana veya tablonun tamamında veya etkilenen bölümlerde columnstore dizinini yeniden derleyene kadar fiziksel olarak kaldırılmaz. Hem dizin REORGANIZE hem REBUILD de pahalı işlemler kaynakları alır ve bu da iş yükü için kullanılabilecek bir işlemdir. Ayrıca, satırlar çok erken sıkıştırıldıysa, sıkıştırma ek yükünün boşa gitmesine neden olan güncelleştirmeler nedeniyle birden çok kez yeniden sıkıştırılması gerekebilir.

Seçeneği kullanarak COMPRESSION_DELAY dizin parçalanmayı en aza indirebilirsiniz.

-- Create a sample table
CREATE TABLE t_colstor (
accountkey int not null,
accountdescription nvarchar (50) not null,
accounttype nvarchar(50),
accountCodeAlternatekey int
);

-- Creating nonclustered columnstore index with COMPRESSION_DELAY. 
-- The columnstore index will keep the rows in closed delta rowgroup 
-- for 100 minutes after it has been marked closed.
CREATE NONCLUSTERED COLUMNSTORE INDEX t_colstor_cci ON t_colstor
(accountkey, accountdescription, accounttype)
WITH (DATA_COMPRESSION = COLUMNSTORE, COMPRESSION_DELAY = 100);

Daha fazla bilgi için bkz . Blog: Sıkıştırma gecikmesi.

Önerilen en iyi yöntemler şunlardır:

• Ekle/Sorgu iş yükü: İş yükünüz öncelikli olarak veri ekleyip sorgulanıyorsa önerilen seçenek varsayılan COMPRESSION_DELAY 0'dır. Yeni eklenen satırlar, tek bir delta satır grubuna 1 milyon satır eklendikten sonra sıkıştırılır. Bu tür iş yüklerine bazı örnekler, bir web uygulamasındaki seçim desenini analiz etmeniz gerektiğinde geleneksel bir DW iş yükü veya bir seçim akışı analizidir.

• OLTP iş yükü: İş yükü DML ağır ise (yani, Update, Delete ve Insert'in yoğun karışımı), DMV'yi sys.dm_db_column_store_row_group_physical_statsinceleyerek columnstore dizini parçalanmasını görebilirsiniz. Son sıkıştırılan satır gruplarında > 10% satırın silinmiş olarak işaretlendiğini görürseniz, satırlar sıkıştırmaya uygun olduğunda zaman gecikmesi eklemek için COMPRESSION_DELAY seçeneğini kullanabilirsiniz. Örneğin, iş yükünüz için yeni eklenen, diyelim ki 60 dakika boyunca 'aktif' kalıyorsa (yani, birden çok kez güncelleniyorsa), COMPRESSION_DELAY değerini 60 olarak seçmelisiniz.

Seçeneğin COMPRESSION_DELAY varsayılan değeri çoğu müşteri için işe yaramalıdır.

İleri düzey kullanıcılar için aşağıdaki sorguyu çalıştırmanızı ve son yedi gün içindeki silinmiş satırların % toplamanızı öneririz.

SELECT row_group_id,
       CAST(deleted_rows AS float)/CAST(total_rows AS float)*100 AS [% fragmented],
       created_time
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('FactOnlineSales2')
      AND state_desc = 'COMPRESSED'
      AND deleted_rows > 0
      AND created_time > DATEADD(day, -7, GETDATE())
ORDER BY created_time DESC;

Sıkıştırılmış satır gruplarındaki > silinen satırların sayısı 20%'dan az ise ve eski satır grupları, soğuk satır grupları olarak adlandırılan < 5% varyasyonu ile platolaşıyorsa, COMPRESSION_DELAY değerini (youngest_rowgroup_created_time - current_time) olarak ayarlayın. Bu yaklaşım, kararlı ve nispeten homojen bir iş yüküyle en iyi şekilde çalışır.

İlgili içerik

• Columnstore dizinleri: Genel bakış
• Columnstore dizinleri - Veri yükleme kılavuzu
• Columnstore dizinleri - Sorgu performansı
• Veri ambarı oluşturmada columnstore dizinleri
• Sorgu performansını geliştirmek ve kaynak tüketimini azaltmak için dizin bakımını iyileştirme