Mulailah dengan indeks penyimpanan kolom untuk analisis operasional waktu nyata.

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

SQL Server 2016 (13.x) memperkenalkan analitik operasional real time, kemampuan untuk menjalankan beban kerja analitik dan OLTP pada tabel database yang sama secara bersamaan. Selain menjalankan analitik secara real time, Anda juga dapat menghilangkan kebutuhan akan ETL dan gudang data.

Penjelasan analitik operasional dalam waktu nyata

Secara tradisional, bisnis telah memiliki sistem terpisah untuk beban kerja operasional (yaitu, OLTP) dan analitik. Untuk pekerjaan sistem tersebut, Ekstrak, Transformasi, dan Muat (ETL) secara teratur memindahkan data dari penyimpanan operasional ke penyimpanan analitik. Data analitik biasanya disimpan di gudang data atau data mart yang didedikasikan untuk menjalankan kueri analitik. Meskipun solusi ini telah menjadi standar, solusi ini memiliki tiga tantangan utama ini:

• Complexity. Menerapkan ETL dapat memerlukan pengkodian yang cukup besar terutama untuk memuat hanya baris yang dimodifikasi. Ini bisa kompleks untuk mengidentifikasi baris mana yang telah dimodifikasi.
• Cost. Menerapkan ETL memerlukan biaya pembelian lisensi perangkat keras dan perangkat lunak tambahan.
• Latensi Data. Menerapkan ETL menambahkan penundaan waktu untuk menjalankan analitik. Misalnya, jika pekerjaan ETL berjalan di akhir setiap hari kerja, kueri analitik akan berjalan pada data yang setidaknya berusia sehari. Bagi banyak bisnis penundaan ini tidak dapat diterima karena bisnis tergantung pada analisis data secara real time. Misalnya, deteksi penipuan memerlukan analitik real time pada data operasional.

Analitik operasional real time menawarkan solusi untuk tantangan ini.

Tidak ada penundaan waktu ketika analitik dan beban kerja OLTP berjalan pada tabel yang mendasari yang sama. Untuk skenario yang dapat menggunakan analitik real time, biaya dan kompleksitas sangat berkurang dengan menghilangkan kebutuhan akan ETL dan kebutuhan untuk membeli dan memelihara gudang data terpisah.

Note

Analitik operasional real time menargetkan skenario sumber data tunggal seperti aplikasi perencanaan sumber daya perusahaan (ERP) tempat Anda dapat menjalankan beban kerja operasional dan analitik. Ini tidak menggantikan kebutuhan akan gudang data terpisah saat Anda perlu mengintegrasikan data dari beberapa sumber sebelum menjalankan beban kerja analitik atau ketika Anda memerlukan performa analitik ekstrem menggunakan data pra-agregat seperti kubus.

Analitik real time menggunakan indeks penyimpan kolom nonclustered yang dapat diperbarui pada tabel rowstore. Indeks penyimpan kolom mempertahankan salinan data, sehingga beban kerja OLTP dan analitik berjalan terhadap salinan data terpisah. Ini meminimalkan dampak performa kedua beban kerja yang berjalan secara bersamaan. Mesin Database secara otomatis mempertahankan perubahan indeks sehingga perubahan OLTP selalu up-to-tanggal untuk analitik. Dengan desain ini, dimungkinkan dan praktis untuk menjalankan analitik secara real time pada data terbaru. Ini berfungsi untuk tabel berbasis disk dan memori yang dioptimalkan.

Contoh untuk memulai

Untuk mulai menggunakan analitik real-time:

1. Identifikasi tabel dalam skema operasional Anda yang berisi data yang diperlukan untuk analitik.

2. Untuk setiap tabel, hilangkan semua indeks pohon B yang terutama dirancang untuk mempercepat analitik yang ada pada beban kerja OLTP Anda. Ganti dengan satu indeks penyimpan kolom yang tidak terkluster. Ini dapat meningkatkan performa keseluruhan beban kerja OLTP Anda karena ada lebih sedikit indeks yang perlu dipertahankan.

   --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)
   ;
   

   Indeks penyimpan kolom pada tabel yang dioptimalkan memori memungkinkan analitik operasional dengan mengintegrasikan teknologi OLTP dalam memori dan penyimpan kolom untuk memberikan performa tinggi untuk beban kerja OLTP dan analitik. Indeks penyimpan kolom pada tabel yang dioptimalkan memori harus berupa indeks berkluster, dengan kata lain harus menyertakan semua kolom.

   -- 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 );
   

Anda sekarang siap untuk menjalankan analitik operasional real-time tanpa membuat perubahan apa pun pada aplikasi Anda. Kueri analitik akan berjalan terhadap indeks penyimpanan kolom dan operasi OLTP akan tetap berjalan terhadap indeks B-tree OLTP Anda. Aktivitas OLTP tetap berjalan, tetapi mengakibatkan beberapa beban tambahan untuk memelihara indeks penyimpan kolom. Lihat pengoptimalan performa di bagian berikutnya.

