SET SHOWPLAN_XML (Transact-SQL)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (khusus kumpulan SQL)

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

Konvensi sintaks transact-SQL

Sintaksis

SET SHOWPLAN_XML { 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_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 menjalankannya, 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. Ketika SET SHOWPLAN_XML NONAKTIF, SQL Server menjalankan pernyataan tanpa menghasilkan laporan.

SET SHOWPLAN_XML dimaksudkan untuk mengembalikan output sebagai nvarchar(maks) 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 query_plan kolom sys.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.

Perkiraan rencana eksekusi melalui SSMS dan SET SHOWPLAN_XML tersedia untuk kumpulan SQL khusus (sebelumnya SQL DW) dan kumpulan SQL khusus di Azure Synapse Analytics. Untuk mengambil rencana eksekusi aktual untuk kumpulan SQL khusus (sebelumnya SQL DW) dan kumpulan SQL khusus di Azure Synapse Analytics, ada perintah yang berbeda. Untuk informasi selengkapnya, lihat Memantau beban kerja kumpulan SQL khusus Azure Synapse Analytics Anda menggunakan DMV.

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 nilainya @@VERSION adalah 14. SQL Server 2019 nilai pertama dari @@VERSION adalah 15. SQL Server 2022 nilai pertama dari @@VERSION adalah 16.

Skema Showplan juga dapat ditemukan di Skema XML Microsoft SQL Server.

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 SELECTpernyataan , , INSERTUPDATE, 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 lain, seperti DDL, USE *database_name*, , SET, DECLARESQL dinamis, 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 dan EstimatedTotalSubtreeCost lebih kecil untuk kueri terindeks pertama, menunjukkan bahwa itu diproses EstimateRows jauh lebih cepat dan menggunakan lebih sedikit sumber daya daripada kueri yang tidak diindeks.

USE AdventureWorks2022;
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;

Langkah berikutnya