Panduan untuk Pemrosesan Kueri untuk Tabel yang Dioptimalkan Memori

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

OLTP Dalam Memori memperkenalkan tabel yang dioptimalkan memori dan prosedur tersimpan yang dikompilasi secara asli di SQL Server. Artikel ini memberikan gambaran umum pemrosesan kueri untuk tabel yang dioptimalkan memori dan prosedur tersimpan yang dikompilasi secara asli.

Dokumen menjelaskan bagaimana kueri pada tabel yang dioptimalkan memori dikompilasi dan dijalankan, termasuk:

• Alur pemrosesan kueri di SQL Server untuk tabel berbasis disk.

• Pengoptimalan kueri; peran statistik pada tabel yang dioptimalkan memori serta panduan untuk memecahkan masalah rencana kueri yang buruk.

• Penggunaan Transact-SQL yang ditafsirkan untuk mengakses tabel yang dioptimalkan memori.

• Pertimbangan tentang pengoptimalan kueri untuk akses tabel yang dioptimalkan memori.

• Kompilasi dan pemrosesan prosedur tersimpan yang dikompilasi secara asli.

• Statistik yang digunakan untuk estimasi biaya oleh pengoptimal.

• Cara untuk memperbaiki rencana kueri yang buruk.

Kueri Contoh

Contoh berikut akan digunakan untuk mengilustrasikan konsep pemrosesan kueri yang dibahas dalam artikel ini.

Kami mempertimbangkan dua tabel, Pelanggan dan Pesanan. Skrip Transact-SQL berikut berisi definisi untuk kedua tabel ini dan indeks terkait, dalam bentuk berbasis disk (tradisional):

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO  

Untuk membuat rencana kueri yang diperlihatkan dalam artikel ini, dua tabel diisi dengan data sampel dari database sampel Northwind, yang dapat Anda unduh dari Northwind dan pub Sampel Database untuk SQL Server 2000.

Pertimbangkan kueri berikut, yang menggabungkan tabel Pelanggan dan Pesanan dan mengembalikan ID pesanan dan informasi pelanggan terkait:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Perkiraan rencana eksekusi seperti yang ditampilkan oleh SQL Server Management Studio adalah sebagai berikut

Rencana kueri untuk bergabung dengan tabel berbasis disk.

Tentang rencana kueri ini:

• Baris dari tabel Pelanggan diambil dari indeks berkluster, yang merupakan struktur data utama dan memiliki data tabel lengkap.

• Data dari tabel Pesanan diambil menggunakan indeks non-kluster pada kolom CustomerID. Indeks ini berisi kolom CustomerID, yang digunakan untuk gabungan, dan kolom kunci utama OrderID, yang dikembalikan kepada pengguna. Mengembalikan kolom tambahan dari tabel Pesanan akan memerlukan pencarian dalam indeks berkluster untuk tabel Pesanan.

• Operator logis Inner Join diimplementasikan oleh operator fisik Merge Join. Jenis gabungan fisik lainnya adalah Nested Loops dan Hash Join. Operator Gabungkan Gabungan memanfaatkan fakta bahwa kedua indeks diurutkan pada kolom gabungan CustomerID.

Pertimbangkan sedikit variasi pada kueri ini, yang mengembalikan semua kolom dari tabel Pesanan, tidak hanya kolom OrderID:

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Perkiraan rencana untuk kueri ini adalah:

Rencana kueri untuk gabungan hash tabel berbasis disk.

Dalam kueri ini, baris dari tabel Pesanan diambil menggunakan indeks berkluster. Operator fisik Hash Match sekarang digunakan untuk Gabungan Dalam. Indeks berkluster pada Urutan tidak diurutkan pada CustomerID, sehingga Gabungan Gabungan akan memerlukan operator pengurutan, yang akan memengaruhi performa. Perhatikan biaya relatif operator Hash Match (75%) dibandingkan dengan biaya operator Gabung gabungan dalam contoh sebelumnya (46%). Pengoptimal akan menganggap operator Hash Match juga dalam contoh sebelumnya, tetapi menyimpulkan bahwa operator Gabung Gabung memberikan performa yang lebih baik.

