SQL плохая производительность плана выполнения хранимых процедур - сниффинг параметров

У меня есть хранимая процедура, которая принимает ввод даты, которая позже устанавливается на текущую дату, если не передается значение в:

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

У меня проблемы, из-за которых если @MyDate передается как NULL когда хранимая процедура впервые компилируется, производительность всегда ужасна для всех входных значений (NULL или иным образом), если передана дата / текущая дата, когда хранимая процедура скомпилирована, производительность подходит для всех входных значений (NULL или иным образом).

Что также сбивает с толку, так это то, что плохой план выполнения, который генерируется в нем, ужасен, даже если значение используемого @MyDate на самом деле NULL (и не установлен на CURRENT_TIMESTAMP по заявлению IF)

Я обнаружил, что отключение сниффинга параметров (путем подмены параметра) решает мою проблему:

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

Я знаю, что это как-то связано со сниффингом параметров, но все примеры, которые я видел, когда "сниффинг параметров пошёл плохо", были связаны с тем, что хранимая процедура была скомпилирована с переданным нерепрезентативным параметром, однако здесь я вижу, что план выполнения ужасен для всех мыслимых значений, которые SQL-сервер может считать параметром в момент выполнения инструкции - NULL, CURRENT_TIMESTAMP или иным образом.

Кто-нибудь понимал, почему это происходит?

Источник

2 ответа

Решение

В основном да - перехват параметров (на некоторых уровнях исправлений) SQL Server 2005 сильно нарушен. Я видел планы, которые фактически никогда не завершаются (в течение нескольких часов на небольшом наборе данных) даже для небольших (несколько тысяч строк) наборов данных, которые завершаются за считанные секунды после маскировки параметров. И это в тех случаях, когда параметр всегда был одинаковым номером. Я хотел бы добавить, что в то же время я имел дело с этим, я обнаружил много проблем с LEFT JOIN/NULL, не завершающими, и я заменил их на NOT IN или NOT EXISTS, и это решило план на что-то, что должно было завершиться. Снова, (очень плохой) вопрос плана выполнения. В то время, когда я имел дело с этим, администраторы баз данных не давали мне доступ к SHOWPLAN, и, поскольку я начал маскировать каждый параметр SP, у меня не было никаких дальнейших проблем с планом выполнения, где мне пришлось бы копаться в этом для незавершенности,

В SQL Server 2008 вы можете использовать OPTIMIZE FOR UNKNOWN,

Источник

Одним способом, которым я смог обойти эту проблему в (SQL Server 2005), вместо того, чтобы просто маскировать параметры путем повторного выделения локальных параметров, было добавление подсказок оптимизатора запросов.

Вот хороший пост в блоге, в котором больше об этом говорится: Анализ параметров в SqlServer 2005

Я использовал: OPTION (оптимизировать для (@p = '-1'))

Источник

Объявите параметр процедуры внутри процедуры и передайте внешний параметр внутреннему.. compile ..

Источник
Другие вопросы по тегам