Conheça a SQL Server Table Hints

Por definição, uma Hint (Dica) é uma diretiva que diz ao otimizador do SQL Server (Query Optimizer) o que deve estar no plano de execução (Query Plan) da consulta ao rodar uma instrução DML (Linguagem de Manipulação de Dados) e o otimizador irá obedecê-lo, a menos que seja impossível de implementar.

Algumas vezes, o otimizador do SQL Server não retorna um bom plano de execução e é para esse tipo de cenário que você pode optar por utilizar uma Hint, como por exemplo, forçar a utilização de um determinado índice nos casos em que o otimizador de consultas não está retornando um bom plano.

Primeiramente vamos compreender um pouco sobre a função do otimizador de consultas do SQL Server e sua relação com as Hints.

O otimizador é um componente da Engine do SQL Server responsável por definir como as Queries serão processadas. São determinados quais índices serão usados, a ordem na qual as tabelas serão processadas, o tipo de algoritmo de Join que será executado, se a Query irá utilizar multiprocessadores em sua execução, entre outras funções. Provavelmente é um dos componentes mais complexos de toda a Engine do SQL Server.

As Hints aplicadas as Queries substituem o comportamento padrão do otimizador de consultas, buscando a melhora dos planos de execução individuais.

Antigamente, em versões iniciais do SQL Server, os engenheiros desenvolviam o otimizador pensando em um dia que esse componente pudesse sempre retornar o melhor plano de execução para toda Query e as Hints não seriam mais necessárias. Na versão do SQL Server 7.0, qualquer momento era oportuno para utilização das Hints, pois retornava, na maioria das vezes, um melhor plano de execução do que os sugeridos pelo otimizador.

Com o passar do tempo e versões, parecia que as Hints seriam descontinuadas à medida que o otimizador tornava-se mais sofisticado, mas na realidade foi o contrário que aconteceu. Com novas Features sendo adicionadas ao SQL Server e as Queries ficando mais sofisticadas, o otimizador se tornou, com o passar do tempo, tão complexo e com inúmeros planos de execução possíveis para investigar, que não existem maneiras de sempre retornar com o melhor plano. A meta que foi definida agora é trabalhar com o melhor plano possível, sem gastar mais tempo do otimizador do que a Query necessita para execução.

Existem hoje quase 50 tipos diferentes de Hints e a cada versão do SQL Server mais e mais são incluídas, mesmo levando em consideração o fato de que o otimizador pode sempre retornar o melhor plano de execução por conta própria.

Uso das Hints

Para usar as Hints, primeiramente questiona-se as aplicações que farão uso do seu benefício, pois não imaginam o enorme impacto que o design da aplicação tem sobre as Hints, se forem apropriadas ou necessárias. Caso o modelo da sua base de dados seja clássico, utilize a terceira forma normal (3FN) aplicada sobre as tabelas e as Queries todas escritas usando o padrão ANSI, o SQL Server provavelmente vai fazer um trabalho razoável em sua Query sem qualquer modificação adicional, ou seja, sem a necessidade de uma Hint para melhorar o plano de execução.

À medida que o sistema é pressionado e o design estressado de diferentes maneiras, você poderá identificar áreas onde os algoritmos do produto começam a não funcionam bem. Por exemplo, se você tem enormes variações de distribuição de dados ou uma aplicação que depende de uma correção estatística nos valores da coluna para assim poder selecionar um bom plano de execução, então às vezes você pode não conseguir a ordem do Join que é ideal para a Query executar da melhor maneira possível.

Levando em consideração o exemplo citado, antes de considerar o uso das Hints, garanta que você pode entender como sua aplicação é modelada, especialmente relacionado às questões que fazem com que seu sistema não se pareça com uma aplicação de banco de dados comum.

Caso você identifique um desempenho pobre em sua Query é importante para sua aplicação que você tenha uma ideia de por que o otimizador possa estar tendo problemas. Deve ser considerado se uma Hint irá ajudar essa aplicação, mas normalmente é indicado não utilizar as Hints a menos que elas tenham uma boa razão, que significa, o comportamento padrão do sistema é inaceitável para o negócio e uma melhor escolha de plano de execução é necessária. Então, se você sabe que uma ordem de Join específico ou uma escolha de índice mantem um Deadlock com outras Queries no seu sistema, você deve considerar usar uma Hint. Lembrando que Deadlock é quando duas ou mais tarefas bloqueiam uma à outra permanentemente, sendo que cada uma tem o bloqueio de um recurso, que a outra tarefa está tentando bloquear.

