Tabel sementara dan variabel tabel yang lebih cepat dengan menggunakan pengoptimalan memori

Artikel
01/16/2024

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Jika Anda menggunakan tabel sementara, variabel tabel, atau parameter bernilai tabel, pertimbangkan konversinya untuk memanfaatkan tabel yang dioptimalkan memori dan variabel tabel untuk meningkatkan performa. Perubahan kode biasanya minimal.

Artikel ini menjelaskan:

Skenario yang berdebat mendukung konversi ke Dalam Memori.
Langkah-langkah teknis untuk menerapkan konversi ke Dalam Memori.
Prasyarat sebelum konversi ke Dalam Memori.
Sampel kode yang menyoroti manfaat performa pengoptimalan memori

J. Dasar-dasar variabel tabel yang dioptimalkan memori

Variabel tabel yang dioptimalkan memori memberikan efisiensi besar dengan menggunakan algoritma dan struktur data yang dioptimalkan memori yang sama yang digunakan oleh tabel yang dioptimalkan memori. Efisiensi dimaksimalkan ketika variabel tabel diakses dari dalam modul yang dikompilasi secara asli.

Variabel tabel yang dioptimalkan memori:

Disimpan hanya dalam memori, dan tidak memiliki komponen pada disk.
Tidak melibatkan aktivitas IO.
Tidak melibatkan pemanfaatan tempdb atau pertikaian.
Dapat diteruskan ke proc tersimpan sebagai parameter bernilai tabel (TVP).
Harus memiliki setidaknya satu indeks, baik hash atau nonclustered.
- Untuk indeks hash, jumlah wadah idealnya harus 1-2 kali jumlah kunci indeks unik yang diharapkan, tetapi jumlah bucket yang berlebihan biasanya baik-baik saja (hingga 10X). Untuk detailnya, lihat Indeks untuk Tabel yang Dioptimalkan Memori.

Jenis objek

OLTP Dalam Memori menyediakan objek berikut yang dapat digunakan untuk mengoptimalkan memori tabel sementara dan variabel tabel:

Tabel yang dioptimalkan memori
- Durabilitas = SCHEMA_ONLY
Variabel tabel yang dioptimalkan memori
- Harus dideklarasikan dalam dua langkah (bukan sebaris):
  - CREATE TYPE my_type AS TABLE ...; Kemudian
  - DECLARE @mytablevariable my_type;.

B. Skenario: Ganti tempdb global ##table

Mengganti tabel sementara global dengan tabel SCHEMA_ONLY yang dioptimalkan memori cukup mudah. Perubahan terbesar adalah membuat tabel pada waktu penyebaran, bukan pada waktu proses. Pembuatan tabel yang dioptimalkan memori membutuhkan waktu lebih lama daripada pembuatan tabel tradisional, karena pengoptimalan waktu kompilasi. Membuat dan menghilangkan tabel yang dioptimalkan memori sebagai bagian dari beban kerja online akan berdampak pada performa beban kerja, serta performa pengulangan pada sekunder Grup Ketersediaan AlwaysOn dan pemulihan database.

Misalkan Anda memiliki tabel sementara global berikut.

CREATE TABLE ##tempGlobalB  
    (  
        Column1   INT   NOT NULL ,  
        Column2   NVARCHAR(4000)  
    );

Pertimbangkan untuk mengganti tabel sementara global dengan tabel yang dioptimalkan memori berikut yang memiliki DURABILITY = SCHEMA_ONLY.

CREATE TABLE dbo.soGlobalB  
(  
    Column1   INT   NOT NULL   INDEX ix1 NONCLUSTERED,  
    Column2   NVARCHAR(4000)  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
        DURABILITY        = SCHEMA_ONLY);

Langkah B.1

Konversi dari global sementara ke SCHEMA_ONLY adalah langkah-langkah berikut:

