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

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

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

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

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

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

<code>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
</code>

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

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

 Justin17 июн. 2009 г., 17:09
Просто заметил, что :-)
 cjk17 июн. 2009 г., 16:38
Это интересно, но вы нигде не задаете вопрос здесь ...

Ответы на вопрос(2)

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

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

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

Решение Вопроса

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

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

Ваш ответ на вопрос