Tipos de SQL Server Table Hints

A 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. As Hints podem ser divididas em três categorias: Join Hints, Query Hints e Table Hints.

Nesse artigo conheceremos 19 dos 23 tipos de Table Hints diferentes. Os demais podem ser conferidos em uma introdução já publicada no Portal.

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

FORCESEEK

A Hint FORCESEEK especifica ao otimizador para utilizar apenas operações com índice Seek, ou seja, o SQL Server irá usar a estrutura b-tree do índice para buscar diretamente os registros correspondentes como caminho de acesso aos dados na tabela ou View. Em situações em que o plano de execução contém Table ou índice Scan e as tabelas correspondentes causam um grande número de leituras durante a execução da Query, forçar uma operação de índice Seek pode ser de grande ajuda para melhorar o desempenho da consulta.

Por exemplo, para utilizar a Hint deve ser especificado o predicado With após a tabela, juntamente ao nome do índice ou apenas o FORCESEEK sem especificar qual índice utilizar, como mostram a Listagem 1 e a Figura 1.

Listagem 1. Query utilizando FORCESEEK.

  SELECT EM.[BusinessEntityID]
        ,EM.[JobTitle]
        ,EM.[BirthDate]
        ,EM.[MaritalStatus]
        ,JC.[Resume]     
    FROM [HumanResources].[Employee] EM
         LEFT JOIN [HumanResources].[JobCandidate] JC
          WITH(FORCESEEK)
               ON EM.BusinessEntityID = JC.BusinessEntityID

Figura 1. Plano de execução com Hint em uso.

No script da Listagem 2 é demonstrado o uso da Hint determinando com qual índice especifico será feito o Seek.

Listagem 2. Query utilizando FORCESEEK especificando o índice.

  
  SELECT EM.[BusinessEntityID]
        ,EM.[JobTitle]
        ,EM.[BirthDate]
        ,EM.[MaritalStatus]
        ,JC.[Resume]     
    FROM [HumanResources].[Employee] EM
         LEFT JOIN [HumanResources].[JobCandidate] JC
          WITH(FORCESEEK,INDEX([IX_JobCandidate_BusinessEntityID]))
               ON EM.BusinessEntityID = JC.BusinessEntityID

Além de especificar o índice, também pode ser acrescentado as colunas pertencentes ao índice no uso da Hint, como mostra a Listagem 3.

Listagem 3. Query utilizando FORCESEEK especificando o índice e a coluna.

  SELECT EM.[BusinessEntityID]
        ,EM.[JobTitle]
        ,EM.[BirthDate]
        ,EM.[MaritalStatus]
        ,JC.[Resume]     
    FROM [HumanResources].[Employee] EM
         LEFT JOIN [HumanResources].[JobCandidate] JC
          WITH(FORCESEEK(IX_JobCandidate_BusinessEntityID(BusinessEntityID)))
               ON EM.BusinessEntityID = JC.BusinessEntityID

FORCESCAN

A Hint FORCESCAN especifica ao otimizador para utilizar apenas operações com índice Scan como caminho de acesso aos dados na tabela ou View. Pode ser bem útil em consultas nas quais o otimizador menospreza o número de linhas afetadas e escolhe uma operação de busca em vez de uma operação de verificação. Quando isso ocorre, a quantidade de memória concedida para a operação é muito baixa e o desempenho da consulta é afetado.

Por exemplo, os modos de especificar essa Hint na Query são iguais ao FORCESSEK visto anteriormente, como mostram as Listagem 4 e Figura 2.

Listagem 4. Query utilizando FORCESCAN.

  SELECT EM.[BusinessEntityID]
        ,EM.[JobTitle]
        ,EM.[BirthDate]
        ,EM.[MaritalStatus]
        ,JC.[Resume]     
    FROM [HumanResources].[Employee] EM WITH(FORCESCAN)
          JOIN [HumanResources].[JobCandidate] JC
               ON EM.BusinessEntityID = JC.BusinessEntityID