Buat tabel dbo.soGlobalB, satu kali, sama seperti tabel lokal tradisional.
Dari Transact-SQL Anda, hapus pembuatan tabel ##tempGlobalB . Penting untuk membuat tabel yang dioptimalkan memori pada waktu penyebaran, bukan pada runtime, untuk menghindari overhead kompilasi yang dilengkapi dengan pembuatan tabel.
Di T-SQL Anda, ganti semua penyebutan ##tempGlobalB dengan dbo.soGlobalB.

C. Skenario: Ganti sesi tempdb #table

Persiapan untuk mengganti tabel sementara sesi melibatkan lebih banyak T-SQL daripada untuk skenario tabel sementara global sebelumnya. Dengan senang hati T-SQL tambahan tidak berarti upaya lagi diperlukan untuk mencapai konversi.

Seperti halnya skenario tabel sementara global, perubahan terbesar adalah membuat tabel pada waktu penyebaran, bukan runtime, untuk menghindari overhead kompilasi.

Misalkan Anda memiliki tabel sementara sesi berikut.

CREATE TABLE #tempSessionC  
(  
    Column1   INT   NOT NULL ,  
    Column2   NVARCHAR(4000)  
);

Pertama, buat fungsi nilai tabel berikut untuk memfilter @@spid. Fungsi ini akan dapat digunakan oleh semua tabel SCHEMA_ONLY yang Anda konversi dari tabel sementara sesi.

CREATE FUNCTION dbo.fn_SpidFilter(@SpidFilter smallint)  
    RETURNS TABLE  
    WITH SCHEMABINDING , NATIVE_COMPILATION  
AS  
    RETURN  
        SELECT 1 AS fn_SpidFilter  
            WHERE @SpidFilter = @@spid;

Kedua, buat tabel SCHEMA_ONLY, ditambah kebijakan keamanan pada tabel.

Perhatikan bahwa setiap tabel yang dioptimalkan memori harus memiliki setidaknya satu indeks.

Untuk tabel dbo.soSessionC, indeks HASH mungkin lebih baik, jika kita menghitung BUCKET_COUNT yang sesuai. Tetapi untuk sampel ini, kami menyederhanakan ke indeks NONCLUSTERED.

CREATE TABLE dbo.soSessionC  
(  
    Column1     INT         NOT NULL,  
    Column2     NVARCHAR(4000)  NULL,  

    SpidFilter  SMALLINT    NOT NULL   DEFAULT (@@spid),  

    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
    --INDEX ix_SpidFilter HASH  
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),  
        
    CONSTRAINT CHK_soSessionC_SpidFilter  
        CHECK ( SpidFilter = @@spid ),  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
            DURABILITY = SCHEMA_ONLY);  
go  
  
  
CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy  
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)  
    ON dbo.soSessionC  
    WITH (STATE = ON);  
go

Ketiga, dalam kode T-SQL umum Anda:

Ubah semua referensi ke tabel sementara dalam pernyataan Transact-SQL Anda ke tabel baru yang dioptimalkan memori:
- Lama: #tempSessionC
- Baru: dbo.soSessionC
CREATE TABLE #tempSessionC Ganti pernyataan dalam kode Anda dengan DELETE FROM dbo.soSessionC, untuk memastikan sesi tidak diekspos ke konten tabel yang disisipkan oleh sesi sebelumnya dengan session_id yang sama. Penting untuk membuat tabel yang dioptimalkan memori pada waktu penyebaran, bukan pada runtime, untuk menghindari overhead kompilasi yang dilengkapi dengan pembuatan tabel.
DROP TABLE #tempSessionC Hapus pernyataan dari kode Anda - secara opsional Anda dapat menyisipkan DELETE FROM dbo.soSessionC pernyataan, jika ukuran memori adalah masalah potensial

D. Skenario: Variabel tabel dapat MEMORY_OPTIMIZED=ON

Variabel tabel tradisional mewakili tabel dalam database tempdb. Untuk performa yang jauh lebih cepat, Anda dapat mengoptimalkan memori variabel tabel Anda.

Berikut adalah T-SQL untuk variabel tabel tradisional. Cakupannya berakhir ketika batch atau sesi berakhir.

