Panduan untuk Pemrosesan Kueri untuk Tabel Memory-Optimized

Artikel
06/13/2017

In-Memory OLTP memperkenalkan tabel yang dioptimalkan memori dan prosedur tersimpan yang dikompilasi secara asli dalam SQL Server. Artikel ini memberikan gambaran umum pemrosesan kueri untuk tabel yang dioptimalkan memori dan prosedur tersimpan yang dikompilasi secara asli.

Dokumen ini 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 dua tabel ini dan indeks terkait, dalam bentuk berbasis disk (tradisional) mereka:

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, kedua tabel diisi dengan data sampel dari database sampel Northwind, yang dapat Anda unduh dari Northwind dan Pubs Sample Databases 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 Merge Joinfisik . Jenis gabungan fisik lainnya adalah Nested Loops dan Hash Join. Operator Merge Join memanfaatkan fakta bahwa kedua indeks diurutkan pada kolom gabungan CustomerID.

Pertimbangkan sedikit variasi pada kueri ini, yang mengembalikan semua baris dari tabel Pesanan, tidak hanya 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 Hash Match fisik sekarang digunakan untuk Inner Join. Indeks berkluster pada Pesanan tidak diurutkan pada CustomerID, sehingga Merge Join akan memerlukan operator pengurutan, yang akan memengaruhi performa. Perhatikan biaya Hash Match relatif operator (75%) dibandingkan dengan biaya Merge Join operator dalam contoh sebelumnya (46%). Pengoptimal akan menganggap Hash Match operator juga dalam contoh sebelumnya, tetapi menyimpulkan bahwa operator memberikan performa yang Merge Join lebih baik.

Pemrosesan Kueri SQL Server untuk Tabel Disk-Based

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

SQL Server alur pemrosesan kueri.

Dalam skenario ini:

Pengguna mengeluarkan kueri.
Parser dan algebrizer membuat pohon kueri dengan operator logis berdasarkan teks Transact-SQL yang dikirimkan oleh pengguna.
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.
Mesin eksekusi kueri memproses interpretasi rencana kueri.
Untuk setiap pencarian indeks, pemindaian indeks, dan operator pemindaian tabel, mesin eksekusi meminta baris dari indeks masing-masing dan struktur tabel dari Metode Akses.
Metode Access mengambil baris dari halaman indeks dan 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 Pesanan dari Metode Akses. Metode Access melintasi struktur indeks pohon B untuk mengambil baris yang diminta. Dalam hal ini semua baris diambil sebagai panggilan paket untuk pemindaian indeks penuh.

Akses Transact-SQL yang Ditafsirkan ke Tabel Memory-Optimized

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 tsql yang ditafsirkan.
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:

Parser 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 In-Memory OLTP. Perbedaan dalam 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 menggabungkan 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 memori yang dioptimalkan 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 bukan Merge Join. Indeks pada tabel Pesanan dan Pelanggan adalah indeks hash, dan dengan demikian tidak dipesan. Merge Join 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 Kueri Contoh).

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 pembuatan. 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 dikompresi 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:

	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 menghidupkan ulang server. Atau, pengeluaran dari cache rencana, biasanya berdasarkan skema atau perubahan statistik, atau tekanan memori.
Kompilasi ulang manual	Tidak didukung. Solusinya adalah menghilangkan dan membuat ulang prosedur tersimpan.	Gunakan `sp_recompile`. Anda dapat mengeluarkan paket secara manual dari cache, misalnya melalui DBCC FREEPROCCACHE. Anda juga dapat membuat prosedur tersimpan DENGAN RECOMPILE dan prosedur tersimpan akan dikommpilasikan ulang di 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,

Pengguna mengeluarkan CREATE PROCEDURE pernyataan untuk SQL Server.
Parser dan algebrizer membuat alur pemrosesan untuk prosedur, serta pohon kueri untuk kueri Transact-SQL dalam prosedur tersimpan.
Pengoptimal membuat rencana eksekusi kueri yang dioptimalkan untuk semua kueri dalam prosedur tersimpan.
Pengkompilasi OLTP In-Memory mengambil alur pemrosesan dengan rencana kueri yang dioptimalkan yang disematkan dan menghasilkan DLL yang berisi kode mesin untuk menjalankan prosedur tersimpan.
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:

Pengguna mengeluarkan EXEC pernyataan usp_myproc.
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.
Runtime OLTP In-Memory menemukan titik masuk DLL untuk prosedur tersimpan.
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 membuat rencana eksekusi. Penggunaan parameter selama kompilasi ini disebut pengendusan parameter.

Sniffing parameter tidak digunakan untuk mengompilasi prosedur tersimpan yang dikompilasi secara asli. Semua parameter untuk 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 pada 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	Contoh kueri
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	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. `SELECT OrderID+1 FROM dbo.[Order]`
Gabungan Perulangan Berlapis	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 rencana untuk kueri yang sama dijalankan seperti Transact-SQL yang ditafsirkan berisi hash atau gabungan gabungan. `SELECT o.OrderID, c.CustomerID` `FROM dbo.[Order] o INNER JOIN dbo.[Customer] c`
Sort	`SELECT ContactName FROM dbo.Customer` `ORDER BY ContactName`
$top	`SELECT TOP 10 ContactName FROM dbo.Customer`
Urutan atas	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. `SELECT TOP 10 ContactName FROM dbo.Customer` `ORDER BY ContactName`
Agregat Aliran	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. `SELECT count(CustomerID) FROM dbo.Customer`

Statistik kolom dan Gabungan

SQL Server mempertahankan statistik pada nilai dalam 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 diperkirakan 91; 91 aktual.
Pemindaian indeks nonkluster pada CustomerID telah diperkirakan 830; 830 aktual.
Operator Gabungkan 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.

Berikut ini adalah rencana untuk kueri:

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 tabel Pelanggan:

Statistik kolom dan gabungan.

Mengenai rencana kueri ini:

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

Statistik dan Kardinalitas untuk Tabel Memory-Optimized

SQL Server mempertahankan statistik tingkat kolom untuk tabel yang dioptimalkan memori. Selain itu, ia mempertahankan jumlah baris tabel yang sebenarnya. Namun, berbeda dengan tabel berbasis disk, statistik untuk tabel yang dioptimalkan memori tidak diperbarui secara otomatis. Oleh karena itu, statistik perlu diperbarui secara manual setelah perubahan signifikan dalam tabel. Untuk informasi selengkapnya, lihat Statistik untuk Tabel Memory-Optimized.

Lihat juga

Tabel yang Dioptimalkan Memori

Bagikan melalui