SET SHOWPLAN_XML (Transact-SQL)

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Menyebabkan SQL Server tidak menjalankan pernyataan Transact-SQL. Sebaliknya, SQL Server mengembalikan informasi terperinci tentang bagaimana pernyataan akan dijalankan dalam bentuk dokumen XML yang ditentukan dengan baik.

Ikon tautan topikKonvensi Sintaks Transact-SQL

Sintaks

SET SHOWPLAN_XML { ON | OFF }

Catatan

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

Keterangan

Pengaturan SET SHOWPLAN_XML diatur pada waktu eksekusi atau eksekusi dan bukan pada waktu penguraian.

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

SET SHOWPLAN_XML dimaksudkan untuk mengembalikan output sebagai nvarchar(max) untuk aplikasi seperti utilitas sqlcmd , di mana output XML kemudian digunakan oleh alat lain untuk menampilkan dan memproses informasi rencana kueri.

Catatan

Tampilan manajemen dinamis, sys.dm_exec_query_plan, mengembalikan informasi yang sama dengan SET SHOWPLAN XML dalam jenis data xml . Informasi ini dikembalikan dari kolom query_plansys.dm_exec_query_plan. Untuk informasi selengkapnya, lihat sys.dm_exec_query_plan (Transact-SQL).

SET SHOWPLAN_XML tidak dapat ditentukan di dalam prosedur tersimpan. Ini harus menjadi satu-satunya pernyataan dalam batch.

SET SHOWPLAN_XML mengembalikan informasi sebagai sekumpulan dokumen XML. Setiap batch setelah pernyataan SET SHOWPLAN_XML ON tercermin dalam output oleh satu dokumen. Setiap dokumen berisi teks pernyataan dalam batch, diikuti dengan detail langkah-langkah eksekusi. Dokumen menunjukkan perkiraan biaya, jumlah baris, indeks yang diakses, dan jenis operator yang dilakukan, urutan gabungan, dan informasi selengkapnya tentang rencana eksekusi.

Catatan

Jika Sertakan Rencana Eksekusi Aktual dipilih di SQL Server Management Studio, opsi SET ini tidak menghasilkan output Xml Showplan. Kosongkan tombol Sertakan Rencana Eksekusi Aktual sebelum menggunakan opsi SET ini.

Lokasi output SHOWPLAN

Dokumen yang berisi skema XML untuk output XML oleh SET SHOWPLAN_XML disalin selama penyiapan ke direktori lokal di komputer tempat Microsoft SQL Server diinstal. Dokumen dapat ditemukan pada drive yang berisi file penginstalan SQL Server, di jalur yang mirip dengan yang berikut ini:

  • \Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

Di jalur sebelumnya, simpul 130\ digunakan oleh SQL Server 2016. Angka 130 berasal dari simpul pertama dari nilai yang dikembalikan oleh SELECT @@VERSION, yaitu 13. Untuk SQL Server 2017 jalur akan menggunakan 140\, karena simpul pertama dari nilai @@VERSION adalah 14. Untuk SQL Server 2019, nilai pertama dari @@VERSION adalah 15.

Skema Showplan juga dapat ditemukan di situs Web ini.

Izin

Untuk menggunakan SET SHOWPLAN_XML, Anda harus memiliki izin yang memadai untuk menjalankan pernyataan tempat SET SHOWPLAN_XML 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_XML untuk memperlihatkan cara SQL Server menganalisis dan mengoptimalkan penggunaan indeks dalam kueri.

Kueri pertama menggunakan operator perbandingan Sama dengan (=) dalam klausa WHERE pada kolom terindeks. 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. Nilai dalam atribut Estimaterows dan EstimatedTotalSubtreeCost lebih kecil untuk kueri terindeks pertama, menunjukkan bahwa kueri diproses jauh lebih cepat dan menggunakan lebih sedikit sumber daya daripada kueri yang tidak diindeks.

USE AdventureWorks2012;
GO
SET SHOWPLAN_XML ON;
GO
-- First query.
SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
-- Second query.
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO
SET SHOWPLAN_XML OFF;

Lihat juga

Pernyataan SET (Transact-SQL)