DECLARE @tvTableD TABLE  
    ( Column1   INT   NOT NULL ,  
      Column2   CHAR(10) );

D.1 Mengonversi sebaris menjadi eksplisit

Sintaks sebelumnya dikatakan membuat variabel tabel sebaris. Sintaksis sebaris tidak mendukung pengoptimalan memori. Jadi mari kita konversi sintaksis sebaris ke sintaks eksplisit untuk TYPE.

Cakupan: Definisi TYPE yang dibuat oleh batch pertama yang dibatasi terus berlanjut bahkan setelah server dimatikan dan dimulai ulang. Tetapi setelah pemisah go pertama, tabel @tvTableC yang dinyatakan hanya bertahan sampai proses berikutnya tercapai dan batch berakhir.

CREATE TYPE dbo.typeTableD  
    AS TABLE  
    (  
        Column1  INT   NOT NULL ,  
        Column2  CHAR(10)  
    );  
go  
        
SET NoCount ON;  
DECLARE @tvTableD dbo.typeTableD  
;  
INSERT INTO @tvTableD (Column1) values (1), (2)  
;  
SELECT * from @tvTableD;  
go

D.2 Mengonversi eksplisit pada disk ke memori yang dioptimalkan

Variabel tabel yang dioptimalkan memori tidak berada di tempdb. Pengoptimalan memori menghasilkan peningkatan kecepatan yang seringkali 10 kali lebih cepat atau lebih.

Konversi ke memori yang dioptimalkan dicapai hanya dalam satu langkah. Tingkatkan pembuatan TYPE eksplisit menjadi berikut, yang menambahkan:

Indeks. Sekali lagi, setiap tabel yang dioptimalkan memori harus memiliki setidaknya satu indeks.
MEMORY_OPTIMIZED = AKTIF.

CREATE TYPE dbo.typeTableD  
    AS TABLE  
    (  
        Column1  INT   NOT NULL   INDEX ix1,  
        Column2  CHAR(10)  
    )  
    WITH  
        (MEMORY_OPTIMIZED = ON);

Selesai.

E. FILEGROUP prasyarat untuk SQL Server

Di Microsoft SQL Server, untuk menggunakan fitur yang dioptimalkan memori, database Anda harus memiliki FILEGROUP yang dideklarasikan dengan MEMORY_OPTIMIZED_DATA.

Azure SQL Database tidak memerlukan pembuatan FILEGROUP ini.

Prasyarat: Kode T-SQL berikut untuk FILEGROUP adalah prasyarat untuk sampel kode T-SQL yang panjang di bagian selanjutnya dari artikel ini.

Anda harus menggunakan SSMS.exe atau alat lain yang dapat mengirimkan T-SQL.
Tempelkan sampel kode FILEGROUP T-SQL ke dalam SQL Server Management Studio.
Edit T-SQL untuk mengubah nama dan jalur direktori spesifiknya sesuai keinginan Anda.

Semua direktori dalam nilai FILENAME harus sudah ada sebelumnya, kecuali direktori akhir tidak boleh ada sebelumnya.

Jalankan T-SQL yang diedit.

Tidak perlu menjalankan FILEGROUP T-SQL lebih dari satu kali, bahkan jika Anda berulang kali menyesuaikan dan menjalankan ulang perbandingan kecepatan T-SQL di subbagian berikutnya.

ALTER DATABASE InMemTest2  
    ADD FILEGROUP FgMemOptim3  
        CONTAINS MEMORY_OPTIMIZED_DATA;  
go  
ALTER DATABASE InMemTest2  
    ADD FILE  
    (  
        NAME = N'FileMemOptim3a',  
        FILENAME = N'C:\DATA\FileMemOptim3a'  
                    --  C:\DATA\    preexisted.  
    )  
    TO FILEGROUP FgMemOptim3;  
go

Skrip berikut membuat grup file untuk Anda dan mengonfigurasi pengaturan database yang direkomendasikan: enable-in-memory-oltp.sql

