SQL Server: consulta rápida, mas lenta do procedimento

Uma consulta é executada rapidamente:

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

Custo da sub-tree: 0,502

Mas colocar o mesmo SQL em um procedimento armazenado é executado lentamente e com um plano de execução totalmente diferente

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

Custo da subtree: 19,2

Eu corri

 sp_recompile ViewOpener 

E ainda corre o mesmo (mal), e eu também mudei o procedimento armazenado para

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

E de volta, tentando realmente fazer uma recompilation.

Eu descartei e recriou o procedimento armazenado para gerá-lo para gerar um novo plano.

Eu tentei forçar recompilações e evitar o sniffing de parâmetro , usando uma variável chamariz:

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

Eu também tentei definir o procedimento armazenado com WITH RECOMPILE :

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

Assim, o plano nunca é armazenado em cache e tentei forçar uma recompilation ao executar:

 EXECUTE ViewOpener @SessionGUID WITH RECOMPILE 

O que não ajudou.

Eu tentei converter o procedimento para 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 

O que não ajudou.

A entidade ” Report_Opener ” é uma exibição que não está indexada. A exibição só faz referência a tabelas subjacentes. Nenhuma tabela contém colunas computadas, indexadas ou não.

Para o inferno eu tentei criar a vista com

 SET ANSI_NULLS ON SET QUOTED_IDENTIFER ON 

Isso não resolveu.

Como é que

  • a consulta é rápida
  • movendo a consulta para uma vista, e selecionando a partir da vista é rápido
  • selecionar a partir da exibição de um procedimento armazenado é 40x mais lento?

Eu tentei mover a definição da exibição diretamente para o procedimento armazenado (violando 3 regras de negócios e quebrando um encapsulamento importante), e isso faz com que seja apenas 6x mais lento.

Por que a versão do procedimento armazenado é tão lenta? O que possivelmente pode levar em conta o SQL Server executando SQL ad-hoc mais rápido do que um tipo diferente de SQL ad-hoc?

Eu realmente prefiro não

  • incorpore o SQL no código
  • mudar o código em tudo

     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) 

Mas o que pode levar o SQL Server sendo incapaz de executar tão rápido quanto o SQL Server executando uma consulta, se não o sniffing de parâmetro.


Minha próxima tentativa será ter StoredProcedureA chamada StoredProcedureB chamada StoredProcedureC chamada StoredProcedureD para consultar a exibição.

E, na falta disso, o procedimento armazenado chama um procedimento armazenado, chama um UDF, chama um UDF, chama um procedimento armazenado, chama um UDF para consultar a exibição.


Resumindo, o seguinte é executado rapidamente no controle de qualidade, mas lento quando colocado em um procedimento armazenado:

O 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) 

Planos de Execução

O bom plano:

  |--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) 

O plano ruim

  |--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) 

O ruim é ansioso para o spool de 6 milhões de linhas; o outro não é.

Nota: Esta não é uma questão sobre o ajuste de uma consulta. Eu tenho uma consulta que é executada rapidamente. Eu só quero que o SQL Server seja executado rapidamente a partir de um procedimento armazenado.

    Eu tive o mesmo problema que o cartaz original, mas a resposta citada não resolveu o problema para mim. A consulta ainda correu muito devagar de um procedimento armazenado.

    Eu encontrei outra resposta aqui “Parâmetro Sniffing” , Obrigado Omnibuzz. Se resume a usar “variables ​​locais” em suas consultas de procedimento armazenado, mas leia o original para mais compreensão, é um ótimo escrever. por exemplo

    Maneira lenta:

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

    Via 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 isso ajude alguém, fazendo isso reduziu meu tempo de execução de 5 minutos para cerca de 6-7 segundos.

    Eu encontrei o problema, aqui está o script das versões lenta e rápida do procedimento armazenado:

    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 

    Se você não viu a diferença, não culpo você. A diferença não está no procedimento armazenado. A diferença que transforma uma rápida consulta de custo 0,5 em uma que faz um spool ansioso de 6 milhões de linhas:

    Lento: SET ANSI_NULLS OFF

    Rápido: SET ANSI_NULLS ON


    Essa resposta também poderia ser feita para fazer sentido, já que a visão tem uma cláusula de junit que diz:

     (table.column IS NOT NULL) 

    Então, há alguns NULL envolvidos.


    A explicação é mais provada retornando ao Analizer de consulta e executando

     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 

    E a consulta é lenta.


    Portanto, o problema não é porque a consulta está sendo executada a partir de um procedimento armazenado. O problema é que a opção padrão de conexão do Enterprise Manager é ANSI_NULLS off , em vez de ANSI_NULLS on , que é o padrão do QA.

    A Microsoft reconhece esse fato em KB296769 ( erro : não é possível usar o SQL Enterprise Manager para criar stored procedures contendo objects de servidor vinculados). A solução alternativa é include a opção ANSI_NULLS na checkbox de diálogo do procedimento armazenado:

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

    Faça isso para o seu database. Eu tenho o mesmo problema – funciona bem em um database, mas quando eu copio esse database para outro usando o SSIS Import (não a restauração usual), esse problema acontece com a maioria dos meus stored procedures. Então, depois de googlar um pouco mais, eu encontrei o blog do Pinal Dave (que aliás, eu encontrei a maior parte do seu post e me ajudou muito, então, obrigado Pinal Dave) .

    Eu executo a consulta abaixo no meu database e ele corrigiu meu problema:

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

    Espero que isto ajude. Apenas passando a ajuda de outras pessoas que me ajudaram.

    Eu estava enfrentando o mesmo problema e este post foi muito útil para mim, mas nenhuma das respostas postadas resolveu o meu problema específico. Eu queria postar a solução que funcionou para mim na esperança de que possa ajudar alguém.

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

    No final da sua consulta, adicione OPTION (OPTIMIZE FOR (@ now UNKNOWN))

    Desta vez você encontrou seu problema. Se da próxima vez você tiver menos sorte e não conseguir descobrir, você pode usar o plano de congelamento e parar de se preocupar com o plano de execução incorreto.

    Eu estava passando por esse problema. Minha consulta parecia algo como:

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

    Meu procedimento armazenado foi definido como:

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

    Eu mudei o tipo de dados para datetime e voila! Passou de 30 minutos a 1 minuto!

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

    Você tentou reconstruir as statistics e / ou os índices na tabela Report_Opener. Todas as recomplicações do SP não valerão nada se as statistics ainda mostrarem dados de quando o database foi primeiro inauguado.

    A consulta inicial funciona rapidamente porque o otimizador pode ver que o parâmetro nunca será nulo. No caso do SP, o otimizador não pode ter certeza de que o parâmetro nunca será nulo.

    Embora eu geralmente seja contra (embora neste caso pareça que você tem uma razão genuína), você tentou fornecer alguma sugestão de consulta sobre a versão do SP da consulta? Se o SQL Server estiver preparando um plano de execução diferente nessas duas instâncias, você poderá usar uma dica para informar qual índice usar, para que o plano corresponda ao primeiro?

    Para alguns exemplos, você pode ir aqui .

    EDIT: Se você pode postar seu plano de consulta aqui, talvez possamos identificar alguma diferença entre os planos que está dizendo.

    SEGUNDO: Atualizado o link para ser específico do SQL-2000. Você terá que percorrer um caminho, mas há um segundo chamado “Table Hints” que é o que você está procurando.

    TERCEIRO: A consulta “Ruim” parece estar ignorando o [IX_Openers_SessionGUID] na tabela “Abridores” – qualquer chance de adicionar uma dica INDEX para forçá-lo a usar esse índice vai mudar as coisas?

    Isto é provavelmente improvável, mas dado que o seu comportamento observado é incomum, ele precisa ser verificado e ninguém mais o mencionou.

    Você tem certeza absoluta de que todos os objects são de propriedade do dbo e você não possui cópias falsas de sua propriedade ou de um usuário diferente?

    Apenas ocasionalmente, quando vejo um comportamento estranho, é porque na verdade havia duas cópias de um object e qual delas você obtém depende do que é especificado e de quem você está logado. Por exemplo, é perfeitamente possível ter duas cópias de uma visão ou procedimento com o mesmo nome, mas pertencentes a diferentes proprietários – uma situação que pode surgir quando você não está logado no database como dbo e esquecer de especificar dbo como proprietário do object quando você cria o object.

    Em nota que no texto você está executando algumas coisas sem especificar o proprietário, por exemplo

     sp_recompile ViewOpener
    

    se, por exemplo, houver duas cópias do viewOpener presentes, de propriedade de dbo e [algum outro usuário], então qual delas você realmente recompilará se não especificar depende das circunstâncias. Idem com a visão Report_Opener – se houver duas cópias (e elas podem diferir na especificação ou no plano de execução), então o que é usado depende das circunstâncias – e como você não especifica o proprietário é perfeitamente possível que sua consulta ad hoc possa usar uma e procedimento compilado pode usar o outro.

    Como eu disse, é provavelmente improvável, mas é possível e deve ser verificado, porque seus problemas podem ser que você está simplesmente procurando o bug no lugar errado.

    Isso pode soar bobo e parece óbvio a partir do nome SessionGUID, mas a coluna é um identificador exclusivo no Report_Opener? Se não, você pode querer tentar lançá-lo para o tipo correto e dar-lhe um tiro ou declarar sua variável para o tipo correto.

    O plano criado como parte do sproc pode funcionar de forma não intuitiva e fazer uma conversão interna em uma tabela grande.

    Eu tenho outra ideia. E se você criar esta function baseada em tabela:

     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 

    E, em seguida, selecionado a partir dele usando a seguinte instrução (mesmo colocando isso no seu SP):

     SELECT * FROM tbfSelectFromView(@SessionGUID) 

    Parece que o que está acontecendo (que todo mundo já comentou) é que o SQL Server apenas faz uma suposição em algum lugar errado, e talvez isso o force a corrigir a suposição. Eu odeio adicionar o passo extra, mas não tenho certeza do que mais pode estar causando isso.

    – Aqui está a solução:

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

    — É isso aí