Consultoria DBA SQL Server

Saiba como encontrar todos os índices duplicados nas tabelas do SQL Server.

Qual a importância em saber quais são as tabelas que têm índices duplicados no SQL Server?
Quando você tem uma tabela com índices nas mesmas colunas, você acaba perdendo espaço em disco e perdendo performance nas operações DML do banco SQL Server. Por que sempre que você atualizar uma tabela, esse índice também tem que ser atualizado, com isso acaba-se perdendo performance.

Sendo assim você não querendo perder performance obviamente vai querer identificar quais são os índices que estão duplicados no seu banco de dados certo?

Olhando em alguns arquivos de scripts que eu tenho eu encontrei esse script que encontra todos esses índices.

Muito bem, eu vou deixar disponível para vocês duas versões a versão para que possa executar apenas na base de dados que você estiver logado e a versão que você possa executar em todas as bases de dados em apenas um script usando o mesmo conceito que eu já escrevi em um outro artigo aqui no blog que é “Executar o mesmo comando para todos os bancos de dados no SQL Server.”

Script 1: Execute apenas na base de dados atual.

WITH indexcols AS
 (SELECT object_id AS id,
 index_id AS indid,
 name,
 
 (SELECT CASE keyno
 WHEN 0 THEN NULL
 ELSE colid
 END AS [data()]
 FROM sys.sysindexkeys AS k
 WHERE k.id = i.object_id
 AND k.indid = i.index_id
 ORDER BY keyno,
 colid
 FOR XML PATH('') ) AS cols,
 
 (SELECT CASE keyno
 WHEN 0 THEN colid
 ELSE NULL
 END AS [data()]
 FROM sys.sysindexkeys AS k
 WHERE k.id = i.object_id
 AND k.indid = i.index_id
 ORDER BY colid
 FOR XML PATH('') ) AS inc
 FROM sys.indexes AS i )
SELECT DB_NAME() AS 'DBName',
 OBJECT_SCHEMA_NAME(c1.id) + '.' + OBJECT_NAME(c1.id) AS 'TableName',
c1.name + CASE c1.indid WHEN 1 THEN ' (clustered index)' ELSE ' (nonclustered index)' END AS 'IndexName', c2.name + CASE c2.indid
 WHEN 1 THEN ' (clustered index)'
 ELSE ' (nonclustered index)'
 END AS 'ExactDuplicatedIndexName'
FROM indexcols AS c1
INNER JOIN indexcols AS c2 ON c1.id = c2.id
AND c1.indid < c2.indid
AND c1.cols = c2.cols
AND c1.inc = c2.inc;

Script 2: Execute em todos os bancos de dados.

DECLARE @command varchar(4000) 
SELECT @command = 'USE ? 
;
 
WITH indexcols AS
 (SELECT object_id AS id,
 index_id AS indid,
 name,
 
 (SELECT CASE keyno
 WHEN 0 THEN NULL
 ELSE colid
 END AS [data()]
 FROM sys.sysindexkeys AS k
 WHERE k.id = i.object_id
 AND k.indid = i.index_id
 ORDER BY keyno,
 colid
 FOR XML PATH('''') ) AS cols,
 
 (SELECT CASE keyno
 WHEN 0 THEN colid
 ELSE NULL
 END AS [data()]
 FROM sys.sysindexkeys AS k
 WHERE k.id = i.object_id
 AND k.indid = i.index_id
 ORDER BY colid
 FOR XML PATH('''') ) AS inc
 FROM sys.indexes AS i )
SELECT DB_NAME() AS ''DBName'',
 OBJECT_SCHEMA_NAME(c1.id) + ''.'' + OBJECT_NAME(c1.id) AS ''TableName'',
c1.name + CASE c1.indid WHEN 1 THEN '' (clustered index)'' ELSE '' (nonclustered index)'' END AS ''IndexName'', c2.name + CASE c2.indid
 WHEN 1 THEN '' (clustered index)''
 ELSE '' (nonclustered index)''
 END AS ''ExactDuplicatedIndexName''
FROM indexcols AS c1
INNER JOIN indexcols AS c2 ON c1.id = c2.id
AND c1.indid < c2.indid
AND c1.cols = c2.cols
AND c1.inc = c2.inc;
'
EXEC sp_MSforeachdb @command

Lembrando que antes de você sair correndo apagando os índices eu recomendo dar uma olhada se o SQL Server não está mais utilizando-o. Mas Wesley, como eu faço isso?

Simples, basta acessar esse link de outro artigo que eu escrevi que é “TOP 10 Scripts de índices que todos DBAs precisam saber – SQL Server”

Lá você encontrará um parágrafo dizendo índice não utilizado com um script abaixo. Pronto, você agora sabe quais são os índices que não são mais utilizados no banco de dados e duplicados. Só falta excluir e mandar o relatório para seu cliente de antes/depois para deixá-lo bem feliz!

Grande abraço!