Pemrosesan Kueri SQL Server untuk Tabel Berbasis Disk

Diagram berikut menguraikan alur pemrosesan kueri di SQL Server untuk kueri ad hoc:

Alur pemrosesan kueri SQL Server.

Dalam skenario ini:

1. Pengguna mengeluarkan kueri.

2. Pengurai dan algebrizer membuat pohon kueri dengan operator logis berdasarkan teks Transact-SQL yang dikirimkan oleh pengguna.

3. Pengoptimal membuat rencana kueri yang dioptimalkan yang berisi operator fisik (misalnya, gabungan perulangan berlapis). Setelah pengoptimalan, paket dapat disimpan dalam cache paket. Langkah ini dilewati jika cache paket sudah berisi rencana untuk kueri ini.

4. Mesin eksekusi kueri memproses interpretasi rencana kueri.

5. Untuk setiap pencarian indeks, pemindaian indeks, dan operator pemindaian tabel, mesin eksekusi meminta baris dari indeks masing-masing dan struktur tabel dari Metode Akses.

6. Metode Access mengambil baris dari indeks dan halaman data di kumpulan buffer dan memuat halaman dari disk ke dalam kumpulan buffer sesuai kebutuhan.

Untuk kueri contoh pertama, mesin eksekusi meminta baris dalam indeks berkluster pada Pelanggan dan indeks non-kluster pada Urutan dari Metode Akses. Metode Akses melintasi struktur indeks pohon B untuk mengambil baris yang diminta. Dalam hal ini semua baris diambil sebagai panggilan paket untuk pemindaian indeks penuh.

Catatan

Dokumentasi SQL Server menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, SQL Server mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau penyimpanan data dalam memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

Akses Transact-SQL yang Ditafsirkan ke Tabel yang Dioptimalkan Memori

Batch ad hoc Transact-SQL dan prosedur tersimpan juga disebut sebagai Transact-SQL yang ditafsirkan. Ditafsirkan mengacu pada fakta bahwa rencana kueri ditafsirkan oleh mesin eksekusi kueri untuk setiap operator dalam rencana kueri. Mesin eksekusi membaca operator dan parameternya dan melakukan operasi.

Transact-SQL yang ditafsirkan dapat digunakan untuk mengakses tabel yang dioptimalkan memori dan berbasis disk. Gambar berikut mengilustrasikan pemrosesan kueri untuk akses Transact-SQL yang ditafsirkan ke tabel yang dioptimalkan memori:

Alur pemrosesan kueri untuk akses Transact-SQL yang ditafsirkan ke tabel yang dioptimalkan memori.

Seperti yang diilustrasikan oleh gambar, alur pemrosesan kueri sebagian besar tetap tidak berubah:

• Pengurai dan algebrizer membuat pohon kueri.

• Pengoptimal membuat rencana eksekusi.

• Mesin eksekusi kueri menginterpretasikan rencana eksekusi.

Perbedaan utama dengan alur pemrosesan kueri tradisional (gambar 2) adalah bahwa baris untuk tabel yang dioptimalkan memori tidak diambil dari kumpulan buffer menggunakan Metode Akses. Sebagai gantinya, baris diambil dari struktur data dalam memori melalui mesin OLTP Dalam Memori. Perbedaan struktur data menyebabkan pengoptimal memilih paket yang berbeda dalam beberapa kasus, seperti yang diilustrasikan oleh contoh berikut.

Skrip Transact-SQL berikut berisi versi tabel Pesanan dan Pelanggan yang dioptimalkan memori, menggunakan indeks hash:

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO  

Pertimbangkan kueri yang sama yang dijalankan pada tabel yang dioptimalkan memori:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Perkiraan rencana adalah sebagai berikut:

Rencana kueri untuk gabungan tabel yang dioptimalkan memori.

