Bagikan melalui

Memory-Optimized Variabel Tabel

Selain tabel yang dioptimalkan memori (untuk akses data yang efisien) dan prosedur tersimpan yang dikompilasi secara asli (untuk pemrosesan kueri yang efisien dan eksekusi logika bisnis) In-Memory OLTP memperkenalkan jenis objek ketiga: jenis tabel yang dioptimalkan memori. Variabel tabel yang dibuat menggunakan jenis tabel yang dioptimalkan memori adalah variabel tabel yang dioptimalkan memori.

Variabel tabel yang dioptimalkan memori menawarkan keuntungan berikut jika dibandingkan dengan variabel tabel berbasis disk:

  • Variabel hanya disimpan dalam memori. Akses data lebih efisien karena jenis tabel yang dioptimalkan memori menggunakan algoritma memori dan struktur data yang dioptimalkan yang sama yang digunakan untuk tabel yang dioptimalkan memori, terutama ketika variabel digunakan dalam prosedur tersimpan yang dikompilasi secara asli.

  • Dengan variabel tabel yang dioptimalkan memori, tidak ada pemanfaatan tempdb. Variabel tabel tidak disimpan dalam tempdb dan tidak menggunakan sumber daya apa pun dalam tempdb.

Skenario penggunaan umum untuk variabel tabel yang dioptimalkan memori adalah:

  • Menyimpan hasil perantara dan membuat satu tataan hasil berdasarkan beberapa kueri dalam prosedur tersimpan yang dikompilasi secara asli.

  • Meneruskan parameter bernilai tabel ke dalam prosedur tersimpan yang dikompilasi secara asli dan prosedur tersimpan yang ditafsirkan.

  • Mengganti variabel tabel berbasis disk, dan dalam beberapa kasus tabel #temp yang bersifat lokal untuk prosedur yang disimpan. Ini sangat berguna jika ada banyak pertikaian tempdb dalam sistem.

  • Variabel tabel dapat digunakan untuk mensimulasikan kursor dalam prosedur tersimpan yang dikompilasi secara asli, yang dapat membantu Anda mengatasi batasan area permukaan dalam prosedur tersimpan yang dikompilasi secara asli.

Seperti tabel yang dioptimalkan memori, SQL Server menghasilkan DLL untuk setiap jenis tabel yang dioptimalkan memori. (Kompilasi dipanggil ketika jenis tabel yang dioptimalkan memori dibuat dan bukan saat digunakan untuk membuat variabel tabel yang dioptimalkan memori.) DLL ini mencakup fungsi untuk mengakses indeks dan mengambil data dari variabel tabel. Ketika variabel tabel yang dioptimalkan memori dideklarasikan berdasarkan jenis tabel, instans tabel dan struktur indeks yang sesuai dengan jenis tabel dibuat dalam sesi pengguna. Variabel tabel kemudian dapat digunakan dengan cara yang sama seperti variabel tabel berbasis disk. Anda dapat menyisipkan, memperbarui, dan menghapus baris dalam variabel tabel, dan Anda bisa menggunakan variabel dalam kueri Transact-SQL. Anda juga dapat meneruskan variabel ke prosedur tersimpan yang dikompilasi dan diinterpretasi secara langsung, sebagai parameter tipe tabel (TVP).

Sampel berikut menunjukkan jenis tabel yang dioptimalkan memori dari sampel OLTP In-Memory berbasis AdventureWorks (Sampel OLTP In-Memory SQL Server 2014).

CREATE TYPE Sales.SalesOrderDetailType_inmem
   AS TABLE
(
   OrderQty         smallint   NOT NULL,
   ProductID        int        NOT NULL,

   SpecialOfferID   int        NOT NULL
      INDEX  IX_SpecialOfferID  NONCLUSTERED,

   LocalID          int        NOT NULL,

   INDEX IX_ProductID HASH (ProductID)
      WITH ( BUCKET_COUNT = 8 )
)
WITH ( MEMORY_OPTIMIZED = ON );

Sampel menunjukkan bahwa sintaks jenis tabel yang dioptimalkan memori mirip dengan jenis tabel berbasis disk, dengan pengecualian berikut:

  • MEMORY_OPTIMIZED=ON menunjukkan bahwa jenis tabel dioptimalkan untuk memori.

  • Jenis harus memiliki setidaknya satu indeks. Seperti halnya tabel yang dioptimalkan untuk memori, Anda dapat menggunakan indeks hash dan non-klaster.

    Untuk indeks hash, jumlah wadah harus sekitar satu hingga dua kali jumlah kunci indeks unik yang diharapkan. Untuk informasi selengkapnya, lihat Menentukan Jumlah Bucket yang Benar untuk Indeks Hash.

  • Jenis data dan batasan pada tabel yang teroptimasi untuk memori juga berlaku untuk jenis tabel yang teroptimasi untuk memori. Misalnya, dalam SQL Server 2014, batasan default didukung, tetapi batasan pemeriksaan tidak didukung.

Seperti tabel yang dioptimalkan untuk memori, variabel tabel yang dioptimalkan untuk memori,

  • Jangan dukung rencana paralel.

  • Harus pas dalam memori dan jangan gunakan sumber daya disk.

Variabel tabel berbasis disk ada dalam tempdb. Variabel tabel yang dioptimalkan memori ada di database pengguna (tetapi tidak menggunakan penyimpanan dan tidak dipulihkan).

