Saudações, entusiastas do SQL Server! Neste post do blog, discutiremos o conceito de índice de cobertura e como ele pode melhorar significativamente o desempenho, reduzindo a E/S.

Antes de entrarmos em detalhes, é importante observar que a otimização de índices é um assunto complexo e deve ser abordado com cautela. Ter muitos índices, especialmente índices de cobertura, pode realmente prejudicar o desempenho. No entanto, quando usados corretamente, os índices desempenham um papel crucial na sintonia de desempenho.

Vamos começar executando uma consulta e examinando o resultado usando o comando DBCC e o plano de execução:

USE [AdventureWorks]
GO

SET STATISTICS IO ON

-- Limpar os buffers (não execute esses comandos DBCC em um servidor de produção)
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

-- Executar a instrução SELECT
SELECT ProductID, ProductNumber, Color
FROM Production.Product
WHERE ProductID < 500
GO

O resultado das estatísticas mostra o número de leituras lógicas, leituras físicas e leituras antecipadas realizadas pela consulta. Além disso, o plano de execução fornece informações valiosas, especialmente o custo de E/S.

Agora, vamos criar um índice de cobertura para a consulta que acabamos de executar. Um índice de cobertura inclui todas as colunas da instrução SELECT:

-- Criar Índice de Cobertura
IF EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID(N'[Production].[Product]') AND name = N'AK_Product_Cover')
    DROP INDEX [AK_Product_Cover] ON [Production].[Product]
GO

CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_Cover] ON [Production].[Product] (ProductID, ProductNumber, Color) ON [PRIMARY]
GO

Com o índice de cobertura no lugar, vamos executar a consulta novamente e observar os resultados:

-- Limpar os buffers
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

-- Executar a instrução SELECT
SELECT ProductID, ProductNumber, Color
FROM Production.Product
-- WITH (INDEX(AK_Product_Cover)) -- Use esta dica se a consulta não usar apenas o índice
WHERE ProductID < 500
GO

Como você pode ver, a consulta agora utiliza o índice de cobertura recém-criado. As estatísticas mostram uma redução significativa nas leituras lógicas, leituras físicas e leituras antecipadas. O plano de execução também reflete o custo de E/S melhorado.

Comparando o desempenho das duas consultas, uma usando o índice da chave primária e a outra usando o índice de cobertura, fica evidente que o índice de cobertura não apenas reduz a E/S, mas também melhora o desempenho geral da consulta:

-- Comparando o Desempenho
SELECT ProductID, ProductNumber, Color
FROM Production.Product WITH (INDEX(PK_Product_ProductID))
WHERE ProductID < 500
GO

SELECT ProductID, ProductNumber, Color
FROM Production.Product WITH (INDEX(AK_Product_Cover))
WHERE ProductID < 500
GO

Em conclusão, o índice de cobertura melhora significativamente o desempenho da consulta, reduzindo a E/S. É importante observar que isso é apenas uma demonstração do conceito e não deve ser implementado em um servidor de produção sem uma análise e teste adequados.

Fique ligado para futuros posts, onde exploraremos outras técnicas de otimização de desempenho, como índices incluídos.

Obrigado por ler e feliz codificação!