Bagikan melalui

Panduan Pemrosesan Kueri untuk Tabel yang Dioptimalkan untuk Memori

Berlaku untuk: SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

OLTP In-Memory memperkenalkan tabel yang dioptimalkan untuk memori dan prosedur tersimpan yang dikompilasi secara native 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 perencanaan 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.
Rencana eksekusi kueri untuk penggabungan 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 Merge Join memanfaatkan fakta bahwa kedua indeks diurutkan pada kolom join 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.
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 Pesanan tidak diurutkan pada CustomerID, sehingga Merge Join akan memerlukan operator penyortiran, yang akan memengaruhi kinerja. Perhatikan biaya operator Hash Match relatif (75%) dibandingkan dengan biaya operator Gabung dalam contoh sebelumnya (46%). Pengoptimalisasi juga akan mempertimbangkan operator Hash Match dalam contoh sebelumnya, tetapi menyimpulkan bahwa operator Merge Join 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.
Alur pemrosesan kueri SQL Server.

Dalam skenario ini:

  1. Pengguna mengajukan 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, rencana dapat disimpan dalam cache rencana. Langkah ini dilewati jika cache rencana 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 contoh kueri pertama, mesin pemroses meminta baris dari indeks berkluster pada Pelanggan dan indeks non-kluster pada Pesanan dari Metode Akses. Metode Akses melintasi struktur indeks pohon B untuk mengambil baris yang diminta. Dalam hal ini, semua baris diambil karena rencana memerlukan pemindaian indeks penuh.

Catatan

Dokumentasi menggunakan istilah pohon B umumnya untuk merujuk pada indeks. Dalam indeks rowstore, Mesin Database mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau indeks pada tabel yang dioptimalkan 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 tsql yang ditafsirkan.
Alur pemrosesan kueri untuk akses Transact-SQL yang diinterpretasikan ke tabel yang dioptimalkan untuk memori.

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

  • Pengurai dan algebrizer membentuk 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 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 memori yang dioptimalkan.
Rencana kueri untuk gabungan tabel yang dioptimalkan memori.

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

  • Rencana ini berisi pemindaian tabel sebagai gantinya pemindaian indeks berkerumun 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.

  • Rencana ini berisi Hash Match daripada Penggabungan. Indeks pada kedua tabel, Pesanan dan Pelanggan, adalah indeks hash, dan dengan demikian tidak diurutkan. Merge Join akan memerlukan operator pengurutan yang akan menurunkan kinerja.

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 akan dikompilasi ulang jika rencana tidak lagi berada dalam cache rencana. 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 Pada pelaksanaan pertama prosedur, setelah database atau server di-restart. Pada mulai ulang server. Atau, penghapusan dari cache perencanaan, biasanya berdasarkan perubahan skema atau statistik, atau tekanan memori.
Kompilasi ulang manual Gunakan sp_recompile. Gunakan sp_recompile. Anda dapat secara manual mengeluarkan rencana 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.
Kompilasi langsung 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.
Eksekusi prosedur tersimpan yang dikompilasi secara asli.

Pemanggilan prosedur tersimpan yang dikompilasi secara asli dijelaskan sebagai berikut:

  1. Pengguna mengeluarkan pernyataan EXECusp_myproc.

  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 simpan.

  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 "parameter sniffing," yang merupakan proses khusus dalam komputasi.

Pengambilan parameter tidak digunakan untuk mengompilasi prosedur tersimpan yang dikompilasi secara asli. Semua parameter pada prosedur tersimpan dianggap memiliki nilai TIDAK DIKETAHUI. 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 pelaksanaan kueri untuk prosedur tersimpan yang dikompilasi secara native dapat diperoleh menggunakan Rencana Eksekusi yang Diperkirakan 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. Operator gabungan memasok baris ke operator tertentu. Operator pilih akhirnya mengembalikan baris kepada 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
Pilih SELECT OrderID FROM dbo.[Order]
SISIPKAN INSERT dbo.Customer VALUES ('abc', 'def')
Pembaruan 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 Nested Loops adalah satu-satunya operator join yang didukung dalam prosedur tersimpan yang dikompilasi secara asli. Semua rencana yang berisi join akan menggunakan operator Nested Loops, bahkan jika rencana untuk kueri yang sama dijalankan sebagai Transact-SQL yang ditafsirkan berisi hash atau gabungan penggabungan.
Mengurutkan SELECT ContactName FROM dbo.Customer ORDER BY ContactName
Atas 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. Jumlahnya 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 Aliran 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 Kolom dan Penggabungan

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 rancangan, dan kemudian diperluas untuk menyertakan operator lain, seperti operator gabung. 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 terkluster pada Pelanggan telah memperkirakan 91; hasil sebenarnya 91.
  • Pemindaian indeks nonclustered pada CustomerID memperkirakan 830; aktualnya 830.
  • Operator Merge Join memperkirakan 815; aktual 830.

Perkiraan untuk pemindaian indeks adalah akurat. SQL Server mempertahankan jumlah baris untuk tabel berbasis disk. Perkiraan untuk pemindaian tabel dan indeks penuh selalu akurat. Perkiraan untuk penggabungan 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:

Statistik kolom dan gabungan.

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

  • Last updated on