Bekanntes Problem ?: SQL server 2005 gespeicherte Prozedur kann mit einem Parameter nicht abgeschlossen werden

Ihr Grund-SP mit einem Standardparameter:

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 

Das gleiche SP mit einem lokalen, das hartcodiert ist.

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

Sie können sehen, wo ich in die (redundanten, sogar) WITH RECOMPILE Optionen, um Parameter Sniffing zu vermeiden (dies war nie notwendig in der Entwicklung, wo dieses Ding funktionierte gut)

Derjenige, der funktioniert, vervollständigt sich gut in ein oder zwei Minuten, der andere schließt sich nicht – nur sitzt dort für Stunden.

Dieses Problem ist nie auf dem Entwicklungsserver passiert (Build 9.00.3282.00), der Produktionsserver ist gebaut 9.00.3068.00

Ich habe alle Arten von Code aus den Procs entfernt, um zu versuchen, sich auf die minimale Version, die noch das Problem zeigt, und waren sehr vorsichtig, um beide Versionen der SP das gleiche außer für diesen einen Parameter zu halten.

Ich habe viele andere SPs, die Parameter nehmen und sie laufen gut. Ich habe auch DROP ped und re CREATE ed die SPs.

Irgendwelche Ideen?

Und ja, ich habe einen DBA, der ihn ansieht und ich habe keinen SHOWPLAN oder irgendwelche nützlichen Rechte auf Produktion, um zu sehen, ob es blockiert (falls der Plan zu einer Sperren-Eskalation führt, denke ich – der einzige Unterschied ist der Parameter)

Ich habe alle SQL server-Build-Informationen überprüft und sehe kein bekanntes Problem darüber, also bis ich es herausfinden oder der DBA es ausstellt, bin ich irgendwie stecken.

AKTUALISIEREN

Dies ist auch nicht abgeschlossen (dies ist eigentlich die normale Form für diese SPs – ich habe nur eine Voreinstellung, um es einfacher zu wechseln hin und her während der Prüfung)

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

aber das schließt sich ab (was als Workaround funktionieren kann, obwohl ich etwa 25 dieser SPs habe, um zu modifizieren, welche alle die gleiche Form haben):

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

Versuchen Sie, den Eingabeparameter zu maskieren.

Ich denke, die Neukompilierung funktioniert nicht, weil der angegebene Default ( EDIT : Oder Parameter beim ersten Aufruf gesendet) zum timepunkt der Kompilierung schnüffelt. Also, neu kompilieren hat keine Wirkung.

Ich habe großen Unterschied zwischen geschätzten Plänen gesehen, indem ich einfach den Vorgang von sagen, null auf NULL, oder nicht mit einem.

 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 

Ich denke, dieser Artikel erklärt …

… Parameterwerte werden während der Kompilierung oder Neukompilierung schnüffelt …

BEARBEITEN:

Neuer Link zu Abfrageplänen und Parametern . Es ist immer noch ein Schnüffeln, ob eine Voreinstellung angegeben ist oder nicht.

Die Option WITH RECOMPILE, die auf der gespeicherten GetRecentSales-Prozedur angegeben ist, beseitigt den Kardinalitätsschätzerrors nicht

Art von verwandten Artikel über Konstanten und Pläne

Verhindern Sie das Parametern, oder wenn Sie sich ändern, wenn sich die Statistiken ändern. Ich habe 500+ sps und alle von ihnen beginnen mit:

DECLARE @_Param1 ..., @_ParamN

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