Algumas equipes de desenvolvimento podem impor regras fixas, como não utilizar Hints ou sempre forçar um índice em determinada tabela quando executar um Select. Por mais que possa parecer um erro manter tal tipo de regra, não significa que eles possam estar errados, mas geralmente esse tipo de regra existe por bons motivos, mas claro que os esclarecimentos dos motivos pelos quais isso é feito devem ser discutidos com o DBA. Quando uma nova Feature é construída e mudam a aplicação do banco de dados, usar uma Hint ou alterar as práticas de desenvolvimento pode ser completamente apropriado, assim como existem situações que são inapropriadas. Sendo assim, todas as situações devem ser avaliadas com cautela.

Tipos de Hints

As Hints podem ser divididas em três categorias: Join Hints, Query Hints e Table Hints. Todas as Hints têm suas particularidades para distingui-las umas das outras:

• As Join Hints são referenciadas na cláusula Join, antes deste predicado;
• As Query Hints são frequentemente mencionadas como Option Hints, por que são especificadas em uma cláusula especial chamada Option, que é usada apenas para esse tipo de Hint. A cláusula Option, se incluída na Query, é sempre a última instrução de qualquer T-SQL.
• As Table Hints são especificadas na cláusula From depois do nome da tabela;

Join Hint

Essa Hint especifica ao otimizador de consulta para forçar o uso de determinado algoritmo de Join antes de efetuar o relacionamento entre duas tabelas, tendo como o objetivo a busca por uma melhora de desempenho na execução da consulta. Por padrão, o SQL Server tenta escolher o melhor plano de execução antes de fazer o relacionamento entre as tabelas, sendo assim, se deve ter cuidado ao utilizar os Join Hints para não degradar o desempenho da consulta no ambiente.

Os Join Hints devem ser especificados na cláusula From da consulta e o SQL Server suporta quatro tipos de Joins (Loop, Hash, Merge e Remote) que podem ser utilizados como Hint, sendo que, todos se aplicam as instruções de Select, Update e Delete.

Query Hint

O Query Hint é um conceito que é suportado pelo SQL Server desde as versões iniciais, entretanto, essas versões tinham uma quantidade pequena de Hints disponíveis. Olhando hoje para documentação oficial, veremos que existem muitos mais do que podem caber em uma única página.

Essa Hint é utilizada quando algum tipo de lógica tem que ser aplicada a uma consulta. As Query Hints afetam todos os operadores da consulta e para utilizá-las devem-se incluir na consulta à cláusula Option, aplicando dessa forma a lógica à consulta. Qualquer consulta contendo as instruções Select, Update, Delete, Insert e Merge podem utilizar as Query Hints aplicados a eles.

A cláusula Option é sempre adicionada ao final da instrução T-SQL, diferente dos Join Hints, que são adicionados na instrução T-SQL depois das tabelas que eles devem afetar.

O mesmo alerta do Join Hint deve ser mantido: o otimizador de consultas do SQL Server busca sempre o melhor plano de execução para a execução da Query e é recomendando que o Query Hint seja usado apenas como último recurso por desenvolvedores experientes ou DBAs para alcançar determinados resultados.

Um último ponto de atenção sobre o uso das Query Hints, diferente dos Locking Hints (Table Hints), que o SQL Server tenta aplicar, é que as Query Hints são fortes, então se o SQL Server não consegue aplicar uma Query Hint irá retornar um erro de número 8622 e não será criado nenhum plano de execução.

Essa mensagem do erro 8622 ocorre quando o O processador de Query não pode gerar um plano de execução por causa das Hints definidas nesta Query. Submeta novamente a Query sem especificar qualquer Hint e sem utilizar Set Forceplan, que é a opção no SQL Server que faz com que o SQL Server execute os joins na sequência em que foram escritos na query.

Table Hint

Essa Hint normalmente é utilizada quando certo mecanismo de bloqueio (Lock) nas tabelas deve ser controlado. O otimizador de consultas do SQL Server sempre coloca os apropriados bloqueios nas tabelas quando qualquer uma das instruções TSQL são utilizadas. Existem certos casos em que o DBA sabe quando e onde subscrever o comportamento padrão do algoritmo de bloqueio e as Table Hints são uteis nesses cenários.

As Table Hints também podem ser utilizadas para forçar o uso de um índice na tabela, ou seja, sobrescrever o comportamento dos índices no plano de execução da consulta.

Levando em consideração que as Table Hints podem afetar o controle dos bloqueios, existe a possibilidade de afetarem a integridade dos dados, levando a dados incorretos ou a perda dos mesmos.

Table Hints são incluídas na cláusula From da instrução e afetam apenas a tabela ou View referenciada nessa cláusula.

