none
sp_executesql RRS feed

  • Pergunta

  • Boa tarde Pessoal, estou com o seguinte problema.

    Quando executo uma query utilizando o SP_EXECUTESQL o plano de execução e as leituras logicas e físicas são umas.

    Table 'ProductColor'. Scan count 7, logical reads 508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'OrderItem'. Scan count 3, logical reads 900, physical reads 0, read-ahead reads 12, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Order'. Scan count 3, logical reads 1151, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'OrderItemSize'. Scan count 3, logical reads 27552, physical reads 0, read-ahead reads 27003, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'SKU'. Scan count 3, logical reads 4990, physical reads 1, read-ahead reads 1947, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Porem quando rodo a mesma query substituindo as variáveis e executando normalmente sem o sp_executesql o plano de execução as leituras físicas e logicas são mais baixas.

    Table 'ProductColor'. Scan count 3, logical reads 460, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'OrderItem'. Scan count 1, logical reads 819, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Order'. Scan count 1, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'OrderItemSize'. Scan count 375, logical reads 1236, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'SKU'. Scan count 1, logical reads 4888, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Alguém sabe me dizer por que isso acontece e se tem um jeito de melhorar a performance do sp_executesql.

    Att..

    segunda-feira, 23 de maio de 2016 18:21

Respostas

  • Andre,

    Sim, uma possibilidade seria utilizar a opção Optimize dentro do código da sua stored procedure, para que o plano de execução entenda que a variável deve ser considerada um elemento da sua procedure e não provocar a mudança na estrutura do plano de execução.

    Veja este exemplo que simila justamente o comportamento do Parameter Sniffing:

    -- Criando o Banco de Dados 
    Create Database ParameterSniffing
    Go
    
    -- Criando a TabelaDados --
    Create Table TabelaDados
    (Id Int Identity Primary Key,
     Descricao Char(100),
     Localal Char(6),
     DataCriacao DateTime Default Getdate(),
     Status char(2) default 'AC')
    Go
    
    -- Criando Índice na coluna Local para TabelaDados --
    Create Index IND_Local on TabelaDados(Local)
    Go
    
    -- Inserindo massa de dados --
    Insert Into TabelaDados (Name,Local) VALUES ('Test1','LocalA')
    GO 100000
    
    Insert Into TabelaDados (Name,Local) VALUES ('Test1','LocalB')
    GO 10
    
    -- Selecionando dados na TabelaDados com Local = 'LocalA' --
    Select * From TabelaDados
    Where Local = 'LocalA'
    Go
    
    -- Selecionando dados na TabelaDados com Local = 'LocalA' --
    Select * From TabelaDados
    Where Local = 'LocalB'
    Go
    
    -- Criando a Stored Procedure 
    Create Procedure ObterDadosPorLocal (@Local Char(6))
    As
    Begin
    
     Set NoCount ON
    
     Select * From TabelaDados
     Where Local = @Local
    End
    
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalA --
    ObterDadosPorLocal 'LocalA'
    Go
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalB --
    ObterDadosPorLocal 'LocalB'
    Go
    
    -- Obtendo informações sobre Plano de Execução de Stored Procedures --
    Select OBJECT_NAME(s.object_id) SP_Name,
           eqp.query_plan
    From sys.dm_exec_procedure_stats s CROSS APPLY sys.dm_exec_query_plan (s.plan_handle) eqp
    Where DB_NAME(database_id) = 'ParameterSniffing'
    
    -- Recompilando a Stored Procedure --
    SP_Recompile 'ObterDadosPorLocal'
    Go
    
    -- Alterando a Stored Procedure ObterDadosPorLocal adicionando a opção Recompile --
    Alter Procedure ObterDadosPorLocal (@Local as Char(6)) With Recompile
    As
    Begin
    
     Set NoCount On
    
     Select * From TabelaDados
     Where Local = @Local
    End
    
    -- Executando novamente a Stored Procedure ObterDadosPorLocal, descartando o Cache do Plano de Execução --
    Exec ObterDadosPorLocal @Local= N'LocalA' 
    Exec ObterDadosPorLocal @Local= N'LocalB' 
    Go
    
    -- Executando novamente a Stored Procedure ObterDadosPorLocal, recompilando e utilizando o do Plano de Execução, sem fazer o armazenamento --
    Exec [dbo].[ObterDadosPorLocal] @Local = N'LocalA' WITH RECOMPILE
    Go
    
    -- Alterando a Stored Procedure ObterDadosPorLocal removendo a opção Recompile e adicionando a Query Hint Optimize --
    Alter Procedure ObterDadosPorLocal (@Local CHAR(6))
    As
    Begin
    
     Set NoCount On
    
     Select * From TabelaDados
     Where Local = @Local
     OPTION (OPTIMIZE FOR (@Local = 'LocalA'))
    
    End
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalA --
    ObterDadosPorLocal 'LocalA'
    Go
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalB --
    ObterDadosPorLocal 'LocalB'
    Go
    


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marcado como Resposta Andre Modena terça-feira, 24 de maio de 2016 18:25
    terça-feira, 24 de maio de 2016 17:48

