Usuário com melhor resposta
sp_executesql

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