Boa tarde minha comunidade, salve, salve meu mestre SQL e porta bandeira Server……
É isso pessoal, quarta – feira de cinzas para muitos no brasil, mas graças a deus deste mal ou depressão eu não vou morrer, estou retornando em mais um post da sessão Dica do Mês, e se por acaso você não tem acessado o meu blog nos últimos meses não tem problema, uso um dos links abaixo para acessar as três últimos dicas do mês publicadas:
Hoje vou destacar mais uma melhoria adicionada recentemente ao Microsoft SQL Server 2016 a partir da instalação do Service Pack 1, sendo este sem dúvida nenhuma o maior conjunto de melhorias e inovações já adicionadas ao SQL Server desde suas primeiras versões.
Caso você ainda não fez download deste service pack, não perca tempo, acesse o link abaixo e realize o download o quanto antes: https://pedrogalvaojunior.wordpress.com/2016/11/19/microsoft-disponibiliza-service-pack-1-para-o-microsoft-sql-server-2016/
Dando continuidade, hoje vou apresentar o novo Query Hint adicionado após a instalação do SP1 chamado de USE HINT, quando encontrei informações sobre este novo recursos acabei ficando um pouco confuso e sem entender em qual momento ele poderia ser útil, mas após um busca mais detalhada pude ter a certeza que valia a pena compartilhar um pouco sobre esta novidade com vocês.
Se você ainda não ouvir falar sobre uso de query hints ou dicas de consulta existentes no SQL Server, isso assunto não é nada novo muito pelo contrário trata-se de uma das práticas mais comuns utilizadas por profissionais de bancos de dados e desenvolvedores com objetivo de otimizar a execução de uma ou mais transações.
Agora no SP1 do 2016 o time de engenheiros do SQL Server adicionou este novo argumento que nos permite dentre suas finalidades fazer uso de alguns dos mais específicos query hints sem exigir elevação no nível de permissão, declaração de trace flags ou até mesmo que o usuário que esteja fazendo uso deste hint pertença ao server role sysadmin.
Bom se você quiser saber um pouco mais sobre este novo argumento, continue lendo este post….
Introdução
Cenários no qual SQL Server deve ser sugerido mudança de comportamento para o query optimizer são bastante comuns de serem encontrados, tradicionalmente o uso de query hints apresentam um papel fundamental neste contexto independente de qual sinalizador você esteja utilizando, sendo este reconhecido como um documentado ou não-documentados.
Entretanto, quando estes sinalizadores de rastreamento são definidos globalmente, eles podem ter um efeito adverso sobre outras cargas de trabalho. Além disso, o uso por sessão não é prático com aplicativos, bem como, por consulta com QUERYTRACEON, sendo que esta opção requer associação na função de servidor fixa sysadmin. Embora você pode contornar esse comportamento usando um guia de plano ou um procedimento armazenado, credenciais elevadas são ainda necessárias.
Estes sinalizadores de rastreamento são conhecidos como trace flags, usados para configurar temporariamente características de servidor específico ou para desligar um determinado comportamento, podem ser difíceis de gerenciar e compreender sua forma de uso ou impactos.
A partir do Microsoft SQL Server 2016 SP1 temos a capacidade de fazer uso do novo query hint argument USE HINT diretamente em nossas querys ou transações de forma simples, rápida e segura, contornando qualquer necessidade de elevação permissional ou mudança no contexto de execução.
Dentre os diversos query hints existentes no SQL Server, o USE HINT nos permite fazer uso dos seguintes:
| Opção | Sinalizador de rastreamento equivalente | Descrição |
| FORCE_LEGACY_CARDINALITY_ESTIMATION | TF 9481 | Permite que você defina o modelo de estimativa de cardinalidade do otimizador de consulta para o SQL Server 2012 e versões anteriores, independentemente do nível de compatibilidade do banco de dados. |
| ENABLE_QUERY_OPTIMIZER_HOTFIXES | TF 4199 | Controles de consulta alterações otimizador lançadas em Service Packs e atualizações cumulativas do SQL Server. |
| DISABLE_PARAMETER_SNIFFING | TF 4136 | Desabilita o parâmetro de rastreamento, a menos que o valor OPTION(RECOMPILE), WITH RECOMPILE ou OPTIMIZE FOR seja usado. |
| ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES | TF 4137 | Faz com que o SQL Server gere um plano usando seletividade mínima ao estimar e predicados para filtros de correlação, em modelo de estimativa de cardinalidade de consulta otimizador do SQL Server 2012 e versões anteriores. |
| DISABLE_OPTIMIZER_ROWGOAL | TF 4138 | Faz com que o SQL Server gere um plano que não usa ajustes de meta de linha com consultas que contenham as palavras-chave TOP, OPTION (FAST N), IN ou EXISTS. |
| ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS | TF 4139 | Ativar automaticamente gerado estatísticas rápidas (emenda do histograma), independentemente do status da coluna de chave. Se essa opção for usada, independentemente do status de coluna estatísticas principais (em ordem crescente, decrescente ou papel de carta), o histograma usado para estimar a cardinalidade será ajustado em tempo de compilação de consulta. |
| ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS | TF 9476 | Faz com que o SQL Server gerar um plano usando a suposição de confinamento simples em vez da suposição de confinamento de Base padrão, no query optimizer cardinalidade estimativa modelo do SQL Server 2014 através de versões do SQL Server 2016. |
| DISABLE_OPTIMIZED_NESTED_LOOP | TF 2340 | Faz com que o SQL Server não use uma operação de classificação (classificação em lotes) para associações de loop aninhado otimizado ao gerar um plano. |
| FORCE_DEFAULT_CARDINALITY_ESTIMATION | TF 2312 | Permite que você definir o modelo de estimativa de cardinalidade de Otimizador de consulta para o SQL Server de 2014 por meio de versões do SQL Server 2016, dependentes do nível de compatibilidade do banco de dados. |
Para consultar esta relação de hint options disponíveis para uso com USE HINT, a Microsoft adicionou um nova DMV denominada: sys.dm_exec_valid_use_hints, sendo esta melhor detalhada no próximo post da sessão Para que serve (não deixe de conferir). A seguir destaco algumas formas de uso do novo USE HINT em conjunto com alguns hints options.
Exemplos
Para ilustrar e ajudar a entender melhor os exemplos a seguir, utilize o banco de Dados AdventureWorksDWCTP3 disponível para download através do link: https://www.microsoft.com/en-us/download/details.aspx?id=49502
— Exemplo 1 – Utilizando o hint option FORCE_LEGACY_CARDINALITY_ESTIMATION —
— Exemplo 2 – Utilizando o hint option FORCE_LEGACY_CARDINALITY_ESTIMATION em conjunto com a QUERYTRACEON 9481 —
Dica: Para que você possa entender e analisar melhor como o SQL Server 2016 se comportar a partir do momento que o argumento USE HINT é declarado, recomendo que você habilite a apresentação do recurso de Include Actual Execution Plan em seu Management Studio e execute estes exemplos sem a declaração do USE HINT e posteriormente com a declaração do mesmo, além disso, também realize a comparação dos planos de execução para notar e identificar as mudanças comportamentais.
Referências
· https://msdn.microsoft.com/en-us/library/ms188396.aspx
· https://msdn.microsoft.com/en-us/library/ms181714.aspx
· https://msdn.microsoft.com/en-us/library/mt791356.aspx
· https://blogs.msdn.microsoft.com/sql_server_team/developers-choice-use-hint-query-hints/
Conclusão
Como de costume a cada nova versão ou atualização a Microsft esta apresentando diversas inovações e melhorias no Microsoft SQL Server, mantendo o produto no seu mais alto nível de funcionalidades, recursos e inovações.
Neste post você pode perceber mais uma melhoria adicionada ao produto, onde através deste novo query hint USE HINT temos a capacidade de usar dicas de consulta orientando o query optimizer a mudar sua forma de execução e comportamento sem necessitarmos de elevação no nível de permissão ou fazermos partes de uma função de servidor sysadmin, algo que representa uma grande evolução por parte do produto no que se relacionado a controle de permissões e níveis de segurança.
Sinceramente eu vejo este novo recurso como um pequeno easter egg, pois na minha humilde opinião estava muito mas muito bem escondido.
Agradecimentos
Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.
Valeu….
Junior Galvão - MVP - Data Platform 
Pingback: Dica do Mês – Microsoft SQL Server – Identificando as transações que estão utilizando o Transact Log – Junior Galvão – MVP – Data Platform – SQL Server
Pingback: Dica do Mês – Conhecendo e aplicando o uso de atualização de estatísticas incrementais – Junior Galvão – MVP – Data Platform – SQL Server
Pingback: Dica do Mês – Utilizando a Trace Flag 9292 para exibir objetos estatísticos úteis – Junior Galvão – MVP – Data Platform – SQL Server
Pingback: Dica do Mês – Simulando a inserção de uma massa de dados aleatória | Junior Galvão – MVP – Data Platform
Pingback: Dica do Mês – Microsoft SQL Server 2017 – Relação de Novas colunas internas | Junior Galvão – MVP – Data Platform