Todas as Respostas

  • Isso acontece mesmo após limpar o plano de execução criado para esta query?

    Pergunto isso pois em algum momento pode ter sido criado um plano de execução para a query considerando uma situação e depois houve alguma alteração ou a própria situação da tabela mudou e continua executando o mesmo plano de execução que já não é o mais adequado.

    Adicione a cláusula WITH RECOMPILE ao final do seu comando sp_executesql e verifique se houve alguma mudança.

    terça-feira, 24 de maio de 2016 02:51
  • Então Murilo, antes de executar as consultas, como era em ambiente de homologação eu usei o comando DBCC FREESYSTEMCACHE para limpar o cache dos planos de execução.

    Só que não adiantou, porem quando usei agora a opção with recompile ele usou o plano de execução 'Correto'. Só que quando tiro ele volta a executar o 'Errado'.

    Estranho.

    terça-feira, 24 de maio de 2016 11:56
  • Andre,

    Então, por acaso você esta utilizando uma variável no seu código? Se for talvez você esteja tendo problemas com parameter sniffing.

    Outro detalhe que pode esta acontecendo, as estatísticas de execução e armazenamento dos seus dados estão desatualizadas, utilize a system stored procedure sp_updatestats para realizar a atualização e tente executar sem o With Recompile.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Sugerido como Resposta Tiago_Neves quarta-feira, 25 de maio de 2016 17:38
    terça-feira, 24 de maio de 2016 15:05
  • Então Junior, as estatísticas estão atualizadas sim, tenho um plano de manutenção que faz esse processo em determinados dias.

    Essa query tem 5 variáveis. Pode ser parameter sniffing mesmo. Teria alguma solução para isso ? 


     


     
    terça-feira, 24 de maio de 2016 16:55
  • Andre,

    Sim, uma possibilidade seria utilizar a opção Optimize dentro do código da sua stored procedure, para que o plano de execução entenda que a variável deve ser considerada um elemento da sua procedure e não provocar a mudança na estrutura do plano de execução.

    Veja este exemplo que simila justamente o comportamento do Parameter Sniffing:

    -- Criando o Banco de Dados 
    Create Database ParameterSniffing
    Go
    
    -- Criando a TabelaDados --
    Create Table TabelaDados
    (Id Int Identity Primary Key,
     Descricao Char(100),
     Localal Char(6),
     DataCriacao DateTime Default Getdate(),
     Status char(2) default 'AC')
    Go
    
    -- Criando Índice na coluna Local para TabelaDados --
    Create Index IND_Local on TabelaDados(Local)
    Go
    
    -- Inserindo massa de dados --
    Insert Into TabelaDados (Name,Local) VALUES ('Test1','LocalA')
    GO 100000
    
    Insert Into TabelaDados (Name,Local) VALUES ('Test1','LocalB')
    GO 10
    
    -- Selecionando dados na TabelaDados com Local = 'LocalA' --
    Select * From TabelaDados
    Where Local = 'LocalA'
    Go
    
    -- Selecionando dados na TabelaDados com Local = 'LocalA' --
    Select * From TabelaDados
    Where Local = 'LocalB'
    Go
    
    -- Criando a Stored Procedure 
    Create Procedure ObterDadosPorLocal (@Local Char(6))
    As
    Begin
    
     Set NoCount ON
    
     Select * From TabelaDados
     Where Local = @Local
    End
    
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalA --
    ObterDadosPorLocal 'LocalA'
    Go
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalB --
    ObterDadosPorLocal 'LocalB'
    Go
    
    -- Obtendo informações sobre Plano de Execução de Stored Procedures --
    Select OBJECT_NAME(s.object_id) SP_Name,
           eqp.query_plan
    From sys.dm_exec_procedure_stats s CROSS APPLY sys.dm_exec_query_plan (s.plan_handle) eqp
    Where DB_NAME(database_id) = 'ParameterSniffing'
    
    -- Recompilando a Stored Procedure --
    SP_Recompile 'ObterDadosPorLocal'
    Go
    
    -- Alterando a Stored Procedure ObterDadosPorLocal adicionando a opção Recompile --
    Alter Procedure ObterDadosPorLocal (@Local as Char(6)) With Recompile
    As
    Begin
    
     Set NoCount On
    
     Select * From TabelaDados
     Where Local = @Local
    End
    
    -- Executando novamente a Stored Procedure ObterDadosPorLocal, descartando o Cache do Plano de Execução --
    Exec ObterDadosPorLocal @Local= N'LocalA' 
    Exec ObterDadosPorLocal @Local= N'LocalB' 
    Go
    
    -- Executando novamente a Stored Procedure ObterDadosPorLocal, recompilando e utilizando o do Plano de Execução, sem fazer o armazenamento --
    Exec [dbo].[ObterDadosPorLocal] @Local = N'LocalA' WITH RECOMPILE
    Go
    
    -- Alterando a Stored Procedure ObterDadosPorLocal removendo a opção Recompile e adicionando a Query Hint Optimize --
    Alter Procedure ObterDadosPorLocal (@Local CHAR(6))
    As
    Begin
    
     Set NoCount On
    
     Select * From TabelaDados
     Where Local = @Local
     OPTION (OPTIMIZE FOR (@Local = 'LocalA'))
    
    End
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalA --
    ObterDadosPorLocal 'LocalA'
    Go
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalB --
    ObterDadosPorLocal 'LocalB'
    Go
    


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marcado como Resposta Andre Modena terça-feira, 24 de maio de 2016 18:25
    terça-feira, 24 de maio de 2016 17:48
  • Obrigado Junior.

    Dessa forma funcionou.

    Abraço

    terça-feira, 24 de maio de 2016 18:25