Amati perbedaan berikut dengan rencana untuk kueri yang sama pada tabel berbasis disk (gambar 1):

• Paket ini berisi pemindaian tabel daripada pemindaian indeks berkluster untuk tabel Pelanggan:

  • Definisi tabel tidak berisi indeks berkluster.

  • Indeks berkluster tidak didukung dengan tabel yang dioptimalkan memori. Sebaliknya, setiap tabel yang dioptimalkan memori harus memiliki setidaknya satu indeks non-kluster dan semua indeks pada tabel yang dioptimalkan memori dapat secara efisien mengakses semua kolom dalam tabel tanpa harus menyimpannya dalam indeks atau merujuk ke indeks berkluster.

• Paket ini berisi Hash Match daripada Gabungan Gabungan. Indeks pada tabel Pesanan dan Pelanggan adalah indeks hash, dan dengan demikian tidak diurutkan. Gabungan Gabungan akan memerlukan operator pengurutan yang akan mengurangi performa.

Prosedur Tersimpan yang Dikompilasi Secara Asli

Prosedur tersimpan yang dikompilasi secara asli adalah prosedur tersimpan Transact-SQL yang dikompilasi ke kode mesin, daripada ditafsirkan oleh mesin eksekusi kueri. Skrip berikut membuat prosedur tersimpan yang dikompilasi secara asli yang menjalankan kueri contoh (dari bagian Contoh Kueri).

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END  

Prosedur tersimpan yang dikompilasi secara asli dikompilasi pada waktu pembuatan, sedangkan prosedur tersimpan yang ditafsirkan dikompilasi pada waktu eksekusi pertama. (Sebagian kompilasi, terutama penguraian dan algebrisasi, terjadi saat membuat. Namun, untuk prosedur tersimpan yang ditafsirkan, pengoptimalan rencana kueri terjadi pada eksekusi pertama.) Logika kompilasi ulang serupa. Prosedur tersimpan yang dikompilasi secara asli dikompilasi ulang pada eksekusi pertama prosedur jika server dimulai ulang. Prosedur tersimpan yang ditafsirkan ulang jika paket tidak lagi berada dalam cache paket. Tabel berikut ini meringkas kasus kompilasi dan kompilasi ulang untuk prosedur tersimpan yang dikompilasi dan ditafsirkan secara asli:

Jenis kompilasi	Dikompilasi secara asli	Ditafsirkan
Kompilasi awal	Pada waktu pembuatan.	Pada eksekusi pertama.
Kompilasi ulang otomatis	Setelah eksekusi pertama prosedur setelah database atau server dimulai ulang.	Pada mulai ulang server. Atau, pengeluaran dari cache rencana, biasanya berdasarkan perubahan skema atau statistik, atau tekanan memori.
Kompilasi ulang manual	Gunakan sp_recompile.	Gunakan sp_recompile. Anda dapat secara manual mengeluarkan paket dari cache, misalnya melalui DBCC FREEPROCCACHE. Anda juga dapat membuat prosedur tersimpan DENGAN RECOMPILE dan prosedur tersimpan akan dikompresi ulang pada setiap eksekusi.

Kompilasi dan Pemrosesan Kueri

Diagram berikut mengilustrasikan proses kompilasi untuk prosedur tersimpan yang dikompilasi secara asli:

Kompilasi asli prosedur tersimpan.

Proses ini digambarkan sebagai,

1. Pengguna mengeluarkan pernyataan CREATE PROCEDURE ke SQL Server.

2. Pengurai dan algebrizer membuat alur pemrosesan untuk prosedur, serta pohon kueri untuk kueri Transact-SQL dalam prosedur tersimpan.

3. Pengoptimal membuat rencana eksekusi kueri yang dioptimalkan untuk semua kueri dalam prosedur tersimpan.

4. Pengkompilasi OLTP Dalam Memori mengambil alur pemrosesan dengan rencana kueri yang dioptimalkan yang disematkan dan menghasilkan DLL yang berisi kode mesin untuk menjalankan prosedur tersimpan.

