Índices redundantes

Question

Prezados colegas, estou analisando a tabela de uma base com 4 milhões de registros, no qual existem na mesma cerca de 20 índices, sendo o recomendado pela MS de trabalhar entre 5 e 11 índices por tabela. Ao primeiro momento me parece existir índices redundantes do tipo: Index 1 (Nome, Data, Descrição); Index 2 (Data, Descrição);  Index 3 (Nome, Descrição) e Index 4 (Nome, Data). Um dúvida, o Index 1 atenderá perfeitamente as consultas que necessitar das colunas (Nome, Data, Descrição)? Outro detalhe importante que preciso da sugestão é de como poderia avaliar o estorno dos índices, sendo que todos estão sendo usado, ou seja, com valores preenchidos nos campos user_seeks + user_scans + user_lookups, porém com valores altissimos no campo user_updates. A regra diz que quando o valor user_updates é muito maior que  user_seeks a qualidade do índices não é boa. Como posso levar em consideração isso se ao rodar as DMVs todos os 20 índices tem o valor de user_updates maior? Qual a melhor maneira de iniciar a minha análise para estornar o que está como desnecessário na tabela do banco?

Agradeço desde já!

   

Answer 1

Deleted

Answer 2

Os índices citados são apenas suposição grande Diz. Estará querendo compreender melhor a forma de analisar o problema que tenho numa consulta em especifica, pois a mesma está levando 12 minutos para me dar o retorno dos resultados. Quando analisei a consulta vi que na mesma tinha o envolvimento de uma tabela bem complexa, pelo fato de ser uma das tops do meu banco de dados. Uma tabela com 4 milhões de registros com 20 índices é bem fora do normal. Então inicialmente decidir avaliar o estorno dos índices desnecessários para melhor a performance dos comandos de update, insert e delete, para depois tentar verificar a consulta que está lenta. A tabela tem índice clustered e 19 índices noclustered, sendo algumas com colunas incluídas (INCLUDE).

Para analisar a existência do hint é utilizar o profiler por certo período e posteriormente varrer em busca do Table_Name WITH (INDEX = NOme_Indice)?

 

Answer 3

Deleted

Answer 4

Jerfeson,

As orientações do José Diz são de extrema importância, eu particularmente gostaria de sugerir que você tente através do script abaixo tentar identificar os índices que não estão sendo utilizados:

-- List unused indexes
SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
        i.name
FROM    sys.indexes AS i
        INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE   i.index_id NOT IN ( SELECT  s.index_id
                            FROM    sys.dm_db_index_usage_stats AS s
                            WHERE   s.[object_id] = i.[object_id]
                                    AND i.index_id = s.index_id
                                    AND database_id = DB_ID() )
        AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;

Através do resultado fica mais fácil analisar e tentar tomar uma ação.

---

Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 5

Para verificar índices não utilizados uso o script abaixo:

;WITH IndicesNaoUtilizados As (
SELECT
    DB_NAME(database_id) As Banco, OBJECT_NAME(I.object_id) As Tabela, I.Name As Indice,
    U.User_Seeks As Pesquisas, U.User_Scans As Varreduras, U.User_Lookups As LookUps,
    U.Last_User_Seek As UltimaPesquisa, U.Last_User_Scan As UltimaVarredura,
    U.Last_User_LookUp As UltimoLookUp, U.Last_User_Update As UltimaAtualizacao,
	i.type_desc as tipo
FROM
    sys.indexes As I
    LEFT OUTER JOIN sys.dm_db_index_usage_stats As U
    ON I.object_id = U.object_id AND I.index_id = U.index_id
WHERE database_id = DB_ID())

SELECT
    Banco, Tabela, Indice, Pesquisas, Varreduras, LookUps,
    UltimaPesquisa, UltimaVarredura, UltimoLookUp, tipo
FROM IndicesNaoUtilizados
WHERE
    (Pesquisas + Varreduras + LookUps) = 0
	and tipo = N'NONCLUSTERED' 
