Table Hints

Nesse post vou tentar explicar de uma forma simples os diversos hints que podem ser utilizados em tabelas.

Lembrando que a sua utilização deve ser avaliada com extremo cuidado.

O que são table hints ?

Hints ou Dicas são comandos utilizados para alterar o comportamento padrão da engine do SQL Server durante a execução de comandos DML (Data Manipulation Language).

Hints podem ser utilizados para especificar a utilização de um determinado índice ou para a alteração do tipo lock que será realizado durante a execução de scripts.

| INDEX =  ( index_value )

• Força a utilização de um índice especifico existente na tabela.

USE WideWorldImporters

SELECT * 
FROM [Purchasing].[PurchaseOrders] PO
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (INDEX = FK_Purchasing_PurchaseOrderLines_PurchaseOrderID) 
 ON PO.PurchaseOrderID = POL.PurchaseOrderID

 

| INDEX  ( index_value [ ,…n ] )

• Se um índice clusterizado existir, o hint WITH (INDEX(0)) irá forçar um CLUSTERED INDEX SCAN e o hint WITH (INDEX(1)) forçará um CLUSTERED INDEX SCAN OR SEEK.
• Se não existir um índice clusterizado, o hint WITH (INDEX(0)) irá forçar um TABLE SCAN e o hint WITH (INDEX(1)) irá causar erro.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (INDEX(0))
 ON PO.PurchaseOrderID = POL.PurchaseOrderID
 
USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (INDEX(1))
 ON PO.PurchaseOrderID = POL.PurchaseOrderID

 

| FORCESEEK 

• Força o padrão de busca a ser utilizado pelo query optimizer ser feito apenas por busca de índices para retornar os dados. Nesse caso o índice mais relevante da tabela será considerado.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (FORCESEEK)
ON PO.PurchaseOrderID = POL.PurchaseOrderID

 

| FORCESEEK [( index_value ( index_column_name  [ ,… ] ) ) ]

• Especifica para o query optimizer que a operação de busca será realizada apenas de um única forma, através do índice especificado no hint.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO WITH (FORCESEEK, INDEX(PK_Purchasing_PurchaseOrders))
INNER JOIN [Purchasing].[PurchaseOrderLines] POL 
 ON PO.PurchaseOrderID = POL.PurchaseOrderID

 

| FORCESCAN

• Especifica que o query optimizer usará apenas uma operação de verificação de índice como o caminho de acesso na tabela.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (FORCESCAN)
 ON PO.PurchaseOrderID = POL.PurchaseOrderID
<br>

  | HOLDLOCK  

• Previne que uma operação DML, altere a tabela que em questão.

USE WideWorldImporters

BEGIN TRAN
UPDATE POL
SET POL.ExpectedUnitPricePerOuter = POL.ExpectedUnitPricePerOuter * 0.10
FROM [Purchasing].[PurchaseOrders] PO
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (HOLDLOCK) ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4
--COMMIT
--ROLLBACK

| NOLOCK

• Evita o bloqueio da tabela durante a execução da query, semelhante ao nível de isolamento READ UMCOMMITTED. Deve ser utilizado com cautela pois pode retornar dados sujos.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO WITH (NOLOCK)
INNER JOIN [Purchasing].[PurchaseOrderLines] POL ON PO.PurchaseOrderID = POL.PurchaseOrderID

| NOWAIT 

• Instrui o database engine a retornar uma mensagem de erro assim que um lock é encontrado em uma tabela.

Semelhante ao comando SET LOCK_TIMEOUT 0;

USE WideWorldImporters

BEGIN TRAN

UPDATE POL
SET POL.ExpectedUnitPricePerOuter = POL.ExpectedUnitPricePerOuter * 0.10
FROM [Purchasing].[PurchaseOrders] PO
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (NOWAIT) ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 2
--COMMIT
--ROLLBACK

| PAGLOCK 

• Realiza bloqueio ao nível de página.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (PAGLOCK)
ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4

| READCOMMITTED

• Especifica que a consulta irá obedecer aos mesmos princípios do nível de isolamento READ COMMITTED, esse nível utiliza bloqueios ou versionamento de linhas para a leitura dos dados, afim de evitar dados que não tenham sido comitados.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (READCOMMITTED)
ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4

| READCOMMITTEDLOCK

• Especifica que a consulta irá obedecer aos mesmos princípios do nível de isolamento READ COMMITTED, esse nível utiliza bloqueios para a leitura dos dados, afim de evitar dados que não tenham sido comitados.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (READCOMMITTEDLOCK)
ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4

| READPAST

• Esse hint permite que sejam ‘puladas’ as linhas locadas durante o retorno dos dados, ou seja, o seu resultado pode vir com linhas faltando.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (READPAST)
ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4

| READUNCOMMITTED

• Sua atuação é igual ao hint NOLOCK.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (READUNCOMMITTED)
ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4

| REPEATABLEREAD

• Esse hint causa um lock em todos os registros envolvidos na transação, impedindo a sua atualização, porém pode retornar leituras fantasmas durante operações de Inserção de dados.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (REPEATABLEREAD)
ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4

| ROWLOCK 

• Especifica que o lock será realizado por linha.

USE WideWorldImporters

BEGIN TRAN

UPDATE POL
SET POL.ExpectedUnitPricePerOuter = POL.ExpectedUnitPricePerOuter * 0.10
FROM [Purchasing].[PurchaseOrders] PO
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (ROWLOCK) ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 2
--COMMIT
--ROLLBACK

| SERIALIZABLE

• Semelhante ao HOLDLOCK, porém torna os locks mais restritivos ao longo da transação. Previne a leitura de dados sujos.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (SERIALIZABLE)
ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4

| SNAPSHOT

• Esse hint só funciona com memory optimized tables e a sua utilização está atrelada ao Transaction Initiation Mode [ Explicit Mode ].

| SPATIAL_WINDOW_MAX_CELLS = integer 

• Utilizado para especificar o número máximo de células em um mosaico geométrico.

| TABLOCK 

• Determina que o tipo de lock a ser realizado será aplicado para toda a tabela, porém existem casos em que instruções selects podem adquirir um bloqueio compartilhado.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (TABLOCK)
ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4

| TABLOCKX 

• Determina que o tipo de lock a ser realizado será Exclusivo do início ao fim da transação.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (TABLOCKX)
ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4

| UPDLOCK  

• Especifica que bloqueios de atualização serão mantidos até que o processo seja concluído.

USE WideWorldImporters

BEGIN TRAN

UPDATE POL
SET POL.ExpectedUnitPricePerOuter = POL.ExpectedUnitPricePerOuter * 0.10
FROM [Purchasing].[PurchaseOrders] PO
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (UPDLOCK) ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 2
--COMMIT
--ROLLBACK

| XLOCK  

• Especifica que bloqueios exclusivos serão usados e mantidos até que a transação seja concluída.

USE WideWorldImporters

SELECT *
FROM [Purchasing].[PurchaseOrders] PO 
INNER JOIN [Purchasing].[PurchaseOrderLines] POL WITH (XLOCK)
ON PO.PurchaseOrderID = POL.PurchaseOrderID
WHERE POL.PurchaseOrderID = 4