5. DLL yang dihasilkan dimuat ke dalam memori.

Pemanggilan prosedur tersimpan yang dikompilasi secara asli diterjemahkan untuk memanggil fungsi di DLL.

Eksekusi prosedur tersimpan yang dikompilasi secara asli.

Pemanggilan prosedur tersimpan yang dikompilasi secara asli dijelaskan sebagai berikut:

1. Pengguna mengeluarkan pernyataan usp_myproc EXEC.

2. Pengurai mengekstrak nama dan parameter prosedur tersimpan.

   Jika pernyataan disiapkan, misalnya menggunakan sp_prep_exec, pengurai tidak perlu mengekstrak nama prosedur dan parameter pada waktu eksekusi.

3. Runtime OLTP Dalam Memori menemukan titik masuk DLL untuk prosedur tersimpan.

4. Kode mesin dalam DLL dijalankan dan hasilnya dikembalikan ke klien.

Sniffing parameter

Prosedur tersimpan Transact-SQL yang ditafsirkan dikompilasi pada eksekusi pertama, berbeda dengan prosedur tersimpan yang dikompilasi secara asli, yang dikompilasi pada waktu pembuatan. Ketika prosedur tersimpan yang ditafsirkan dikompilasi saat pemanggilan, nilai parameter yang disediakan untuk pemanggilan ini digunakan oleh pengoptimal saat menghasilkan rencana eksekusi. Penggunaan parameter ini selama kompilasi disebut sniffing parameter.

Sniffing parameter tidak digunakan untuk mengompilasi prosedur tersimpan yang dikompilasi secara asli. Semua parameter ke prosedur tersimpan dianggap memiliki nilai UNKNOWN. Seperti prosedur tersimpan yang ditafsirkan, prosedur tersimpan yang dikompilasi secara asli juga mendukung petunjuk OPTIMIZE FOR . Untuk informasi selengkapnya, lihat Petunjuk Kueri (Transact-SQL).

Mengambil Rencana Eksekusi Kueri untuk Prosedur Tersimpan yang Dikompilasi Secara Asli

Rencana eksekusi kueri untuk prosedur tersimpan yang dikompilasi secara asli dapat diambil menggunakan Estimasi Rencana Eksekusi di Management Studio, atau menggunakan opsi SHOWPLAN_XML di Transact-SQL. Contohnya:

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO  

Rencana eksekusi yang dihasilkan oleh pengoptimal kueri terdiri dari pohon dengan operator kueri pada simpul dan daun pohon. Struktur pohon menentukan interaksi (aliran baris dari satu operator ke operator lainnya) antara operator. Dalam tampilan grafis SQL Server Management Studio, alurnya dari kanan ke kiri. Misalnya, rencana kueri di gambar 1 berisi dua operator pemindaian indeks, yang memasok baris ke operator gabungan gabungan. Operator gabungan memasok baris ke operator tertentu. Operator pilih, akhirnya, mengembalikan baris ke klien.

Operator Kueri dalam Prosedur Tersimpan yang Dikompilasi Secara Asli

Tabel berikut ini meringkas operator kueri yang didukung di dalam prosedur tersimpan yang dikompilasi secara asli:

