sql-server - trucos - ver consultas en ejecucion sql server

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

Question

Una consulta se ejecuta rápidamente:

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

He corrido

sp_recompile ViewOpener

Y todavía funciona igual (mal), y también he cambiado el procedimiento almacenado a

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

Y de nuevo, tratando de engañarlo realmente para recompilarlo.

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

He intentado forzar recompilaciones, y evitar el rastreo de parámetros , usando 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 he intentado 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

Para que el plan nunca se almacene en caché, y he intentado forzar una recompilación en la ejecución:

EXECUTE ViewOpener @SessionGUID WITH RECOMPILE

Lo que no ayudó.

He intentado convertir el procedimiento a SQL dinámico:

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 que 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 computadas, indexadas o de otro tipo.

Por el infierno de eso intenté crear la vista con

SET ANSI_NULLS ON
SET QUOTED_IDENTIFER ON

Eso no lo solucionó.

Cómo es que

• la consulta es rápida
• mover la consulta a una vista y seleccionar de la vista es rápido
• ¿Seleccionar de la vista de un procedimiento almacenado es 40 veces más lento?

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

¿Por qué la versión del procedimiento almacenado es tan lenta? ¿Qué puede 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 un rastreo de parámetros?

Mi próximo intento será que StoredProcedureA llame a StoredProcedureB llame a StoredProcedureC llame a StoredProcedureD para consultar la vista.

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

En resumen, lo siguiente se ejecuta rápidamente desde el control de calidad, pero es lento cuando se 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-uno está ansioso de poner en cola 6 millones de filas; el otro no lo es

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

Answer 1

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

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 será nulo.

Answer 2

- Aquí está la solución:

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

-- Eso es

Answer 3

Aunque normalmente estoy en contra (aunque en este caso parece que tienes una razón genuina), ¿has intentado proporcionar alguna sugerencia 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 sugerencia 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án diciendo.

SEGUNDO: Se actualizó el enlace para ser específico de SQL-2000. Tendrá que desplazarse hacia abajo en una dirección, pero hay un segundo titulado "Sugerencias de tabla" que es lo que está buscando.

TERCERO: La consulta "incorrecta" parece estar ignorando el [IX_Openers_SessionGUID] en la tabla "Abridores". ¿Alguna posibilidad de agregar una sugerencia INDEX para forzarla a usar ese índice cambiará las cosas?

Answer 4

Encontré el problema, aquí está el script 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 notaste la diferencia, no te culpo. La diferencia no está en el procedimiento almacenado en absoluto. La diferencia que convierte una consulta rápida de costo de 0.5 en una que hace una cola impaciente 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)

Así que hay algunos NULL involucrados.

La explicación se prueba aún más volviendo a Query Analizer y ejecutando

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 está ejecutando 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 KB296769 SQL para crear procedimientos almacenados que contengan objetos de servidor vinculados). La solución es incluir la opción ANSI_NULLS en el cuadro de diálogo del procedimiento almacenado:

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

Answer 5

Esta vez encontraste tu problema. Si la próxima vez que tenga menos suerte y no pueda resolverlo, puede usar la congelación de planes y dejar de preocuparse por un plan de ejecución incorrecto.

Answer 6

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

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

Mi procedimiento almacenado fue definido 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 ¡voilá! Fuimos de 30 minutos a 1 minuto!

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

Answer 7

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

El plan creado como parte del sproc puede funcionar de forma no intuitiva y hacer un lanzamiento interno en una mesa grande.

Answer 8

Haga esto para su base de datos. Tengo el mismo problema: funciona bien en una base de datos, pero cuando copio esta base de datos a otra utilizando SSIS Import (no la restauración habitual), este problema ocurre en la mayoría de los procedimientos almacenados. Entonces, 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 siguiente consulta en mi base de datos y corrigí 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.

Answer 9

Me enfrenté al mismo problema y esta publicación me ayudó mucho, 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 alguien más.

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

Al final de su consulta, agregue OPCIÓN (OPTIMIZAR PARA (@now DESCONOCIDO))

Answer 10

Probablemente esto sea improbable, pero dado que su comportamiento observado es inusual, debe comprobarse y nadie más lo ha mencionado.

¿Está absolutamente seguro de que todos los objetos son propiedad de dbo y no tiene una copia falsa que posea usted o un usuario diferente también presente?

Solo de vez en cuando, cuando veo un comportamiento extraño, es porque en realidad había dos copias de un objeto y el que obtienes depende de lo que se especifique y de quién inicias sesión. Por ejemplo, es perfectamente posible tener dos copias de una vista o procedimiento con el mismo nombre pero que pertenezcan a diferentes propietarios, una situación que puede surgir cuando no se registra en la base de datos como dbo y se olvida de especificar dbo como propietario del objeto cuando usted crea 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 la que realmente compile si no especifica depende de las circunstancias. Igual que con la vista Report_Opener, si hay dos copias (y pueden diferir en la especificación o el plan de ejecución), entonces lo que se usa depende de las circunstancias, y como no especifica el propietario, es perfectamente posible que su consulta adhoc pueda usar una y la El procedimiento compilado podría utilizar el otro.

Como dije, es probable que sea improbable, pero es posible y debe revisarse porque sus problemas pueden ser que simplemente está buscando el error en el lugar equivocado.

Answer 11

Tengo otra idea. ¿Qué pasa si creas 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 seleccione de él usando la siguiente declaración (incluso poniendo esto en su SP):

SELECT *
FROM tbfSelectFromView(@SessionGUID)

Parece que lo que está sucediendo (lo 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 obligará a corregir la suposición. Odio agregar el paso adicional, pero no estoy seguro de qué otra cosa podría estar causándolo.

Answer 12

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

Encontré otra respuesta aquí "Parámetro Sniffing" , Gracias Omnibuzz. Se reduce al uso de "Variables locales" en sus consultas de procedimientos almacenados, pero lea el original para comprenderlo mejor, es una excelente reseña. p.ej

Manera lenta:

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 alguien más, haciendo esto redujo mi tiempo de ejecución de más de 5 minutos a unos 6-7 segundos.