Caso a Hint não seja satisfeita, o otimizador apresenta um erro e não irá retornar nenhum plano de execução. As Table Hints referentes aos controles de bloqueios, conhecidas como Locking Hints, são uma exceção, pois esse tipo, algumas vezes, é ignorado para preservar a integridade das operações de manipulação de dados necessárias para o sistema funcionar de forma correta.

O SQL Server geralmente escolhe o correto tipo de Lock e o modo de Lock. Esse comportamento não deve ser alterado, a menos que, através de testes, tenha se mostrado que uma diferente abordagem é preferível. Mantenha em mente que as configurações de nível de isolamento afetam os Locks que são mantidos, os conflitos que causam Blocking e a duração dos Locks. Seu nível de isolamento está em uso enquanto durar a sessão e você deve escolher aquele que fornece a consistência de dados requerida pela sua aplicação. Use os Table-level Locking Hints para mudar o comportamento padrão do Lock apenas quando necessário, pois desabilitando o nível de Locking pode afetar de forma negativa a concorrência.

A sintaxe T-SQL permite que você especifique Locking Hints para tabelas individuais quando elas são referenciadas nas cláusulas Select, Insert, Update ou Delete. A Hint diz ao SQL Server o tipo de Locking ou versionamento de linha para usar na tabela em uma determinada Query. Pelo fato dessas Hints serem especificadas na cláusula From, elas são chamadas de Table-level Hints.

O SQL Server Books Online lista outros Table-level Hints além dos Locking Hints, mas a grande maioria deles afeta o comportamento do Locking. Essas Hints devem ser usadas apenas quando você realmente precisa de um controle mais preciso do Locking ao nível de objeto do que é fornecido pelo nível de isolamento da sessão.

Muitos Locking Hints trabalham apenas no contexto da transação, entretanto, todas as cláusulas de Insert, Update e Delete estão automaticamente em uma transação, ou seja, a única preocupação é quando você usa um Locking Hint com uma cláusula Select. Para obter benefícios da maioria dos Hints quando usados em uma cláusula Select, você deve declarar explicitamente uma transação, começando pelo Begin Tran e terminando pelo Commit Tran ou Rollback Tran.

Tipos Table Hints

Existem cerca de 23 tipos de Table Hints diferentes, mas nesse artigo apresentaremos apenas quatro tipos: NOEXPAND, INDEX, KEEPIDENTITY e KEEPDEFAULTS.

Para os exemplos apresentados foi utilizado o banco AdventureWorks2012 e alguns comandos estão sujeitos a compatibilidade de versão.

NOEXPAND

A Hint NOEXPAND se aplica apenas a Views indexadas, dizendo ao otimizador para usar apenas os índices definidos para ela e não olhar para as tabelas que compõem a View. Isso pode ser necessário em algumas circunstâncias onde a View indexada pode trabalhar mais rápido com seus próprios índices, do que deixar o otimizador acessar os índices das tabelas adjacentes.

Por exemplo, no script da Listagem 1, um índice Cluster será adicionado a tabela utilizada na consulta em seguida, dessa forma poderemos observar o plano de execução utilizando o índice recém criado. Veja a execução na Figura 1.

Listagem 1. Criação do índice e Query executada.

  CREATE UNIQUE CLUSTERED INDEX [IDX_HumanResources] ON [HumanResources].[Employee] ( [BusinessEntityID] ASC ) 
   
  GO
   
  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee]

Figura 1. Plano de execução com índice IDX_HumanResources sendo utilizado.

No script da Listagem 2 será criado uma View com a mesma consulta anterior e um novo índice Cluster será criado em cima da View, ou seja, indexando a View. Ao observar novamente o retorno do plano de execução, o índice que foi escolhido pelo otimizador não foi o criado em cima da View e sim, o IDX_HumanResources criado inicialmente na tabela que compõe a View. Observe a execução na Figura 2.

Listagem 2.Criação da View indexada, adição do índice e execução da Query.

  CREATE VIEW [vw_HumanResources] WITH SCHEMABINDING
  AS
  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee]
   
  GO
   
  CREATE UNIQUE CLUSTERED INDEX [IDX_vw_HumanResources] ON [vw_HumanResources] ( [BusinessEntityID] ASC ) 
   
  GO
   
  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [vw_HumanResources]

Figura 2. Plano de execução com índice IDX_HumanResources sendo utilizado.

Para contornar essa situação da não utilização do índice da View, colocaremos em ação a Hint NOEXPAND e veremos o retorno do plano de execução dessa vez utilizando o índice da VIEW, ou seja, o IDX_vw_HumanResources, como mostra o script da Listagem 3, e a execução na Figura 3.

Listagem 3.Query utilizando a Hint NOEXPAND.

  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [vw_HumanResources] (NOEXPAND) -- Utilize com ou sem a cláusula WITH

