SET SHOWPLAN_ALL (Transact-SQL)

Artikel
12/22/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Menyebabkan Microsoft SQL Server tidak menjalankan pernyataan Transact-SQL. Sebagai gantinya, SQL Server mengembalikan informasi terperinci tentang bagaimana pernyataan akan dijalankan (rencana kueri) dan memberikan perkiraan persyaratan sumber daya untuk pernyataan dan baris yang diharapkan (estimasi kardinalitas).

Konvensi sintaks transact-SQL

Sintaksis

  
SET SHOWPLAN_ALL { ON | OFF }

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Keterangan

Pengaturan SET SHOWPLAN_ALL diatur pada waktu eksekusi atau run time dan bukan pada waktu penguraian.

Ketika SET SHOWPLAN_ALL AKTIF, SQL Server mengembalikan informasi eksekusi untuk setiap pernyataan tanpa mengeksekusinya, dan pernyataan Transact-SQL tidak dijalankan. Setelah opsi ini diatur AKTIF, informasi tentang semua pernyataan Transact-SQL berikutnya dikembalikan hingga opsi diatur NONAKTIF. Misalnya, jika pernyataan CREATE TABLE dijalankan saat SET SHOWPLAN_ALL aktif, SQL Server mengembalikan pesan kesalahan dari pernyataan SELECT berikutnya yang melibatkan tabel yang sama, memberi tahu pengguna bahwa tabel yang ditentukan tidak ada. Oleh karena itu, referensi berikutnya ke tabel ini gagal. Ketika SET SHOWPLAN_ALL NONAKTIF, SQL Server menjalankan pernyataan tanpa menghasilkan laporan.

SET SHOWPLAN_ALL dimaksudkan untuk digunakan oleh aplikasi yang ditulis untuk menangani outputnya. Gunakan SET SHOWPLAN_TEXT untuk mengembalikan output yang dapat dibaca untuk aplikasi prompt perintah Microsoft Win32, seperti utilitas osql .

SET SHOWPLAN_TEXT dan SET SHOWPLAN_ALL tidak dapat ditentukan di dalam prosedur tersimpan; mereka harus menjadi satu-satunya pernyataan dalam batch.

SET SHOWPLAN_ALL mengembalikan informasi sebagai sekumpulan baris yang membentuk pohon hierarkis yang mewakili langkah-langkah yang diambil oleh prosesor kueri SQL Server saat menjalankan setiap pernyataan. Setiap pernyataan yang tercermin dalam output berisi satu baris dengan teks pernyataan, diikuti oleh beberapa baris dengan detail langkah-langkah eksekusi. Tabel memperlihatkan kolom yang dimuat output.

Nama kolom	Deskripsi
StmtText	Untuk baris yang bukan tipe PLAN_ROW, kolom ini berisi teks pernyataan Transact-SQL. Untuk baris tipe PLAN_ROW, kolom ini berisi deskripsi operasi. Kolom ini berisi operator fisik dan mungkin juga berisi operator logis secara opsional. Kolom ini juga dapat diikuti oleh deskripsi yang ditentukan oleh operator fisik. Untuk informasi selengkapnya, lihat Referensi Operator Logis dan Fisik Showplan.
StmtId	Jumlah pernyataan dalam batch saat ini.
NodeId	ID simpul dalam kueri saat ini.
Orangtua	ID node dari langkah induk.
PhysicalOp	Algoritma implementasi fisik untuk simpul. Untuk baris jenis PLAN_ROWS saja.
LogicalOp	Operator aljabar relasional yang diwakili node ini. Untuk baris jenis PLAN_ROWS saja.
Argumen	Menyediakan informasi tambahan tentang operasi yang sedang dilakukan. Konten kolom ini bergantung pada operator fisik.
DefinedValues	Berisi daftar nilai yang dipisahkan koma yang diperkenalkan oleh operator ini. Nilai-nilai ini mungkin ekspresi komputasi yang ada dalam kueri saat ini (misalnya, dalam daftar SELECT atau klausa WHERE), atau nilai internal yang diperkenalkan oleh prosesor kueri untuk memproses kueri ini. Nilai yang ditentukan ini kemudian dapat dirujuk di tempat lain dalam kueri ini. Untuk baris jenis PLAN_ROWS saja.
EstimateRows	Perkiraan jumlah baris output yang dihasilkan oleh operator ini. Untuk baris jenis PLAN_ROWS saja.
EstimateIO	Estimasi biaya I/O* untuk operator ini. Untuk baris jenis PLAN_ROWS saja.
EstimasiCPU	Estimasi biaya CPU* untuk operator ini. Untuk baris jenis PLAN_ROWS saja.
AvgRowSize	Estimasi ukuran baris rata-rata (dalam byte) dari baris yang diteruskan melalui operator ini.
TotalSubtreeCost	Estimasi biaya (kumulatif) * dari operasi ini dan semua operasi turunan.
Daftar Output	Berisi daftar kolom yang dipisahkan koma yang sedang diproyeksikan oleh operasi saat ini.
Warnings	Berisi daftar pesan peringatan yang dipisahkan koma yang berkaitan dengan operasi saat ini. Pesan peringatan dapat mencakup string "NO STATS:()" dengan daftar kolom. Pesan peringatan ini berarti bahwa pengoptimal kueri mencoba membuat keputusan berdasarkan statistik untuk kolom ini, tetapi tidak ada yang tersedia. Akibatnya, pengoptimal kueri harus membuat tebakan, yang mungkin telah menghasilkan pemilihan rencana kueri yang tidak efisien. Untuk informasi selengkapnya tentang membuat atau memperbarui statistik kolom (yang membantu pengoptimal kueri memilih rencana kueri yang lebih efisien), lihat MEMPERBARUI STATISTIK. Kolom ini dapat secara opsional menyertakan string "MISSING JOIN PREDICATE", yang berarti bahwa gabungan (melibatkan tabel) sedang berlangsung tanpa predikat gabungan. Secara tidak sengaja menghilangkan predikat gabungan dapat mengakibatkan kueri yang membutuhkan waktu lebih lama untuk dijalankan dari yang diharapkan, dan mengembalikan kumpulan hasil besar. Jika peringatan ini ada, verifikasi bahwa tidak adanya predikat gabungan disengaja.
Type	Jenis node. Untuk simpul induk dari setiap kueri, ini adalah jenis pernyataan Transact-SQL (misalnya, SELECT, INSERT, EXECUTE, dan sebagainya). Untuk subnode yang mewakili rencana eksekusi, jenisnya PLAN_ROW.
Paralel	0 = Operator tidak berjalan secara paralel. 1 = Operator berjalan secara paralel.
EstimateExecutions	Perkiraan berapa kali operator ini akan dijalankan saat menjalankan kueri saat ini.