Untuk informasi selengkapnya tentang ALTER DATABASE ... ADD FILE dan FILEGROUP, lihat:

F. Tes cepat untuk membuktikan peningkatan kecepatan

Bagian ini menyediakan kode Transact-SQL yang dapat Anda jalankan untuk menguji dan membandingkan perolehan kecepatan untuk INSERT-DELETE dari menggunakan variabel tabel yang dioptimalkan memori. Kode terdiri dari dua bagian yang hampir sama, kecuali pada paruh pertama jenis tabel dioptimalkan memori.

Tes perbandingan berlangsung sekitar 7 detik. Untuk menjalankan sampel:

Prasyarat: Anda harus sudah menjalankan FILEGROUP T-SQL dari bagian sebelumnya.
Jalankan skrip T-SQL INSERT-DELETE berikut.

Perhatikan pernyataan 'GO 5001', yang mengirimkan ulang T-SQL 5001 kali. Anda dapat menyesuaikan nomor dan menjalankan ulang.

Saat menjalankan skrip di Azure SQL Database, pastikan untuk menjalankan dari VM di wilayah yang sama.

PRINT ' ';
PRINT '---- Next, memory-optimized, faster. ----';

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

CREATE TYPE dbo.typeTableC_mem -- !!  Memory-optimized.  
AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR(10)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _mem.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_mem;-- !!  

INSERT INTO @tvTableC (Column1)
VALUES (1), (2);

INSERT INTO @tvTableC (Column1)
VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _mem.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

---- End memory-optimized.  
-------------------------------------------------  
---- Start traditional on-disk.  
PRINT ' ';
PRINT '---- Next, tempdb based, slower. ----';

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

CREATE TYPE dbo.typeTableC_tempdb -- !!  Traditional tempdb.  
AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR(10)
);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _tempdb.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_tempdb;-- !!  

INSERT INTO @tvTableC (Column1)
VALUES (1), (2);

INSERT INTO @tvTableC (Column1)
VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _tempdb.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

PRINT '---- Tests done. ----';
GO

Berikut adalah hasil yang ditetapkan.

---- Next, memory-optimized, faster. ----  
2016-04-20 00:26:58.033  = Begin time, _mem.  
Beginning execution loop  
Batch execution completed 5001 times.  
2016-04-20 00:26:58.733  = End time, _mem.  

---- Next, tempdb based, slower. ----  
2016-04-20 00:26:58.750  = Begin time, _tempdb.  
Beginning execution loop  
Batch execution completed 5001 times.  
2016-04-20 00:27:05.440  = End time, _tempdb.  
---- Tests done. ----

G. Memprediksi konsumsi memori aktif

Anda dapat belajar memprediksi kebutuhan memori aktif tabel yang dioptimalkan memori Anda dengan sumber daya berikut:

Untuk variabel tabel yang lebih besar, indeks nonclustered menggunakan lebih banyak memori daripada yang mereka lakukan untuk tabel yang dioptimalkan memori. Semakin besar jumlah baris dan kunci indeks, semakin banyak perbedaan yang meningkat.

Jika variabel tabel yang dioptimalkan memori hanya diakses dengan satu nilai kunci yang tepat per akses, indeks hash mungkin menjadi pilihan yang lebih baik daripada indeks non-klusster. Namun, jika Anda tidak dapat memperkirakan BUCKET_COUNT yang sesuai, indeks NONCLUSTERED adalah pilihan kedua yang baik.

H. Baca juga

Tabel yang Dioptimalkan Memori.
Menentukan Durabilitas untuk Objek yang Dioptimalkan Memori.
Pembaruan Kumulatif untuk menghilangkan kemungkinan kesalahan Kehabisan Memori yang tidak tepat, diumumkan dalam blog September 2017.
- Versi build SQL Server 2016 menyediakan detail lengkap rilis, paket layanan, dan pembaruan kumulatif.
- Kesalahan yang sesekali tidak tepat ini tidak terjadi di SQL Server edisi Enterprise.

Bagikan melalui