Postingan blog

Baca posting blog berikut untuk mempelajari selengkapnya tentang analitik operasional real time. Mungkin lebih mudah untuk memahami bagian tips performa jika Anda melihat posting blog terlebih dahulu.

• Kasus bisnis untuk analitik operasional waktu-nyata

• Menggunakan indeks penyimpan kolom non-klusster untuk analitik operasional real time

• Contoh sederhana menggunakan indeks penyimpan kolom yang tidak terkluster

• Cara SQL Server mempertahankan indeks penyimpan kolom non-klusster pada beban kerja transaksional

• Meminimalkan dampak pemeliharaan indeks penyimpan kolom tanpa kluster dengan menggunakan indeks yang difilter

• Meminimalkan dampak pemeliharaan indeks penyimpan kolom non-kluster dengan menggunakan penundaan kompresi

• Meminimalkan dampak pemeliharaan indeks kolomstore nonkluster dengan menggunakan penundaan kompresi - data kinerja

• Analitik operasional waktu nyata dengan tabel yang dioptimalkan untuk memori

• Indeks penyimpan kolom dan kebijakan penggabungan untuk grup baris

Videos

Seri video Data Exposed menjelaskan dengan lebih detail bagi beberapa kemampuan dan pertimbangan.

• Bagian 1: Cara Azure SQL Mengaktifkan Analitik Operasional Real Time (HTAP)
• Bagian 2: Mengoptimalkan database dan aplikasi yang ada dengan analitik operasional
• Bagian 3: Cara membuat analitik operasional dengan Window Functions.

Tips performa #1: Gunakan indeks yang difilter untuk meningkatkan performa kueri

Menjalankan analitik operasional real time dapat memengaruhi performa beban kerja OLTP. Dampak ini harus minimal. Contoh A menunjukkan cara menggunakan indeks yang difilter untuk meminimalkan dampak indeks penyimpan kolom non-kluster pada beban kerja transaksional sambil tetap mengirimkan analitik secara real time.

Untuk meminimalkan overhead mempertahankan indeks penyimpan kolom non-kluster pada beban kerja operasional, Anda dapat menggunakan kondisi yang difilter untuk membuat indeks penyimpan kolom non-kluster hanya pada data yang hangat atau berubah perlahan. Misalnya, dalam aplikasi manajemen pesanan, Anda dapat membuat indeks penyimpan kolom non-kluster pada pesanan yang telah dikirim. Setelah pesanan dikirim, pesanan jarang berubah dan oleh karena itu dapat dianggap data hangat. Dengan indeks yang difilter, data dalam indeks penyimpan kolom non-kluster memerlukan lebih sedikit pembaruan sehingga menurunkan dampak pada beban kerja transaksional.

Kueri analitik secara transparan mengakses data hangat dan panas sesuai kebutuhan untuk menyediakan analitik real time. Jika bagian penting dari beban kerja operasional menyentuh data 'panas', operasi tersebut tidak memerlukan pemeliharaan tambahan indeks penyimpan kolom. Praktik terbaik adalah memiliki indeks berkluster tipe rowstore pada kolom yang digunakan dalam definisi indeks yang disaring. Mesin Database menggunakan indeks berkluster untuk memindai baris yang tidak memenuhi kondisi terfilter dengan cepat. Tanpa indeks berkluster ini, pemindaian tabel lengkap tabel rowstore diperlukan untuk menemukan baris ini, yang dapat berdampak negatif pada performa kueri analitik. Jika tidak ada indeks berkluster, Anda dapat membuat indeks pohon B non-kluster yang difilter sebagai pelengkap untuk mengidentifikasi baris-baris tersebut, tetapi ini tidak disarankan karena akses terhadap rentang baris yang luas melalui indeks pohon B non-kluster itu mahal.

Note

Indeks penyimpan kolom non-kluster yang difilter hanya didukung pada tabel berbasis disk. Ini tidak didukung pada tabel yang dioptimalkan untuk memori.

Contoh A: Mengakses data panas dari indeks B-tree, data hangat dari indeks kolom

Contoh ini menggunakan kondisi yang difilter (accountkey > 0) untuk menetapkan baris mana yang disertakan dalam indeks penyimpan kolom. Tujuannya adalah untuk merancang kondisi yang telah difilter dan kueri berikutnya untuk mengakses data "panas" yang sering berubah dari indeks pohon B+, dan untuk mengakses data "hangat" yang lebih stabil dari indeks penyimpan kolom.

Note

Pengoptimal Kueri mempertimbangkan, tetapi tidak selalu memilih, indeks penyimpan kolom untuk rencana kueri. Saat pengoptimal kueri memilih indeks penyimpan kolom yang difilter, pengoptimal kueri secara transparan menggabungkan baris baik dari indeks penyimpan kolom maupun baris yang tidak memenuhi kondisi yang difilter untuk memungkinkan analitik real time. Ini berbeda dari indeks terfilter nonkluster yang hanya dapat digunakan dalam kueri yang membatasi pada baris yang ada dalam indeks.

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

