SQL SERVER – QUERY HINT OPTION – OPTIMIZE FOR UNKNOWN

Querys ad-hoc:
Não parametrizadas, ou carentes de precisão em sua parametrização, geram um novo plano de execução para cada alteração de seu conjunto de condições, ou definição de parâmetros.
O que necessariamente não é de todo o mal. Afinal, baseado nas estatísticas, é possível que cada query possua um plano diferente, com operadores específicos e mais performáticos considerando o valor dos parâmetros de cada execução.

Ex.: Querys que geram planos de execução distintos:

select nome from Cidade where uf='RS' 
select nome from Cidade where uf='SC'

Desnecessário? Talvez.
Vamos à boa pratica de parametrizar com precisão a query. Utilizando uma stored procedure, dinamizamos a @uf em questão e, o plano de execução será compilado apenas na primeira chamada. O mesmo será reaproveitando enquanto existir no cache.

Ex.:

create procedure USP_ObterNomeDeCidadePorUF @uf char(2) 
as 
   select nome from Cidade where uf = @uf 
go
exec USP_ObterNomeDeCidadePorUF @uf='RS'
exec USP_ObterNomeDeCidadePorUF @uf='SC'

Ratificando: O plano de execução será compilado na primeira execução, e reaproveitado nas demais. Perfeito?! Em nosso exemplo, não.

Parameter Sniffing:
Para definir o plano mais performático de determinada query, o optimazer utiliza parameter sniffing para incluir o valor dos parâmetros na elaboração do plano de execução. Ou seja, o plano de execução de nossa stored procedure será definido considerando o valor do parâmetro @uf na primeira execução, que no exemplo será para @uf=’RS’. O mesmo plano será reaproveitado para a chamada subsequente, uf=’SC’. No cenário de exemplo, as querys ad-hoc geram planos de execução diferentes para ‘RS’ e ‘SC’. Demonstrando assim que, neste caso, é mais performático planos específicos. Em outras palavras, o plano para ‘RS’ não é o melhor para ‘SC’ e vice-versa.

OPTIMIZE FOR UNKNOWN:
Apelidado de “one size fits all”, pois o optimazer não considerará o valor dos parâmetros na compilação do plano de execução. Gera assim um plano de execução genérico. Em outras palavras, seguindo nosso exemplo, não será o melhor plano para @uf=’RS’, como também não será o melhor plano para @uf=’SC’.

Ex.:

create procedure USP_ObterNomeDeCidadePorUF @uf char(2) 
as 
   select nome from Cidade where uf = @uf option (optimize for unknown)
go
exec USP_ObterNomeDeCidadePorUF @uf='RS'
exec USP_ObterNomeDeCidadePorUF @uf='SC'

O que devemos considerar para apoiar nossa tomada de decisão:
– Quem representa o maior gargalo em nosso ambiente? Compilar e utilizar planos ad-hoc para cada chamada, utilizar o plano do cache para determinada parametrização, ou utilizar o plano do cache genérico?

Neste post da Kendra Little, “Optimize for… Mediocre?”, ela demonstra com excelentes exemplos e prints, sem esquecer da base parameter sniffing, o que descrevo acima.

Divirta-se!

Post:
http://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/

Kendra Little:
http://www.brentozar.com/archive/author/kendra-little/

Referências:
http://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s