Anda tidak dapat membuat variabel tabel yang dioptimalkan memori menggunakan sintaks in-line. Tidak seperti variabel tabel berbasis disk, Anda harus membuat jenis terlebih dahulu.

Parameter dengan Nilai Tabel

Contoh skrip berikut menunjukkan deklarasi variabel tabel sebagai jenis tabel yang dioptimalkan memori Sales.SalesOrderDetailType_inmem, penyisipan tiga baris ke dalam variabel, dan penerusan variabel sebagai TVP ke Sales.usp_InsertSalesOrder_inmem.

DECLARE @od Sales.SalesOrderDetailType_inmem,  
  @SalesOrderID uniqueidentifier,  
  @DueDate datetime2 = SYSDATETIME()  
  
INSERT @od (LocalID, ProductID, OrderQty, SpecialOfferID) VALUES  
  (1, 888, 2, 1),  
  (2, 450, 13, 1),  
  (3, 841, 1, 1)  
  
EXEC Sales.usp_InsertSalesOrder_inmem  
  @SalesOrderID = @SalesOrderID,  
  @DueDate = @DueDate,  
 @OnlineOrderFlag = 1,  
  @SalesOrderDetails = @od

Tipe tabel yang dioptimalkan untuk memori dapat digunakan sebagai tipe untuk parameter bernilai tabel dalam prosedur tersimpan (TVP) dan dapat dirujuk oleh klien dengan cara yang sama persis seperti tipe tabel dan TVP berbasis disk. Oleh karena itu, pemanggilan prosedur tersimpan dengan TVP yang dioptimalkan memori, dan prosedur tersimpan yang dikompilasi secara asli berfungsi sama persis dengan pemanggilan prosedur tersimpan yang ditafsirkan dengan TVP berbasis disk.

#temp Penggantian Tabel

Sampel berikut menunjukkan jenis tabel yang dioptimalkan untuk memori dan variabel tabel sebagai pengganti tabel #temp yang bersifat lokal pada prosedur yang disimpan.

-- Using SQL procedure and temp table  
CREATE TABLE #tempTable (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
  
CREATE PROCEDURE sqlProc  
AS  
BEGIN  
  TRUNCATE TABLE #tempTable  
  
  INSERT #tempTable VALUES (1)  
  INSERT #tempTable VALUES (2)  
  INSERT #tempTable VALUES (3)  
  SELECT * FROM #tempTable  
END  
GO  
  
-- Using natively compiled stored procedure and table variable  
CREATE TYPE TT AS TABLE (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
GO  
  
CREATE PROCEDURE NCSPProc  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  DECLARE @tableVariable TT  
  INSERT @tableVariable VALUES (1)  
  INSERT @tableVariable VALUES (2)  
  INSERT @tableVariable VALUES (3)  
  SELECT c FROM @tableVariable  
END  
GO

Membuat Satu Kumpulan Hasil

Sampel berikut menunjukkan cara menyimpan hasil perantara dan membuat satu tataan hasil berdasarkan beberapa kueri dalam prosedur tersimpan yang dikompilasi secara asli. Sampel menghitung gabungan SELECT c1 FROM dbo.t1 UNION SELECT c1 FROM dbo.t2.

CREATE DATABASE hk  
GO  
ALTER DATABASE hk ADD FILEGROUP hk_mod CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE hk ADD FILE( NAME = 'hk_mod' , FILENAME = 'c:\data\hk_mod') TO FILEGROUP hk_mod;  
  
USE hk  
GO  
  
CREATE TYPE tab1 AS TABLE (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON)  
  
CREATE TABLE dbo.t1 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
CREATE TABLE dbo.t2 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
  
INSERT INTO dbo.t1 VALUES (1), (2)  
INSERT INTO dbo.t2 VALUES (3), (4)  
GO  
  
CREATE PROCEDURE dbo.p1  
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
  AS  
  BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' )  
  
    DECLARE @t dbo.tab1  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t1;  
  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t2;  
  
    SELECT c1 FROM @t;  
  END  
GO  
  
EXEC dbo.p1  
GO

Konsumsi Memori untuk Variabel Tabel

Konsumsi memori untuk variabel tabel mirip dengan tabel yang dioptimalkan untuk memori, dengan pengecualian indeks non-klaster. Jika Anda memasukkan banyak baris ke dalam variabel tabel yang dioptimalkan memori dengan indeks yang tidak terkluster dan jika kunci indeks besar, variabel tabel ini akan menggunakan jumlah memori yang tidak proporsional. Indeks nonclustered pada variabel tabel besar memerlukan memori yang lebih besar secara proporsional dibandingkan indeks nonclustered yang diperlukan untuk jumlah baris yang sama yang dimasukkan ke dalam tabel (memori yang lebih besar pada halaman indeks).

Memori untuk variabel tabel berasal dari kumpulan sumber daya Resource Governor database.

Tidak seperti tabel yang dioptimalkan untuk memori, memori yang digunakan (termasuk baris yang dihapus) oleh variabel tabel dibebaskan ketika variabel tabel keluar dari lingkup.

Memori diperhitungkan sebagai bagian dari satu-satunya konsumen memori PGPOOL dalam database.

Lihat Juga

Dukungan Transact-SQL untuk OLTP Dalam Memori

  • Last updated on