Tabel sementara dan variabel tabel yang lebih cepat dengan menggunakan pengoptimalan memori
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 ...;
kemudianDECLARE @mytablevariable my_type;
.
- Harus dideklarasikan dalam dua langkah (bukan sebaris):
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 denganDELETE 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 menyisipkanDELETE 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:
- Memperkirakan Persyaratan Memori untuk Tabel yang Dioptimalkan Memori
- Ukuran Tabel dan Baris dalam Tabel yang Dioptimalkan Memori: Contoh Perhitungan
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. Lihat juga
Menentukan Durabilitas untuk Objek yang Dioptimalkan Memori.
-
- 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.