order by Tabela asc

Bem eficiente, porém meu caso primeiro é reduzir a quantidade de índices na tabela e resolver o problema de lentidão na consulta que a tabela está bem envolvida.

 

Answer 6

Deleted

Answer 7

(...) tentar identificar os índices que não estão sendo utilizados: (...)

(...) Para verificar índices não utilizados uso o script abaixo: (...)

Há vários scripts na web que utilizam sys.dm_db_index_usage_stats para descobrir índices não utilizados, passíveis de remoção (DROP). Entretanto, tal fonte não deve ser utilizada com este propósito, pois é preciso ficar atento que, sempre que o serviço SQL Server Engine for reiniciado ou o banco de dados for desconectado/reconectado, os valores serão zerados.

O fato de um índice não constar de sys.dm_db_index_usage_stats não significa que ele não seja utilizado mas sim que ele não foi utilizado nas queries executadas desde a ativação do serviço SQL Server mas que pode estar em uso em outras queries que não tenham sido executadas.

Em suma, não é uma fonte confiável para detectar índices que não sejam utilizados.

---

    José Diz     Belo Horizonte, MG - Brasil
(Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)

---

José,

Concordo com a sua observação, realmente não pode ser considerado a melhor solução, mas sim é uma forma de se identificar.

Pode ser mesmo que um determinado índice criado não conste naquele momento nesta dmv, mas o mesmo pode estar vinculado a outra query, então ai temos outra necessidade para ser analisada, porque será que uma determinada query que faz uso de um índice específico não foi utilizada?

Essa é justamente a consideração a fazer, ao meu ver tanto a query quanto o índices são passíveis de remoção, a própria Microsoft adicionou esta dmv como recurso para tentarmos eliminar a existência de objetos "índices" que por algum motivo ou necessidade não estão sendo utilizados!!! Isso tudo vai depender muito da análise e interpretação que o time de desenvolvimento e principalmente os DBAs tem que verificar.

---

Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 8

Mais sempre relaciono o resultados dos índices com o tempo em que o serviço do SQL Server está ONLINE. Quais a melhores medidas para reduzir a redundância dos índices? É válido analisar individualmente índice a índice?  

Answer 9

Jerfeson,

Analisar cada índice eu particularmente não acho uma boa ideia, o que gosto de fazer é analisar a estrutura da tabela, os dados contidos nela e principalmente as querys processadas com maior frequência que podem estar utilizando este índice.

---

Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 10

Galvão, poderia me sugestionar de que forma faz essa análise? Você diz que analisa a estrutura da tabela (Que pontos observa?), os dados contidos? Quais são os critérios que adota para as querys que mais são processadas usando determinado índice que devo levar em consideração para estorno de tal índice?

Answer 11

Jerfeson,

A análise é simples, normalmente não utilizo outras ferramentas, o próprio Management Studio, SQL Server Profiler ou Activity Monitor.

1 - Análise da estrutura da tabela:

• Campos;
• Tipos de Dados;
• Tamanhos especificados;
• Índices;
• Uso de chave primária; e
• Relacionamentos.

2 - Em relação aos dados:

• Verifico uma média de espaço ocupado em cada coluna;
• Verifico a existência de colunas que não possuem dados utilizados, sendo colunas desnecessárias;

3 - As querys:

• Normalmente eu utilizo o Activity Monitor para identifcar as querys que estão sendo mais utilizadas para cada banco de dados;
• Também utilizo um script para listar as querys mais pessadas de todo servidor;
• Analiso o plano de execução de cada query principalmente identificando a possível ocorrência de Key-Lookup, Table-Scan, Heap ou Paralelismo;
• Verifico em cada plano de execução se os índices listados como chaves primárias estão sendo utilizadas;
• Nestas querys tendo identificar a ocorrência de índices existentes nas tabelas que não estão sendo utilizados; e
• Dependendo da query utilizo o Set Statistics Time e Set Statistics IO.

---

Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]