Operator	Kueri Sampel	Catatan
SELECT	SELECT OrderID FROM dbo.[Order]
INSERT	INSERT dbo.Customer VALUES ('abc', 'def')
UPDATE	UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'
DELETE	DELETE dbo.Customer WHERE CustomerID='abc'
Skalar Komputasi	SELECT OrderID+1 FROM dbo.[Order]	Operator ini digunakan baik untuk fungsi intrinsik maupun konversi jenis. Tidak semua fungsi dan konversi jenis didukung di dalam prosedur tersimpan yang dikompilasi secara asli.
Gabungan Perulangan Berlapis	SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c	Perulangan Berlapis adalah satu-satunya operator gabungan yang didukung dalam prosedur tersimpan yang dikompilasi secara asli. Semua paket yang berisi gabungan akan menggunakan operator Nested Loops, bahkan jika paket untuk kueri yang sama dijalankan seperti transact-SQL yang ditafsirkan berisi hash atau gabungan gabungan.
Sort	SELECT ContactName FROM dbo.Customer ORDER BY ContactName
$top	SELECT TOP 10 ContactName FROM dbo.Customer
Urutan teratas	SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName	Ekspresi TOP (jumlah baris yang akan dikembalikan) tidak boleh melebihi 8.000 baris. Lebih sedikit jika ada juga operator gabungan dan agregasi dalam kueri. Gabungan dan agregasi biasanya mengurangi jumlah baris yang akan diurutkan, dibandingkan dengan jumlah baris tabel dasar.
Agregat Stream	SELECT count(CustomerID) FROM dbo.Customer	Perhatikan bahwa operator Hash Match tidak didukung untuk agregasi. Oleh karena itu, semua agregasi dalam prosedur tersimpan yang dikompilasi secara asli menggunakan operator Agregat Aliran, bahkan jika rencana untuk kueri yang sama dalam Transact-SQL yang ditafsirkan menggunakan operator Hash Match.

Statistik dan Gabungan Kolom

SQL Server mempertahankan statistik pada nilai di kolom kunci indeks untuk membantu memperkirakan biaya operasi tertentu, seperti pemindaian indeks dan pencarian indeks. ( SQL Server juga membuat statistik pada kolom kunci non-indeks jika Anda secara eksplisit membuatnya atau jika pengoptimal kueri membuatnya sebagai respons terhadap kueri dengan predikat.) Metrik utama dalam estimasi biaya adalah jumlah baris yang diproses oleh satu operator. Perhatikan bahwa untuk tabel berbasis disk, jumlah halaman yang diakses oleh operator tertentu signifikan dalam estimasi biaya. Namun, karena jumlah halaman tidak penting untuk tabel yang dioptimalkan memori (selalu nol), diskusi ini berfokus pada jumlah baris. Estimasi dimulai dengan operator pencarian dan pemindaian indeks dalam rencana, dan kemudian diperluas untuk menyertakan operator lain, seperti operator gabungan. Perkiraan jumlah baris yang akan diproses oleh operator gabungan didasarkan pada estimasi untuk operator indeks, pencarian, dan pemindaian yang mendasarinya. Untuk akses Transact-SQL yang ditafsirkan ke tabel yang dioptimalkan memori, Anda dapat mengamati rencana eksekusi aktual untuk melihat perbedaan antara perkiraan dan jumlah baris aktual untuk operator dalam rencana.

Untuk contoh dalam gambar 1,

• Pemindaian indeks berkluster pada Pelanggan telah memperkirakan 91; 91 aktual.
• Pemindaian indeks nonclustered pada CustomerID telah memperkirakan 830; 830 aktual.
• Operator Gabung Gabung telah memperkirakan 815; 830 aktual.

Perkiraan untuk pemindaian indeks akurat. SQL Server mempertahankan jumlah baris untuk tabel berbasis disk. Perkiraan untuk pemindaian tabel dan indeks penuh selalu akurat. Perkiraan untuk gabungan juga cukup akurat.

Jika perkiraan ini berubah, pertimbangan biaya untuk alternatif paket yang berbeda juga berubah. Misalnya, jika salah satu sisi gabungan memiliki perkiraan jumlah baris 1 atau hanya beberapa baris, menggunakan gabungan perulangan berlapis lebih murah. Pertimbangkan kueri berikut:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Setelah menghapus semua baris tetapi satu di Customer tabel, rencana kueri berikut dibuat:

Mengenai rencana kueri ini:

• Hash Match telah diganti dengan operator gabungan fisik Nested Loops.
• Pemindaian indeks lengkap pada IX_CustomerID telah diganti dengan pencarian indeks. Ini menghasilkan pemindaian 5 baris, alih-alih 830 baris yang diperlukan untuk pemindaian indeks penuh.

Lihat Juga

Tabel yang Dioptimalkan Memori