Figura 3. Plano de execução com índice IDX_vw_HumanResources no resultado.

INDEX

As Hints que forçam o uso de índices são um recurso poderoso e ao mesmo tempo potencialmente perigoso, pois seu uso pode ser feito sempre que desejar alterar o comportamento do otimizador para conseguir um possível melhor plano de execução com índices específicos.

Por exemplo, no script da Listagem 4 temos uma consulta e no retorno do plano de execução será possível observar o seu custo estimado, assim como, o índice escolhido pelo otimizador para o uso. Veja a execução na Figura 4.

Listagem 4. Query executada.

  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee] 
    where [JobTitle] = 'Research and Development Manager'

Figura 4. Plano de execução com o custo estimado da Query e índice utilizado.

Caso um novo índice ,que certamente atenda melhor a consulta, for adicionado, veremos que nessa situação o otimizador irá escolher o plano de execução com o novo índice criado e seu custo será melhor estimado do que o mostrado anteriormente. Veja a Listagem 5 e a Figura 5.

Listagem 5. Criação do índice e Query executada.

  CREATE NONCLUSTERED INDEX [NonClusteredIndex_HumanResources] ON [HumanResources].[Employee]
  (
         [JobTitle] ASC
  )
  INCLUDE (    [BusinessEntityID],
         [BirthDate],
         [MaritalStatus])
   
  GO
   
  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee] 
    where [JobTitle] = 'Research and Development Manager'

Figura 5. Plano de execução com o custo estimado da Query e índice utilizado.

Optando por forçar o uso de determinado índice na consulta, então podemos utilizar a Hint, como mostra a Listagem 6. Colocaremos o predicado With após a tabela na qual o índice deverá atender e o nome do mesmo, que no caso será o índice Clustered IDX_HumanResources. Veja o resultado na Figura 6.

Listagem 6. Query executada com a Hint forçando o índice.

  
  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee] WITH (INDEX(IDX_HumanResources)) -- Pode também ser utilizado como (INDEX(1))
    where [JobTitle] = 'Research and Development Manager'

Figura 6. Plano de execução com o índice forçado sendo utilizado.

O objetivo dessa alteração do índice utilizado no plano de execução, é para tentar conseguir um melhor desempenho do que o otimizador poderia proporcionar em sua sugestão inicial, mas como vimos anteriormente o custo estimado do índice escolhido pelo otimizador foi superior ao índice Clustered IDX_HumanResources que foi forçado pela Hint.

Comparando os custos entre ambas consultas, sem a Hint e com a Hint, veremos que o impacto de desempenho foi muito grande e a escolha inicial do otimizador foi com certeza a melhor opção nesse cenário que foi apresentado, como mostra a Figura 7. Caso mais registros sejam adicionados a tabela e as estatísticas mudarem, existe a chance do cenário mudar e talvez o índice forçado tenha um comportamento diferente.

Figura 7. Comparação de custos entre os planos de execução.

KEEPIDENTITY

A Hint KEEPIDENTITY se aplica apenas as instruções Insert e caso a opção Bulk seja usada com OPENROWSET, permitindo que os valores Identity no arquivo de dados importado possa ser usado pela coluna Identity na tabela de destino. Se por acaso essa Hint não for especificada, os valores da coluna Identity são verificados, mas não importados.

Por exemplo, no script da Listagem 7 temos o uso da Hint após o comando With dentro de uma operação de Bulk Insert.

Listagem 7. Importação de dados pelo Bulk Insert.

  BULK INSERT [HumanResources].[Employee]
     FROM 'C:\Employeedoc.Dat'
     WITH (
        KEEPIDENTITY,
        FORMATFILE='C:\Employeedoc-f-n-x.Xml'
     )

KEEPDEFAULTS

A Hint KEEPDEFAULTS também se aplicar apenas as instruções Insert e caso a opção Bulk seja usada com OPENROWSET, permitindo a inserção de uma coluna na tabela com valor padrão, em vez de nulo quando o registro não existe para a coluna.

Por exemplo, no script da Listagem 8 vemos como uso da Hint após o comando With dentro de uma operação de Insert funciona.

Listagem 8. Importação de dados pelo OPENROWSET.

  INSERT INTO [HumanResources].[Employee]
      WITH (KEEPDEFAULTS)
      SELECT *
        FROM OPENROWSET(BULK  'C:\Employeedoc -c.Dat',
        FORMATFILE='C:\Employeedoc -f-c.Fmt'     
        ) as t1 

Como mencionado, existem 23 tipos de Table Hints e veremos mais algumas em uma próxima oportunidade. As já apresentadas neste artigo são as mais comumente usadas.

Espero que tenham gostado e até a próxima.