Figura 2. Plano de execução com Hint em uso.

HOLDLOCK

A Hint HOLDLOCK fornece um alto nível de consistência, mas tem como seu custo a baixa concorrência, sendo dessa forma equivalente ao nível de isolamento Serializable, que se baseia na concorrência pessimista e sendo o mais restritivo, bloqueando todas as modificações nos dados. Essa Hint especifica os bloqueios compartilhados que estarão esperando a conclusão da transação. Por padrão, os bloqueios compartilhados são liberados tão logo quanto as tabelas requeridas ou página de dados não sejam mais necessárias.

Por exemplo, o uso da Hint deve é feito de forma simples, como mostra a Listagem 5.

Listagem 5. Query utilizando a Hint HOLDLOCK.

  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee] WITH (HOLDLOCK)

Consultando a DMV sys.dm_tran_locks, que retorna informações sobre os bloqueios atuais, analisaremos as estruturas de bloqueio criadas na execução da consulta, poderemos ver a cada linha de requisição de bloqueio a comparação dos tipos e quantidade que foram criados antes e depois da utilização da Hint HOLDLOCK, como mostram as Figuras 3 e 4.

Figura 3. Resultado da execução da DMV antes de aplicar a Hint na consulta.

Figura 4. Resultado da execução da DMV depois de aplicar a Hint na consulta.

NOLOCK

A Hint NOLOCK possibilita trabalhar com uma alta concorrência, quando essa Hint é usada os bloqueios compartilhados não são feitos e nenhum bloqueio exclusivo é honrado.

Uma vez que a Hint NOLOCK não aplica nenhum bloqueio durante a leitura, os dados podem ser retornados sujos (leitura suja), ou seja, não comitados ainda por outras transações, sendo assim, evite utilizá-lo em operações de Delete e Update. Essa Hint é equivalente ao nível de isolamento ReadUncommited e será eliminada em versões futuras.

Por exemplo, o uso dessa Hint é igual a Hint HOLDLOCK, como mostra a Listagem 6.

Listagem 6. Query utilizando a Hint NOLOCK.

  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee] WITH (NOLOCK)

Analisando novamente a DMV sys.dm_tran_locks, podemos observar que durante a execução da Query com a Hint, não são apresentados os bloqueios nas páginas de dados, como mostra a Figura 5.

Figura 5. Resultado da execução da DMV depois de aplicar a Hint na consulta.

NOWAIT

A Hint NOWAIT instrui ao Database Engine para retornar uma mensagem assim que um bloqueio é encontrado na tabela. Esse comando é equivalente a especificar o SET LOCK_TIMEOUT para um tabela específica, mas não tão flexível.

Por exemplo, abra duas conexões diferentes: na primeira será executado um Update e na segunda uma consulta na mesma tabela que está sofrendo o Update. Com isso os bloqueios serão gerados e em nosso exemplo nos basearemos que o nível de isolamento em uso é o Read Committed, que especifica que as instruções não podem ler os dados que foram modificados e não comitados por outras transações, como mostram as Listagens 7 e 8.

Listagem 7. Query com Update da tabela.

  --SESSÃO 1
  BEGIN TRAN
   
  GO
   
  UPDATE [HumanResources].[Employee] SET JobTitle = 'DBA'
  WHERE JobTitle = 'Research and Development Manager'

Listagem 8. Consulta aos dados da tabela sem especificar Hint.

  --SESSÃO 2
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   
  GO
   
  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee]

Uma vez que ambas as operações foram executadas em sequência, a sessão 2 que está com o Select ficará aguardando o fim da transação com a operação de Update da sessão 1, para só assim, retornar os dados da instrução Select. Para contornar essa situação colocaremos em uso a Hint NOWAIT e novamente executaremos a consulta, como mostra o script da Listagem 9.

Listagem 9. Consulta aos dados da tabela especificando a Hint NOWAIT.

  --SESSÃO 2
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   
  GO
   
  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee] WITH (NOWAIT)

Imediatamente após executar a consulta é retornado a mensagem 1222, em vez de ter que aguardar pela conclusão da transação da sessão 1, como mostra a Figura 6.

Figura 6. Mensagem de erro após o uso da Hint NOWAIT.

PAGLOCK

A Hint PAGLOCK especifica que bloqueios compartilhados de página serão colocados em vez de bloqueios a nível de tabela, linhas ou chaves.

Por exemplo, ao executar a mesma consulta utilizada em exemplos anteriores das Hints e verificar a DMV sys.dm_tran_locks, poderemos ver que três bloqueios foram criados com os modos S (Shared) e ID (Intent Shared). Sendo que, a nível de Bando de Dados o bloqueio colocado foi o Shared e para o nível de página e objeto foi o Intent Shared.

O modo de bloqueio Shared é usado para operações de leitura que não precisam alterar dados, como por exemplo, instruções Select. Já o modo Intent Shared é uma combinação do Intent, que é usado para estabelecer uma hierarquia de bloqueio, com o Shared.

O Intent Shared protege a requisição ou bloqueios compartilhados que foram adquiridos em algum recurso menor na hierarquia, como mostram a Listagem 10 e a Figura 7.

Listagem 10. Query com consulta sem o uso da Hint.

  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee]

Figura 7. Bloqueios gerados pela consulta sem o uso da Hint.

Aplicando a Hint PAGLOCK, o resultado apresentando pela DMV que analisa os bloqueios muda um pouco. A quantidade bloqueios continua sendo três, mas agora ao nível de página o modo aplicado foi modificado para Shared, ou seja, podemos ver efetivamente o resultado da utilização da Hint na consulta, como mostram a Listagem 11 e a Figura 8.

Listagem 11. Query com consulta utilizando a Hint PAGLOCK.

  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee] WITH (PAGLOCK)

Figura 8. Bloqueios gerados pela consulta com o uso da Hint PAGLOCK.

READCOMMITTED

A Hint READCOMMITTED especifica que as instruções não podem ler dados que foram modificados e ainda não comitados por outras transações. Dessa forma, essa Hint deve obedecer as regras do nível de isolamento Read Committed usando bloqueios compartilhados ou versionamento de linha, caso a opção Read Committed Snapshot estiver ativa.

Na Listagem 12 será aberta a transação em uma primeira sessão com a instrução de Update.

Listagem 12. Query com Update.

array13

Na segunda sessão o nível de isolamento será definido como Read UnCommitted e aplicado a Hint READCOMMITTED na consulta. Dessa forma, poderemos observar a real aplicação da Hint, visto que, irá sobrepor o comportamento padrão de nível de isolamento. Ao executar a consulta não serão retornados os registros até que a transação da outra conexão seja comitada ou dada o Rollback, evitando dessa forma a leitura suja dos dados, como mostra a Listagem 13.

Listagem 13. Query com nível de isolamento e uso da Hint ReadCommitted.

  --SESSÃO 2
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   
  GO
   
  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee] WITH (READCOMMITTED)

READCOMMITTEDLOCK

A Hint READCOMMITTEDLOCK garante que operações de leitura obedeçam as regras do nível de isolamento Read Committed usando bloqueios, independentemente da configuração do Read Committed Snapshot.

Será aberta a transação em uma primeira sessão com uma instrução de Update, como mostra a Listagem 14.

Listagem 14. Query com Update.

array13

Na segunda sessão o nível de isolamento do banco AdventureWorks2012 será alterado para Read Committed Snapshot e o Select será executado. Com a Hint em uso, o comportamento dos bloqueios será convertido para o antigo padrão da base, ou seja, Read Committed, mesmo que já esteja configurado o Read Committed Snapshot. A instrução DBCC USEROPTIONS irá mostrar o atual nível de isolamento do banco AdventureWorks2012, como mostra a Listagem 15.

Listagem 15. Alteração no nível de isolamento e Query com Hint READCOMMITTEDLOCK.

  --SESSÃO 2
  ALTER DATABASE AdventureWorks2012 SET READ_COMMITTED_SNAPSHOT ON
  WITH ROLLBACK IMMEDIATE
   
  GO
   
  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee] WITH (READCOMMITTEDLOCK)
   
  GO
   
    DBCC USEROPTIONS

READPAST

A Hint READPAST especifica para o Database Engine que não considere qualquer bloqueio de linha ou página de dados quando retornar os resultados.

Leituras de dados sujas não acontecem com essa Hint, porque o READPAST não irá retornar registros bloqueados, mas devido a esses registros que não são retornados por causa do bloqueio é muito difícil determinar se o resultado está completo, com todas as linhas.

Por exemplo, na primeira sessão é retornado um total de 290 registros existentes na tabela, como mostra a Listagem 16.

Listagem 16. Query com total de linhas da tabela.

  --SESSÃO 1
  SELECT COUNT(*) AS QUANTIDADE 
    FROM [HumanResources].[Employee]

Na segunda sessão abriremos uma transação e a operação de Update será executada em apenas três registros, como mostra a Listagem 17.

Listagem 17. Query com Update.

  --SESSÃO 2
  BEGIN TRAN
   
  UPDATE [HumanResources].[Employee] SET [JobTitle] = 'DBA'
  WHERE BusinessEntityID in (242,243,244)

Em uma terceira sessão execute novamente a consulta que verifica o total de registros na tabela, mas dessa vez, utilizando a Hint READPAST. A quantidade total de linhas será de 287, por que ainda existem bloqueios colocados pela instrução Update nos três registros e estes não podem são ignorados pela Hint READPAST, como mostra o script da Listagem 18.

Listagem 18. Query com total de linhas utilizando a Hint READPAST.

  --SESSÃO 3
  SELECT COUNT(*) AS QUANTIDADE 
    FROM [HumanResources].[Employee] WITH (READPAST)   

READUNCOMMITTED

A Hint READUNCOMMITTED especifica que as instruções podem ler dados que foram modificados e ainda não comitados por outras transações, sendo assim, essa Hint deve obedecer as regras do nível de isolamento Read UnCommitted. Esse nível de isolamento é o menos restritivo dentro do SQL Server, sendo muito popular por desenvolvedores que desejam diminuir a quantidade de bloqueios, pois a Query não irá requerer bloqueios compartilhados durante a leitura, mas em compensação leituras sujas podem ocorrer sem restrições.

Como exemplo será aberta a transação na primeira sessão com uma instrução de Update, como mostra o código da Listagem 19.

Listagem 19. Query com Update.

array13

Em uma segunda sessão o nível de isolamento será definido como Read Committed e aplicado a Hint READUNCOMMITTED na instrução Select. Dessa forma poderemos observar o uso da Hint, visto que, irá sobrepor o comportamento padrão de nível de isolamento definido. Ao executar a consulta será retornado todos os registros, mesmo que ainda não tenha sido dado o Commit ou Rollback para transação da sessão 1, sendo assim, os dados retornados podem ser considerados como sujos. Veja o script da Listagem 20.

Listagem 20. Query com nível de isolamento e uso da Hint READUNCOMMITTED.

  --SESSÃO 2
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   
  GO
   
  SELECT [BusinessEntityID]
        ,[JobTitle]
        ,[BirthDate]
        ,[MaritalStatus]     
    FROM [HumanResources].[Employee] WITH (READUNCOMMITTED)

REPEATABLEREAD

A Hint REPEATABLEREAD garante uma maior consistência do que a Hint READCOMMITTED ou SERIALIZABLE. Por padrão, o SQL Server usa o nível de isolamento Read Committed e com ele durante a leitura de dados é colocado bloqueios compartilhados, isso garante que dados não comitados não sejam lidos, mas não garante que os dados não serão alterados antes do final da transação. Se você precisa que os dados atuais não sejam alterados antes do final da transação, você pode usar a Hint REPEATABLEREAD.

A Hint REPEATABLEREAD fornece o mesmo comportamento do nível de isolamento Repeatable Read, garantindo que a transação que leia uma tabela mais do que uma vez dentro desta mesma transação, possa fazer isso sem ler dados diferentes dos registros já encontrados da primeira vez.

No código da primeira sessão da listagem 21 será especificado o nível de isolamento Read Committed para simular o comportamento padrão do isolamento na sessão aberta. Além disso, a Hint REPEATABLEREAD estará em uso na consulta e teremos o comando WaitFor Delay para aguardar alguns segundos antes da conclusão da execução da Query.

Listagem 21. Query definindo o nível de isolamento e Hint.

  --SESSÃO 1
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   
  GO
   
  BEGIN TRAN 
   
  select * from [HumanResources].[Employee] WITH (REPEATABLEREAD)
  WHERE BusinessEntityID in (242,243)
   
  WAITFOR DELAY '00:00:10' 

Em uma nova sessão execute a operação da Update da Listagem 22 enquanto a Query anterior não finaliza sua execução. Esse Update será feito em um dos valores que fazem parte da transação anterior, sendo assim, nenhum dos dados já lidos anteriormente serão modificados na leitura, mas novos registros ainda podem surgir, visto que, não existem restrições para as operações de Insert.

Listagem 22. Query com Update.

  --SESSÃO 2
  UPDATE [HumanResources].[Employee] 
  SET [JobTitle] = 'DBA' 
  WHERE BusinessEntityID = 242 

ROWLOCK

A Hint ROWLOCK especifica que bloqueios compartilhados de linhas serão colocados em vez de bloqueios nas páginas ou tabela. O ROWLOCK fornece uma alta concorrência, mas ao custo de um baixo desempenho por que o SQL Server precisa segurar e liberar os bloqueios ao nível de linha.

Por exemplo, ao executar uma consulta simples com o uso da Hint e verificar a DMV sys.dm_tran_locks, poderemos ver que três bloqueios foram criados com os modos S (Shared) e ID (Intent Shared). Sendo que, a nível de Bando de Dados, o bloqueio colocado foi o Shared e para o nível de página e objeto foi o Intent Shared, como mostra o código a seguir:

select * from [Person].[Address] WITH (ROWLOCK)

O retorno pode ser visto na Figura 9.

Figura 9. Retorno da DVM sys.dm_tran_locks.

SERIALIZABLE

A Hint SERIALIZABLE fornece o mesmo comportamento do nível de isolamento que tem seu nome. É uma variação mais completa do Repeatable Read, bloqueando qualquer modificação de dados nas colunas que são consultadas até que sejam concluídas, independente da operação ser um Update ou Insert. Esse comportamento fornece uma alta consistência, mas ao custo de uma baixa concorrência.

Execute o script da Listagem 23 em uma nova sessão. Nele, o nível de isolamento Read Committed foi definido e a Hint é aplicada na consulta e o comando WaitFor Delay é usado para aguardar alguns segundos antes da conclusão da execução.

Listagem 23. Query definindo o nível de isolamento e utilizando a Hint SERIALIZABLE na consulta.

  --SESSÃO 1
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   
  GO
   
  BEGIN TRAN 
   
  select * from [HumanResources].[Employee] WITH (SERIALIZABLE)
  WHERE BusinessEntityID in (242,243,999)
   
  WAITFOR DELAY '00:00:10'

Em uma segunda sessão execute as operações de Update e Insert especificadas na Listagem 24. Para ambas operações o SQL Server irá gerar bloqueios impedindo a sua conclusão devido a Hint SERIALIZABLE que foi usada.

Listagem 24. Query com código do Insert.

  --SESSÃO 2
  UPDATE [HumanResources].[Employee] 
  SET [JobTitle] = 'DBA' 
  WHERE BusinessEntityID = 242 
   
   
  INSERT INTO [HumanResources].[Employee] 
  VALUES (999, 60517918,    'adventure-works\candy0',  
  'Accounts Receivable Specialist', 
  '1970-03-26', 'S',   'F',   '2003-02-07',
  0,     61,    50,    1,     '9E9F713B-707C-4F7E-9504-DE188052A045', 
  '2008-07-31 00:00:00.000')

SNAPSHOT

A Hint SNAPSHOT fornece o mesmo comportamento do nível de isolamento que tem seu nome, mas só se aplica a tabelas em memória, ou seja, que utilizam o recurso do In-Memory do SQL Server 2014. Essa Hint especifica que os dados lidos por qualquer instrução em transação serão a versão transacionalmente consistente dos dados que existiam no início da transação.

Na consulta da Listagem 25 é possível observar o uso dessa Hint.

Listagem 25. Consulta com a Hint SNAPSHOT em uso.

  BEGIN TRAN 
  select * from [HumanResources].[Employee] WITH (SNAPSHOT)
  WHERE BusinessEntityID in (242,243,999)

SPATIAL_WINDOW_MAX_CELLS

A Hint SPATIAL_WINDOW_MAX_CELLS é utilizada em objetos espaciais, especialmente para otimizações em cima dos índices espaciais, sendo que, seu efeito é maior em cima do resultado da consulta do que no índice espacial propriamente dito.

Na consulta da Listagem 26 é possível ver o uso da Hint alterando o valor padrão do número de células que usam o Tessellation, que é a divisão da área de backup em subáreas pequenas e registrar as subáreas que se cruzam cada instância espacial. Para o tipo de dados a geografia, isso significa que dividir o mundo inteiro em hemisférios e projetando cada hemisfério em um plano.

Listagem 26. Consulta com a Hint SPATIAL_WINDOW_MAX_CELLS em uso.

  select [AddressID],[SpatialLocation],[ModifiedDate] 
  from [Person].[Address] WITH (SPATIAL_WINDOW_MAX_CELLS = 1536)

TABLOCK

A Hint TABLOCK especifica que um bloqueio compartilhado será colocado na tabela até o final da transação, mas o tipo do bloqueio que é adquirido depende da instrução que está sendo executada. Ao utilizar a Hint o bloqueio compartilhado é aplicado a tabela inteira e não no nível de linha ou página.

Por exemplo, ao executar uma consulta simples e verificar a DMV sys.dm_tran_locks, poderemos ver que três bloqueios foram criados com os modos S (Shared) e IS (Intent Shared), sendo que, a nível de banco e página de dados, o bloqueio colocado foi o Shared e para o nível de objeto foi o Intent Shared, como mostra o código a seguir:

select * from [Person].[Address]

O retorno você confere na Figura 10.

Figura 10. Retorno da DMV sys.dm_tran_locks.

Após o uso da Hint TABLOCK, apenas permaneceram bloqueios compartilhados ao nível de banco de dados e objeto, como mostra o código a seguir e o resultado na Figura 11:

select * from [Person].[Address] WITH (TABLOCK)

Figura 11. Retorno da DMV sys.dm_tran_locks.

TABLOCKX

A Hint TABLOCKX especifica que um bloqueio exclusivo será colocado na tabela até o final da execução da instrução ou final da transação. Essa Hint fornece o pior controle de concorrência, mas o SQL Server se esforçará minimamente para garantir e liberar os bloqueios.

Por exemplo, analisando os bloqueios criados com a DMV sys.dm_tran_locks após a aplicação da Hint na consulta a seguir, podemos ver que a nível de banco de dados foi colocado um bloqueio compartilhado e para objeto, um bloqueio exclusivo:

select * from [Person].[Address] WITH (TABLOCKX)

O resultado pode ser visto na Figura 12.

Figura 12. Retorno da DMV sys.dm_tran_locks.

UPDLOCK

A Hint UPDLOCK força bloqueios de atualização em vez de bloqueios compartilhados a serem gerados, sendo assim, essa Hint especifica que bloqueios de Update são colocados enquanto é feito a leitura da tabela e eles são garantidos até o final da execução da instrução ou fim da transação. A Hint UPDLOCK utiliza bloqueios de atualização apenas em operações de leitura no nível de linhas ou página.

Novamente analisando a DMV sys.dm_tran_locks após a aplicação da Hint na consulta a seguir, podemos ver que a nível de banco de dados o bloqueio colocado foi o compartilhado, no objeto o Intent Shared, para página o Intent Update e na chave o de Update:

select * from [Person].[Address] WITH (UPDLOCK)

Na Figura 13 você confere retorno da consulta.

Figura 13. Retorno da DMV sys.dm_tran_locks.

XLOCK

A Hint XLOCK deve ser usada para colocar e segurar os bloqueios exclusivos até que a transação seja completada. Essa Hint força bloqueios exclusivos nos recursos que estão sendo referenciados.

Por exemplo, olhando a DMV sys.dm_tran_locks veremos que bloqueios específicos no uso da Hint XLOCK foram colocados para consulta a seguir, sendo que, ao nível do banco de dados o bloqueio foi compartilhado, para chave o exclusivo e na página o Intent Page:

select * from [Person].[Address] WITH (XLOCK)

O retorno você confere na Figura 14.

Figura 14. Retorno da DMV sys.dm_tran_locks.

Pontos de Atenção

Existem uma série pontos de atenção que devem ser levados em consideração:

• Esteja ciente que problemas com o uso das Hints podem acontecer a qualquer momento, podendo causar desde uma falha na compilação da Query, até as mais complexas e difíceis questões de desempenho;
• Em cenários onde ocorrem mudanças nos dados das tabelas, sem os Query Hints, o otimizador de Query automaticamente atualiza as estatísticas e ajusta os planos de execução conforme necessário, mas se você bloqueou o otimizador de Query para um grupo específico de comandos usando as Query Hints, então o plano de execução não pode ser modificado e você pode acabar com um plano ruim, necessitando de uma ação sua para identificar e resolver o novo problema desempenho.

A documentação oficial da Microsoft tem uma clara recomendação de precaução relacionada ao uso das Hints: “Como o otimizador de consulta do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, é recomendável que desenvolvedores e administradores de banco de dados experientes usem as Hints apenas como um último recurso.”

Mesmo em situações que existe a recomendação de permitir que o otimizador determine o melhor plano de execução para suas Queries, às vezes ele não consegue entregar o melhor plano e você pode achar que a única maneira razoável de conseguir desempenho é utilizar as Hints. Isso geralmente é uma mudança simples para sua aplicação, claro que após você verificar que a Hint desejada realmente faz diferença, entretanto, em alguns ambientes, você não tem controle sobre o código na aplicação. Em casos que as Queries são encapsuladas no código do Vendor ou quando modificando o mesmo, pode ocasionar em algum tipo de quebra de licenciamento ou garantia de suporte, você pode não conseguir colocar uma Hint na Query.

Ou seja, as Hints definitivamente têm seu lugar, entretanto, esse lugar não deve ser no topo da sua lista de técnicas de Tuning e otimização e sim, o mais próximo possível do final dessa lista. As Hints podem ser a solução se você não for capaz de achar outra maneira de fazer o otimizador do SQL Server encontrar um plano de execução aceitável, mas uma ótima dica é aprender todas as técnicas de Tuning e otimização antes de deliberadamente colocar várias Hints no código. Claro que sempre vão aparecer situações onde existem complexas Queries ou é lidado com grandes Datasets que derrotam as estimativas de cardinalidade do SQL Server, nesses cenários o uso de Hints pode ser necessário.

Até a próxima!