Memigrasikan Rencana Kueri

Dalam kebanyakan kasus, meningkatkan database ke versi terbaru SQL Server akan mengakibatkan peningkatan performa kueri. Namun, jika Anda memiliki kueri yang sangat penting dan telah disetel dengan cermat untuk performa, Anda mungkin ingin mempertahankan rencana kueri yang sudah ada untuk kueri-kueri ini sebelum meningkatkan versinya dengan membuat petunjuk rencana untuk setiap kueri. Jika setelah pemutakhiran, pengoptimum kueri memilih rencana yang kurang efisien untuk satu atau beberapa kueri, Anda dapat mengaktifkan panduan pelaksanaan dan memaksa pengoptimum kueri untuk menggunakan rencana sebelum pemutakhiran.

Untuk membuat panduan paket sebelum memutakhirkan, ikuti langkah-langkah berikut:

1. Rekam rencana saat ini untuk setiap kueri penting misi dengan menggunakan prosedur tersimpan sp_create_plan_guide dan tentukan rencana kueri dalam petunjuk kueri USE PLAN.

2. Verifikasi bahwa panduan rencana diterapkan ke kueri.

3. Tingkatkan database ke versi SQL Server yang lebih baru.

   Rencana disimpan dalam database yang ditingkatkan dalam panduan rencana dan digunakan sebagai cadangan jika terjadi penurunan kinerja rencana setelah database ditingkatkan.

   Kami menyarankan agar Anda tidak mengaktifkan panduan rencana setelah peningkatan, karena Anda mungkin melewatkan peluang untuk mendapatkan rencana yang lebih baik dalam rilis baru atau kompilasi ulang yang menguntungkan karena statistik yang telah diperbarui.

4. Jika paket yang kurang efisien dipilih setelah peningkatan, aktifkan semua atau sebagian dari panduan paket untuk menggantikan paket baru.

Contoh

Contoh berikut menunjukkan cara merekam rencana untuk kueri pra-peningkatan dengan membuat panduan rencana.

Langkah 1: Kumpulkan Rencana

Rencana query yang direkam dalam panduan rencana harus dalam format XML. Rencana kueri berformat XML dapat diproduksi dengan cara berikut:

• SET SHOWPLAN_XML

• SET STATISTIK XML

• Menjalankan kueri pada kolom query_plan dari fungsi manajemen dinamis sys.dm_exec_query_plan.

• Profiler SQL Server Showplan XML, Statistik Profil Showplan XML, dan XML Showplan Kelas Peristiwa untuk Kompilasi Kueri.

Contoh berikut mengumpulkan rencana kueri untuk pernyataan SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; dengan mengkueri tampilan manajemen dinamis.

USE AdventureWorks;  
GO  
SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%';  
GO  

Langkah 2: Buat Panduan Rencana untuk Memaksa Rencana

Menggunakan rencana kueri berformat XML (diperoleh melalui salah satu metode yang dijelaskan sebelumnya) dalam panduan rencana, salin dan tempel rencana kueri sebagai string literal di dalam petunjuk kueri USE PLAN yang ditentukan dalam klausul OPTION dari sp_create_plan_guide.

Dalam rencana XML itu sendiri, tanda kutip (') yang muncul dalam rencana harus diberi tanda kutip tambahan sebelum membuat panduan rencana. Misalnya, rencana yang berisi WHERE A.varchar = 'This is a string' harus di-escape dengan memodifikasi kode menjadi WHERE A.varchar = ''This is a string''.

Contoh berikut membuat panduan rencana untuk rencana kueri yang dikumpulkan di langkah 1 dan menyisipkan Xml Showplan untuk kueri dalam parameter @hints. Untuk singkatnya, hanya keluaran Showplan parsial yang disertakan dalam contoh.

EXECUTE sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',  
@type = N'SQL',  
@module_or_batch = NULL,  
@params = NULL,  
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan''''   
    Version=''''0.5'''' Build=''''9.00.1116''''>  
    <BatchSequence><Batch><Statements><StmtSimple>  
    ...  
    </StmtSimple></Statements></Batch>  
    </BatchSequence></ShowPlanXML>'')';  
GO  

Langkah 3: Verifikasi bahwa Panduan Rencana Diterapkan pada Kueri

Jalankan kueri lagi dan periksa rencana kueri yang dihasilkan. Anda seharusnya melihat bahwa rencana tersebut sesuai dengan yang Anda tentukan dalam panduan rencana.

Lihat Juga

sp_create_plan_guide (T-SQL)
Petunjuk Kueri (Transact-SQL)
Panduan Rencana