Kueri analitik dijalankan dengan rencana kueri berikut. Anda dapat melihat bahwa baris yang tidak memenuhi kondisi yang difilter diakses melalui indeks pohon B berkluster.

Untuk informasi selengkapnya, lihat Blog: Indeks penyimpanan kolom non-kluster yang difilter.

Tip kinerja #2: Alihkan analitik ke sekunder yang selalu aktif untuk dibaca

Meskipun Anda dapat meminimalkan pemeliharaan indeks penyimpan kolom dengan menggunakan indeks penyimpan kolom yang difilter, kueri analitik masih dapat memerlukan sumber daya komputasi yang signifikan (CPU, I/O, memori) yang memengaruhi performa beban kerja operasional. Untuk sebagian besar beban kerja penting misi, rekomendasi kami adalah menggunakan konfigurasi AlwaysOn. Dalam konfigurasi ini, Anda dapat mengurangi dampak menjalankan analitik dengan memindahkannya ke komponen sekunder yang dapat dibaca.

Tips performa #3: Mengurangi fragmentasi indeks dengan menyimpan data panas di grup baris delta

Tabel dengan indeks penyimpan kolom mungkin terfragmentasi secara signifikan (yaitu, baris yang dihapus) jika beban kerja memperbarui/menghapus baris yang telah dikompresi. Indeks penyimpan kolom terfragmentasi menyebabkan pemanfaatan memori/penyimpanan yang tidak efisien. Selain penggunaan sumber daya yang tidak efisien, ini juga berdampak negatif pada performa kueri analitik karena I/O tambahan dan kebutuhan untuk memfilter baris yang dihapus dari kumpulan hasil.

Baris yang dihapus tidak dihapus secara fisik sampai Anda menjalankan defragmentasi indeks dengan REORGANIZE perintah atau membangun kembali indeks penyimpan kolom pada seluruh tabel atau partisi yang terpengaruh. Baik indeks REORGANIZE maupun REBUILD adalah operasi yang membutuhkan banyak sumber daya yang seharusnya dapat digunakan untuk beban kerja. Selain itu, jika baris dikompresi terlalu cepat, baris mungkin perlu dikompresi ulang beberapa kali akibat pembaruan yang menyebabkan beban kompresi yang sia-sia.

Anda dapat meminimalkan fragmentasi indeks menggunakan COMPRESSION_DELAY opsi.

-- 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);

Untuk informasi selengkapnya, lihat Blog: Penundaan pemadatan.

Berikut adalah praktik terbaik yang direkomendasikan:

• Sisipkan/Kueri beban kerja: Jika beban kerja Anda terutama menyisipkan data dan mengkuerinya, default COMPRESSION_DELAY 0 adalah opsi yang direkomendasikan. Baris yang baru disisipkan akan dikompresi setelah 1 juta baris dimasukkan ke dalam satu grup baris delta. Beberapa contoh beban kerja tersebut adalah beban kerja DW tradisional atau analisis select-stream saat Anda perlu menganalisis pola pemilihan dalam aplikasi web.

• Beban kerja OLTP: Jika beban kerja DML berat (yaitu, campuran berat dari Update, Delete, dan Insert), Anda mungkin melihat fragmentasi indeks penyimpan kolom dengan memeriksa DMV sys.dm_db_column_store_row_group_physical_stats. Jika Anda melihat bahwa > 10 baris% ditandai sebagai dihapus dalam grup baris yang baru-baru ini dikompresi, Anda dapat menggunakan opsi COMPRESSION_DELAY untuk menambahkan penundaan waktu saat baris memenuhi syarat untuk dikompresi. Misalnya, jika untuk beban kerja Anda, yang baru dimasukkan tetap 'panas' (misalnya, diperbarui beberapa kali) selama 60 menit, Anda harus memilih COMPRESSION_DELAY menjadi 60.

Nilai default dari opsi COMPRESSION_DELAY seharusnya berfungsi untuk sebagian besar pelanggan.

Untuk pengguna tingkat lanjut, sebaiknya jalankan kueri berikut dan kumpulkan % baris yang dihapus selama tujuh hari terakhir.

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;

Jika jumlah baris yang dihapus dalam grup baris terkompresi kurang dari > 20%, dan grup baris yang lebih lama menjadi datar dengan variasi < 5% (disebut sebagai grup baris dingin), maka tetapkan COMPRESSION_DELAY = (youngest_rowgroup_created_time - current_time). Pendekatan ini bekerja paling baik dengan beban kerja yang stabil dan relatif homogen.

Konten terkait

• Indeks Penyimpanan Kolom: Ikhtisar
• Indeks Penyimpan Kolom - Panduan pemuatan data
• Indeks Penyimpan Kolom - Kinerja Kueri
• Indeks penyimpan kolom di pergudangan data
• Optimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya