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í