SQL Server: Consulta rápida, pero lenta desde el procedimiento

Una consulta se ejecuta rápido:

DECLARE @SessionGUID uniqueidentifier SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908' SELECT * FROM Report_Opener WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank 

costo del subárbol: 0.502

Pero poner el mismo SQL en un procedimiento almacenado se ejecuta lentamente, y con un plan de ejecución totalmente diferente

 CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS SELECT * FROM Report_Opener WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank EXECUTE ViewOpener @SessionGUID 

Costo del subárbol: 19.2

Corrí

 sp_recompile ViewOpener 

Y sigue funcionando igual (mal), y también he cambiado el procedimiento almacenado para

 CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS SELECT *, 'recompile please' FROM Report_Opener WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank 

Y otra vez, tratando de engañarlo para que recompile.

He descartado y recreado el procedimiento almacenado para que genere un nuevo plan.

Intenté forzar comstackciones y evitar la detección de parámetros mediante el uso de una variable señuelo:

 CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS DECLARE @SessionGUIDbitch uniqueidentifier SET @SessionGUIDbitch = @SessionGUID SELECT * FROM Report_Opener WHERE SessionGUID = @SessionGUIDbitch ORDER BY CurrencyTypeOrder, Rank 

También intenté definir el procedimiento almacenado WITH RECOMPILE :

 CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier WITH RECOMPILE AS SELECT * FROM Report_Opener WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank 

De modo que su plan nunca se almacena en caché, y he intentado forzar una recomstackción en la ejecución:

 EXECUTE ViewOpener @SessionGUID WITH RECOMPILE 

Lo cual no ayudó.

Intenté convertir el procedimiento a SQL dynamic:

 CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier WITH RECOMPILE AS DECLARE @SQLString NVARCHAR(500) SET @SQLString = N'SELECT * FROM Report_OpenerTest WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank' EXECUTE sp_executesql @SQLString, N'@SessionGUID uniqueidentifier', @SessionGUID 

Lo cual no ayudó.

La entidad ” Report_Opener ” es una vista, que no está indexada. La vista solo hace referencia a tablas subyacentes. Ninguna tabla contiene columnas calculadas, indexadas o de otro modo.

Por el placer de hacerlo intenté crear la vista con

 SET ANSI_NULLS ON SET QUOTED_IDENTIFER ON 

Eso no lo solucionó.

Cómo es que

  • la consulta es rapida
  • mover la consulta a una vista y seleccionar desde la vista es rápido
  • seleccionar desde la vista desde un procedimiento almacenado es 40 veces más lento?

Traté de mover la definición de la vista directamente al procedimiento almacenado (incumpliendo 3 reglas de negocio y rompiendo una importante encapsulación), y eso hace que sea aproximadamente 6 veces más lenta.

¿Por qué la versión del procedimiento almacenado es tan lenta? ¿Qué puede posiblemente explicar que SQL Server ejecute SQL ad-hoc más rápido que un tipo diferente de SQL ad-hoc?

Realmente preferiría no

  • incrustar el código SQL en
  • cambiar el código en absoluto

     Microsoft SQL Server 2000 - 8.00.2050 (Intel X86) Mar 7 2008 21:29:56 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

Pero, ¿qué puede explicar que SQL Server no pueda ejecutarse tan rápido como SQL Sever ejecutando una consulta, si no es parámetro sniffing?


Mi próximo bash será tener StoredProcedureA llamada StoredProcedureB llamada StoredProcedureC llamada StoredProcedureD para consultar la vista.

Y en su defecto, haga que el procedimiento almacenado llame a un procedimiento almacenado, llame a un UDF, llame a un UDF, llame a un procedimiento almacenado, llame a un UDF para consultar la vista.


En resumen, lo siguiente se ejecuta rápidamente desde QA, pero lento cuando se lo coloca en un procedimiento almacenado:

El original:

 --Runs fine outside of a stored procedure SELECT * FROM Report_OpenerTest WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank 

