Indeks penyimpan kolom di pergudangan data

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSistem Platform Analitik (PDW)Database SQL di Microsoft Fabric

Indeks penyimpan kolom, bersama dengan pemartisian, sangat penting untuk membangun gudang data SQL Server. Artikel ini berfokus pada kasus penggunaan utama dan contoh desain pergudangan data dengan SQL Database Engine.

Fitur utama untuk pergudangan data

SQL Server 2016 (13.x) memperkenalkan fitur-fitur ini untuk peningkatan performa penyimpan kolom:

  • Grup ketersediaan AlwaysOn mendukung kueri indeks penyimpan kolom pada replika sekunder yang dapat dibaca.
  • Beberapa Active Result Sets (MARS) mendukung indeks penyimpan kolom.
  • Tampilan manajemen dinamis baru sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) menyediakan informasi pemecahan masalah performa di tingkat grup baris.
  • Semua kueri pada indeks penyimpan kolom dapat berjalan dalam mode batch. Sebelumnya, hanya kueri paralel yang dapat berjalan dalam mode batch.
  • Operator Urutkan, Urutan Berbeda, dan Berbeda berjalan dalam mode batch.
  • Agregat jendela sekarang berjalan dalam mode batch untuk tingkat kompatibilitas database 130 dan yang lebih tinggi.
  • Penurunan agregat untuk pemrosesan agregat yang efisien. Ini didukung pada semua tingkat kompatibilitas database.
  • String predikat dorongan ke bawah untuk pemrosesan predikat string yang efisien. Ini didukung pada semua tingkat kompatibilitas database.
  • Isolasi cuplikan untuk tingkat kompatibilitas database 130 atau lebih tinggi.
  • Indeks penyimpan kolom berkluster yang diurutkan diperkenalkan dengan SQL Server 2022 (16.x). Untuk informasi selengkapnya, lihat MEMBUAT INDEKS PENYIMPAN KOLOM dan Penyetelan Performa dengan Indeks Penyimpan Kolom yang Diurutkan . Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Ketersediaan indeks kolom yang diurutkan.

Untuk informasi selengkapnya tentang fitur baru dalam versi dan platform SQL Server dan Azure SQL, lihat Apa yang baru dalam indeks penyimpan kolom.

Meningkatkan kinerja dengan menggabungkan indeks nonclustered dan columnstore

Dimulai dengan SQL Server 2016 (13.x), Anda dapat membuat indeks nonclustered rowstore pada indeks penyimpan kolom berkluster.

Contoh: Meningkatkan efisiensi pencarian data tabel dengan indeks tanpa pengelompokan

Untuk meningkatkan efisiensi pencarian tabel di gudang data, Anda dapat membuat indeks nonclustered yang dioptimalkan untuk menjalankan kueri yang paling efektif menggunakan pencarian tabel. Misalnya, kueri yang mencari nilai yang cocok atau mengembalikan rentang kecil nilai lebih cocok dibandingkan dengan indeks pohon B daripada indeks kolom. Mereka tidak memerlukan pemindaian penuh indeks penyimpan kolom dan mengembalikan hasil yang benar lebih cepat dengan melakukan pencarian biner melalui indeks pohon B.

--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.

--Create the table
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int
);

--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;

--Add a nonclustered index.
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

Contoh: Gunakan indeks non-kluster untuk memberlakukan batasan kunci utama pada tabel penyimpan kolom

Karena tabel dapat memiliki paling banyak satu indeks berkluster, tabel dengan indeks penyimpan kolom berkluster tidak dapat memiliki batasan kunci utama terkluster. Untuk membuat batasan kunci utama pada tabel penyimpan kolom, Anda harus mendeklarasikannya sebagai non-kluster.

Contoh berikut membuat tabel dengan batasan kunci primer non-kluster lalu membuat indeks penyimpan kolom berkluster pada tabel. Karena penyisipan atau pembaruan apa pun pada tabel penyimpan kolom juga memodifikasi indeks non-kluster, semua operasi yang melanggar batasan kunci utama menyebabkan seluruh operasi gagal.

--Create a primary key constraint on a columnstore table.

--Create a rowstore table with a nonclustered primary key constraint.
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int,
    CONSTRAINT pk_account PRIMARY KEY NONCLUSTERED (AccountKey)
);

--Convert the table to columnstore.
--The primary key constraint is preserved as a nonclustered index on the columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account;

Meningkatkan performa dengan mengaktifkan penguncian tingkat baris dan tingkat grup baris

Untuk melengkapi indeks nonclustered pada fitur indeks columnstore, SQL Server 2016 (13.x) menawarkan kemampuan penguncian terperinci untuk operasi SELECT, UPDATE, dan DELETE. Kueri dapat berjalan dengan penguncian tingkat baris pada pencarian indeks terhadap indeks nonclustered dan penguncian tingkat grup baris pada pemindaian tabel penuh terhadap indeks penyimpan kolom. Gunakan ini untuk mencapai keserentakan baca/tulis yang lebih tinggi dengan menggunakan penguncian secara tepat pada tingkat baris dan grup baris.

--Granular locking example
--Store table t_account as a columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account

--Add a nonclustered index for use with this example
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

--Look at locking with access through the nonclustered index
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN
    -- The query plan chooses a seek operation on the nonclustered index
    -- and takes the row lock
    SELECT * 
    FROM t_account 
    WHERE AccountKey = 100;
COMMIT TRAN;

Isolasi snapshot dan isolasi snapshot dengan baca terkunci

Gunakan isolasi rekam jepret (SI) untuk menjamin konsistensi transaksional, dan isolasi rekam jepret yang diterapkan baca (RCSI) untuk menjamin konsistensi tingkat pernyataan untuk kueri pada indeks penyimpan kolom. Ini memungkinkan kueri berjalan tanpa memblokir penulis data. Perilaku non-blokir ini juga secara signifikan mengurangi kemungkinan kebuntuan untuk transaksi yang kompleks. Untuk informasi selengkapnya, lihat Tingkat isolasi berbasis pemversionan baris di Mesin Basis Data.

Konten terkait

  • Last updated on