*Unit biaya didasarkan pada pengukuran waktu internal, bukan waktu jam dinding. Mereka digunakan untuk menentukan biaya relatif rencana dibandingkan dengan rencana lain.

Izin

Untuk menggunakan SET SHOWPLAN_ALL, Anda harus memiliki izin yang memadai untuk menjalankan pernyataan tempat SET SHOWPLAN_ALL dijalankan, dan Anda harus memiliki izin SHOWPLAN untuk semua database yang berisi objek yang dirujuk.

Untuk pernyataan SELECT, INSERT, UPDATE, DELETE, EXEC stored_procedure, dan EXEC user_defined_function , untuk menghasilkan Showplan, pengguna harus:

Memiliki izin yang sesuai untuk menjalankan pernyataan Transact-SQL.
Memiliki izin SHOWPLAN pada semua database yang berisi objek yang dirujuk oleh pernyataan Transact-SQL, seperti tabel, tampilan, dan sebagainya.

Untuk semua pernyataan lainnya, seperti DDL, USE database_name, SET, DECLARE, dynamic SQL, dan sebagainya, hanya izin yang sesuai untuk menjalankan pernyataan Transact-SQL yang diperlukan.

Contoh

Dua pernyataan yang mengikuti menggunakan pengaturan SET SHOWPLAN_ALL untuk menunjukkan cara SQL Server menganalisis dan mengoptimalkan penggunaan indeks dalam kueri.

Kueri pertama menggunakan operator perbandingan Sama dengan (=) dalam klausa WHERE pada kolom terindeks. Ini menghasilkan nilai Pencarian Indeks Berkluster di kolom LogicalOp dan nama indeks di kolom Argumen .

Kueri kedua menggunakan operator LIKE dalam klausa WHERE. Ini memaksa SQL Server untuk menggunakan pemindaian indeks berkluster dan menemukan data yang memenuhi kondisi klausa WHERE. Ini menghasilkan nilai Pemindaian Indeks Berkluster di kolom LogicalOp dengan nama indeks di kolom Argumen , dan nilai Filter di kolom LogicalOp dengan kondisi klausa WHERE di kolom Argumen .

Nilai dalam kolom EstimateRows dan TotalSubtreeCost lebih kecil untuk kueri terindeks pertama, menunjukkan bahwa itu diproses jauh lebih cepat dan menggunakan lebih sedikit sumber daya daripada kueri yang tidak diindeks.

USE AdventureWorks2022;  
GO  
SET SHOWPLAN_ALL ON;  
GO  
-- First query.  
SELECT BusinessEntityID   
FROM HumanResources.Employee  
WHERE NationalIDNumber = '509647174';  
GO  
-- Second query.  
SELECT BusinessEntityID, EmergencyContactID   
FROM HumanResources.Employee  
WHERE EmergencyContactID LIKE '1%';  
GO  
SET SHOWPLAN_ALL OFF;  
GO

Lihat Juga

Pernyataan SET (Transact-SQL)
SET SHOWPLAN_TEXT (Transact-SQL)
SET SHOWPLAN_XML (Transact-SQL)