sp_executesql :

 --Runs fine outside of a stored procedure DECLARE @SQLString NVARCHAR(500) SET @SQLString = N'SELECT * FROM Report_OpenerTest WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank' EXECUTE sp_executesql @SQLString, N'@SessionGUID uniqueidentifier', @SessionGUID 

EXEC(@sql) :

 --Runs fine outside of a stored procedure DECLARE @sql NVARCHAR(500) SET @sql = N'SELECT * FROM Report_OpenerTest WHERE SessionGUID = '''+CAST(@SessionGUID AS varchar(50))+''' ORDER BY CurrencyTypeOrder, Rank' EXEC(@sql) 

Planes de ejecución

El buen plan

  |--Sort(ORDER BY:([Expr1020] ASC, [Currencies].[Rank] ASC)) |--Compute Scalar(DEFINE:([Expr1020]=If ([Currencies].[CurrencyType]='ctCanadianCash') then 1 else If ([Currencies].[CurrencyType]='ctMiscellaneous') then 2 else If ([Currencies].[CurrencyType]='ctTokens') then 3 else If ([Currencies].[CurrencyType] |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Openers].[OpenerGUID])) |--Filter(WHERE:((([Currencies].[IsActive]0 AND [Currencies].[OnOpener]0) AND ((((((([Currencies].[CurrencyType]='ctUSCoin' OR [Currencies].[CurrencyType]='ctMiscellaneousUS') OR [Currencies].[CurrencyType]='ctUSCash') OR [Currencies]. | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Currencies].[CurrencyGUID], [Openers].[OpenerGUID]) WITH PREFETCH) | |--Nested Loops(Left Outer Join) | | |--Bookmark Lookup(BOOKMARK:([Bmk1016]), OBJECT:([GrobManagementSystemLive].[dbo].[Windows])) | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Openers].[WindowGUID])) | | | |--Bookmark Lookup(BOOKMARK:([Bmk1014]), OBJECT:([GrobManagementSystemLive].[dbo].[Openers])) | | | | |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_SessionGUID]), SEEK:([Openers].[SessionGUID]=[@SessionGUID]) ORDERED FORWARD) | | | |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Windows].[IX_Windows]), SEEK:([Windows].[WindowGUID]=[Openers].[WindowGUID]) ORDERED FORWARD) | | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType])) | |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID] AND [OpenerDetails].[CurrencyGUID]=[Currenc |--Hash Match(Cache, HASH:([Openers].[OpenerGUID]), RESIDUAL:([Openers].[OpenerGUID]=[Openers].[OpenerGUID])) |--Stream Aggregate(DEFINE:([Expr1006]=SUM(If (((([Currencies].[CurrencyType]='ctMiscellaneous' OR [Currencies].[CurrencyType]='ctTokens') OR [Currencies].[CurrencyType]='ctChips') OR [Currencies].[CurrencyType]='ctCanadianCoin') OR [ |--Nested Loops(Inner Join, OUTER REFERENCES:([OpenerDetails].[CurrencyGUID]) WITH PREFETCH) |--Nested Loops(Inner Join) | |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_OneOpenerPerSession]), SEEK:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD) | |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD) |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[PK_Currencies_CurrencyGUID]), SEEK:([Currencies].[CurrencyGUID]=[OpenerDetails].[CurrencyGUID]) ORDERED FORWARD) 

El mal plan

  |--Sort(ORDER BY:([Expr1020] ASC, [Currencies].[Rank] ASC)) |--Compute Scalar(DEFINE:([Expr1020]=If ([Currencies].[CurrencyType]='ctCanadianCash') then 1 else If ([Currencies].[CurrencyType]='ctMiscellaneous') then 2 else If ([Currencies].[CurrencyType]='ctTokens') then 3 else If ([Currencies].[Currency |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Openers].[OpenerGUID])) |--Filter(WHERE:((([Currencies].[IsActive]0 AND [Currencies].[OnOpener]0) AND ((((((([Currencies].[CurrencyType]='ctUSCoin' OR [Currencies].[CurrencyType]='ctMiscellaneousUS') OR [Currencies].[CurrencyType]='ctUSCash') OR [Currenc | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Currencies].[CurrencyGUID], [Openers].[OpenerGUID]) WITH PREFETCH) | |--Filter(WHERE:([Openers].[SessionGUID]=[@SessionGUID])) | | |--Concatenation | | |--Nested Loops(Left Outer Join) | | | |--Table Spool | | | | |--Hash Match(Inner Join, HASH:([Windows].[WindowGUID])=([Openers].[WindowGUID]), RESIDUAL:([Windows].[WindowGUID]=[Openers].[WindowGUID])) | | | | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Windows].[IX_Windows_CageGUID])) | | | | |--Table Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Openers])) | | | |--Table Spool | | | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType])) | | |--Compute Scalar(DEFINE:([Openers].[OpenerGUID]=NULL, [Openers].[SessionGUID]=NULL, [Windows].[UseChipDenominations]=NULL)) | | |--Nested Loops(Left Anti Semi Join) | | |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType])) | | |--Row Count Spool | | |--Table Spool | |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID] AND [OpenerDetails].[CurrencyGUID]=[Cu |--Hash Match(Cache, HASH:([Openers].[OpenerGUID]), RESIDUAL:([Openers].[OpenerGUID]=[Openers].[OpenerGUID])) |--Stream Aggregate(DEFINE:([Expr1006]=SUM([partialagg1034]), [Expr1007]=SUM([partialagg1035]), [Expr1008]=SUM([partialagg1036]), [Expr1009]=SUM([partialagg1037]), [Expr1010]=SUM([partialagg1038]), [Expr1011]=SUM([partialagg1039] |--Nested Loops(Inner Join) |--Stream Aggregate(DEFINE:([partialagg1034]=SUM(If (((([Currencies].[CurrencyType]='ctMiscellaneous' OR [Currencies].[CurrencyType]='ctTokens') OR [Currencies].[CurrencyType]='ctChips') OR [Currencies].[CurrencyType]=' | |--Nested Loops(Inner Join, OUTER REFERENCES:([OpenerDetails].[CurrencyGUID]) WITH PREFETCH) | |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD) | |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[PK_Currencies_CurrencyGUID]), SEEK:([Currencies].[CurrencyGUID]=[OpenerDetails].[CurrencyGUID]) ORDERED FORWARD) |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_OneOpenerPerSession]), SEEK:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD) 

El malo está ansioso por enrollar 6 millones de filas; el otro no lo es

Nota: esta no es una pregunta sobre cómo ajustar una consulta. Tengo una consulta que funciona a la velocidad del rayo. Solo quiero que SQL Server se ejecute rápidamente desde un procedimiento almacenado.

Tuve el mismo problema que el póster original, pero la respuesta no me solucionó el problema. La consulta todavía se ejecutó realmente lento a partir de un procedimiento almacenado.

Encontré otra respuesta aquí “Parameter Sniffing” , Gracias Omnibuzz. Se reduce a usar “Variables locales” en las consultas de procedimientos almacenados, pero lee el original para obtener más información, es una excelente redacción. p.ej

Camino lento

 CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20)) AS BEGIN SELECT * FROM orders WHERE customerid = @CustID END 

Manera rápida:

 CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20)) AS BEGIN DECLARE @LocCustID varchar(20) SET @LocCustID = @CustID SELECT * FROM orders WHERE customerid = @LocCustID END 

Espero que esto ayude a otra persona, y esto reduce mi tiempo de ejecución de más de 5 minutos a aproximadamente 6-7 segundos.

Encontré el problema, aquí está la secuencia de comandos de las versiones lentas y rápidas del procedimiento almacenado:

dbo.ViewOpener__RenamedForCruachan__Slow.PRC

 SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Slow @SessionGUID uniqueidentifier AS SELECT * FROM Report_Opener_RenamedForCruachan WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO 

dbo.ViewOpener__RenamedForCruachan__Fast.PRC

 SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Fast @SessionGUID uniqueidentifier AS SELECT * FROM Report_Opener_RenamedForCruachan WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO 

Si no viste la diferencia, no te culpo. La diferencia no está en absoluto en el procedimiento almacenado. La diferencia que convierte una consulta rápida de costo 0.5 en una que hace un ansioso spool de 6 millones de filas:

Lento: SET ANSI_NULLS OFF

Rápido: SET ANSI_NULLS ON


Esta respuesta también podría tener sentido, ya que la vista tiene una cláusula de unión que dice:

 (table.column IS NOT NULL) 

Entonces hay algunos NULL involucrados.


La explicación se prueba aún más volviendo a Query Analizer, y ejecutándose

 SET ANSI_NULLS OFF 

.

 DECLARE @SessionGUID uniqueidentifier SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908' 

.

 SELECT * FROM Report_Opener_RenamedForCruachan WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank 

Y la consulta es lenta.


Entonces, el problema no es porque la consulta se ejecuta desde un procedimiento almacenado. El problema es que la opción predeterminada de conexión de Enterprise Manager es ANSI_NULLS off , en lugar de ANSI_NULLS on , que es la predeterminada de QA.

Microsoft reconoce este hecho en KB296769 (ERROR: No se puede usar el Administrador corporativo de SQL para crear procedimientos almacenados que contengan objetos de servidor vinculados). La solución alternativa es incluir la opción ANSI_NULLS en el diálogo de procedimiento almacenado:

 Set ANSI_NULLS ON Go Create Proc spXXXX as .... 

Haz esto para tu base de datos. Tengo el mismo problema: funciona bien en una base de datos, pero cuando copio esta base de datos a otra usando la importación de SSIS (no la restauración habitual), este problema ocurre con la mayoría de mis procedimientos almacenados. Así que después de buscar en Google un poco más, encontré el blog de Pinal Dave (que por cierto, encontré la mayor parte de su publicación y me ayudó mucho, así que gracias Pinal Dave) .

Ejecuto la consulta a continuación en mi base de datos y corrigió mi problema:

 EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO EXEC sp_updatestats GO 

Espero que esto ayude. Solo pasando la ayuda de otros que me ayudaron.

Estaba enfrentando el mismo problema y esta publicación fue muy útil para mí, pero ninguna de las respuestas publicadas resolvió mi problema específico. Quería publicar la solución que funcionó para mí con la esperanza de que pueda ayudar a otra persona.

https://stackoverflow.com/a/24016676/814299

Al final de su consulta, agregue OPCIÓN (OPTIMIZAR POR (@now UNKNOWN))

Esta vez encontraste tu problema. Si la próxima vez tiene menos suerte y no puede resolverlo, puede usar el congelamiento del plan y dejar de preocuparse por un plan de ejecución incorrecto.

Estaba experimentando este problema. Mi consulta se veía algo así como:

 select a, b, c from sometable where date > '20140101' 

Mi procedimiento almacenado se definió como:

 create procedure my_procedure (@dtFrom date) as select a, b, c from sometable where date > @dtFrom 

Cambié el tipo de datos a datetime y voila! ¡Pasó de 30 minutos a 1 minuto!

 create procedure my_procedure (@dtFrom datetime) as select a, b, c from sometable where date > @dtFrom 

¿Ha intentado reconstruir las estadísticas y / o los índices en la tabla Report_Opener? Todas las recompliaciones del SP no valdrán nada si las estadísticas aún muestran datos de cuando se inauguró por primera vez la base de datos.

La consulta inicial en sí misma funciona rápidamente porque el optimizador puede ver que el parámetro nunca será nulo. En el caso del SP, el optimizador no puede estar seguro de que el parámetro nunca sea nulo.

Aunque normalmente estoy en contra de esto (aunque en este caso parece que tienes un motivo genuino), ¿has intentado proporcionar sugerencias de consulta sobre la versión SP de la consulta? Si SQL Server está preparando un plan de ejecución diferente en esas dos instancias, ¿puede usar una pista para decirle qué índice usar, para que el plan coincida con el primero?

Para algunos ejemplos, puedes ir aquí .

EDITAR: Si puede publicar su plan de consulta aquí, tal vez podamos identificar alguna diferencia entre los planes que está diciendo.

SEGUNDO: actualizó el enlace para que sea específico de SQL-2000. Tendrás que desplazarte un poco hacia abajo, pero hay un segundo titulado “Table Hints” que es lo que estás buscando.

TERCERO: La consulta “Malo” parece estar ignorando el [IX_Openers_SessionGUID] en la tabla “Openers” – ¿alguna posibilidad de agregar una sugerencia INDEX para forzarla a usar ese índice cambiará las cosas?

Esto es probablemente poco probable, pero dado que su comportamiento observado es inusual, debe verificarse y nadie más lo ha mencionado.

¿Estás absolutamente seguro de que todos los objetos son propiedad de dbo y no tienes copias fraudulentas propiedad de ti o de otro usuario presente?

Solo ocasionalmente, cuando he visto un comportamiento extraño, es porque en realidad había dos copias de un objeto y la que recibes depende de lo que se especifica y de con quién has iniciado sesión. Por ejemplo, es perfectamente posible tener dos copias de una vista o procedimiento con el mismo nombre pero propiedad de diferentes propietarios, una situación que puede surgir cuando no está conectado a la base de datos como dbo y olvida especificar dbo como propietario del objeto cuando tu creas el objeto

Tenga en cuenta que en el texto está ejecutando algunas cosas sin especificar el propietario, por ejemplo

 sp_recompile ViewOpener

si, por ejemplo, hay dos copias de viewOpener presentes propiedad de dbo y [algún otro usuario], entonces cuál de las que realmente recomstackrá si no especifica depende de las circunstancias. Lo mismo con la vista Report_Opener: si hay dos copias (y pueden diferir en las especificaciones o en el plan de ejecución), entonces lo que se usa depende de las circunstancias, y como no se especifica el propietario, es perfectamente posible que su consulta adhoc pueda usar una y la procedimiento comstackdo podría usar usar el otro.

Como ya he dicho, probablemente sea poco probable, pero es posible y debería verificarse porque sus problemas podrían ser simplemente buscar el error en el lugar equivocado.

Esto puede sonar tonto y parece obvio por el nombre SessionGUID, pero ¿es la columna un identificador único en Report_Opener? Si no es así, es posible que desee intentar convertirlo al tipo correcto y darle una oportunidad o declarar su variable al tipo correcto.

El plan creado como parte de sproc puede funcionar intuitivamente y hacer un reparto interno en una mesa grande.

Tengo otra idea. ¿Qué sucede si crea esta función basada en tablas?

 CREATE FUNCTION tbfSelectFromView ( -- Add the parameters for the function here @SessionGUID UNIQUEIDENTIFIER ) RETURNS TABLE AS RETURN ( SELECT * FROM Report_Opener WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank ) GO 

Y luego se seleccionó usando la siguiente statement (incluso poniendo esto en su SP):

 SELECT * FROM tbfSelectFromView(@SessionGUID) 

Parece que lo que está sucediendo (que todo el mundo ya ha comentado) es que SQL Server simplemente hace una suposición en algún lugar que está mal, y tal vez esto lo forzará a corregir la suposición. Odio agregar el paso extra, pero no estoy seguro de qué más podría estar causando eso.

– Aquí está la solución:

 create procedure GetOrderForCustomers(@CustID varchar(20)) as begin select * from orders where customerid = ISNULL(@CustID, '') end 

— Eso es