Известная проблема: хранимая процедура SQL Server 2005 не завершена параметром

Ваш базовый СП с параметром по умолчанию:

ALTER PROCEDURE [usp_debug_fails] @DATA_DT_ID AS int = 20081130 WITH RECOMPILE AS BEGIN /* Usage: EXEC [usp_debug_fails] WITH RECOMPILE */ -- Stuff here that depends on DATA_DT_ID END 

Тот же SP с локальным, который жестко закодирован.

 ALTER PROCEDURE usp_debug_works] WITH RECOMPILE AS BEGIN /* Usage: EXEC [usp_debug_works] WITH RECOMPILE */ DECLARE @DATA_DT_ID AS int SET @DATA_DT_ID = 20081130 -- Stuff here that depends on DATA_DT_ID END 

Вы можете увидеть, где я помещал (избыточно, даже) WITH RECOMPILE опции, чтобы избежать нюхания параметра (это никогда не было необходимо в разработке, где эта вещь работала нормально)

Тот, который работает отлично, завершается через минуту или две, другой никогда не завершается – он сидит там часами.

Эта проблема никогда не возникала на сервере разработки (сборка 9.00.3282.00), производственный сервер построен 9.00.3068.00

Я удалил все виды кода из procs, чтобы попытаться перейти к минимальной версии, которая все еще обнаруживает проблему, и очень осторожно поддерживала обе версии SP той же, за исключением одного параметра.

У меня есть много других SP, которые принимают параметры, и они работают нормально. У меня также есть DROP ped и re CREATE ed SP.

Есть идеи?

И да, у меня есть DBA, смотрящий на него, и у меня нет SHOWPLAN или каких-либо полезных прав на производство, чтобы увидеть, есть ли блокировка (в случае, если ваш план приводит к эскалации блокировки, я думаю, опять же, единственное отличие – это параметр)

Я просмотрел всю информацию о сборке SQL Server и не вижу известной проблемы об этом, поэтому, пока я не выясню это, или DBA не выяснит это, я как бы застрял.

ОБНОВИТЬ

Это также не может завершиться (это на самом деле нормальная форма для этих SP), я просто по умолчанию включил, чтобы было легче переключаться туда и обратно во время тестирования)

 ALTER PROCEDURE [usp_debug_fails] @DATA_DT_ID AS int WITH RECOMPILE AS BEGIN /* Usage: EXEC [usp_debug_fails] 20081130 WITH RECOMPILE */ -- Stuff here that depends on DATA_DT_ID END 

однако это завершает (что может работать как обходное решение, хотя у меня есть около 25 из этих SP, чтобы изменить, все они имеют одну и ту же форму):

 ALTER PROCEDURE [usp_debug_fails] @DATA_DT_ID_in AS int WITH RECOMPILE AS BEGIN /* Usage: EXEC [usp_debug_fails] 20081130 WITH RECOMPILE */ DECLARE @DATA_DT_ID AS int SET @DATA_DT_ID = @DATA_DT_ID_in -- Stuff here that depends on DATA_DT_ID END 

Попробуйте маскировать входной параметр.

Я думаю, что повторная компиляция не работает из-за указанного значения по умолчанию ( EDIT : or параметр, отправленный при первом вызове), который был обнюхал во время компиляции. Таким образом, перекомпиляция не влияет.

Я видел огромную разницу между оценочными планами, просто изменив значение по умолчанию, скажем, от нуля до NULL, или не имея его.

 ALTER PROCEDURE [usp_debug_mightwork] @DATA_DT_ID AS int = 20081130 AS BEGIN DECLARE @IDATA_DT_ID AS int SET @IDATA_DT_ID = @DATA_DT_ID -- Stuff here that depends on IDATA_DT_ID END 

Я думаю, эта статья объясняет …

… значения параметров считываются во время компиляции или перекомпиляции …

РЕДАКТИРОВАТЬ:

Новая ссылка на планы и параметры запросов . По-прежнему параметр sniffing задает значение по умолчанию или нет.

Параметр WITH RECOMPILE, указанный в хранимой процедуре GetRecentSales выше, не устраняет ошибку оценки мощности

Вид связанной статьи о константах и ​​планах

Предотвратите обнюхивание параметров или вы будете тостами, когда статистика изменится. У меня 500+ sps, и все они начинаются с:

DECLARE @_Param1 ..., @_ParamN

--- prevent pameter sniffing
SELECT @_Param1 = @Param1, @_ParamN = @ParamN

Давайте будем гением компьютера.