Bagaimana Saya Boleh Menyelesaikan Kemerosotan Prestasi Prosedur Tersimpan SQL Server yang Disebabkan oleh Menghidu Parameter?

SQL Server Stored Procedure Performance Degradasi disebabkan oleh Parameter Sniffing

Parameter sniffing ialah teknik pengoptimuman yang digunakan oleh SQL Server untuk meningkatkan prestasi pertanyaan dengan memilih pelan pelaksanaan berdasarkan nilai parameter pada masa prosedur tersimpan pertama kali disusun. Walau bagaimanapun, dalam keadaan tertentu, penghidu parameter boleh membawa kepada isu prestasi.

Pertimbangkan prosedur tersimpan berikut:

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
    -- Do Something using @MyDate

Apabila prosedur tersimpan ini mula-mula dilaksanakan dengan @MyDate sebagai NULL, pertanyaan pengoptimum menjana pelan pelaksanaan yang dioptimumkan untuk nilai parameter tertentu itu. Walau bagaimanapun, jika prosedur tersimpan kemudiannya dilaksanakan dengan nilai yang berbeza untuk @MyDate, pengoptimum pertanyaan mungkin tidak menyusun pelan pelaksanaan yang optimum, yang membawa kepada penurunan prestasi.

Isu ini dikenali sebagai "parameter sniffing gone bad" . Dalam contoh di atas, walaupun @MyDate sebenarnya NULL apabila digunakan dalam prosedur tersimpan, pelan pelaksanaan yang lemah yang dijana berdasarkan kompilasi awal kekal berkuat kuasa.

Untuk menyelesaikan isu ini, seseorang boleh melumpuhkan parameter menghidu dengan memalsukan parameter:

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    DECLARE @MyDate_Copy DATETIME
    SET @MyDate_Copy = @MyDate
    IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP
    -- Do Something using @MyDate_Copy

Pendekatan ini mencipta salinan parameter input dan menggunakan salinan itu untuk pertanyaan proses pengoptimuman, menghalang penghidu parameter berat sebelah.

Dalam SQL Server 2008 dan kemudian, penghidu parameter boleh dioptimumkan menggunakan pembayang OPTIMIZE FOR UNKNOWN:

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
WITH RECOMPILE, OPTIMIZE FOR UNKNOWN
AS
    -- Do Something using @MyDate

Petua ini mengarahkan pengoptimum pertanyaan untuk menjana pelan pelaksanaan berdasarkan nilai parameter yang tidak diketahui, menghapuskan risiko parameter isu prestasi yang berkaitan dengan menghidu.

Atas ialah kandungan terperinci Bagaimana Saya Boleh Menyelesaikan Kemerosotan Prestasi Prosedur Tersimpan SQL Server yang Disebabkan oleh Menghidu Parameter?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!