Início / dba / Perguntas / 31635
Accepted
DamagedGoods
Asked: 2013-01-10 06:06:56 +0800 CST

Ok para descartar índices em FK's se eles não tiverem estatísticas no DMVS

  • 772

Usei a conhecida consulta abaixo de Kevin Kline para verificar índices não utilizados. Vários índices criados em chaves estrangeiras não retornam estatísticas de leitura, apenas gravações.

Você está 100% seguro para descartar esses índices? ou eles poderiam ser usados ​​pelo otimizador para, digamos, inserções ou exclusões e não registrariam nenhuma estatística no DMV? em caso afirmativo, como se pode dizer que eles são 100% seguros para remover?

O servidor está em execução há 2 meses, então tenho certeza de que cobri nosso ciclo de carga de trabalho mensal.

    SELECT o.name
    , indexname=i.name
    , i.index_id   
    , reads=user_seeks + user_scans + user_lookups   
    , writes =  user_updates   
    , rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
    , CASE
        WHEN s.user_updates < 1 THEN 100
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END AS reads_per_write
    , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
    FROM sys.dm_db_index_usage_stats s  
    INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id   
    INNER JOIN sys.objects o on s.object_id = o.object_id
    INNER JOIN sys.schemas c on o.schema_id = c.schema_id
    WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
    AND s.database_id = DB_ID()   
    AND i.type_desc = 'nonclustered'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 2000
    ORDER BY  name, reads
index sql-server-2012

1 respostas

  1. Best Answer

    Contanto que você tenha certeza de que o servidor esteve ativo o tempo todo e que ninguém limpou as estatísticas do DMV inadvertidamente. Isso pode acontecer se o banco de dados for desanexado + reanexado / restaurado / off-line + on-line / fechamento automático + on-line ou se o índice tiver sido descartado / recriado explicitamente (o DMV não é afetado por desabilitar / reconstruir / reorganizar, exceto no caso do SQL Server 2012, onde a reconstrução atualmente limpa as estatísticas - suspeito que isso será corrigido - obrigado @MartinSmith).

    Você deve esperar que, se uma atividade DML usasse o índice em uma capacidade de leitura por algum motivo (por exemplo, para executar uma atualização em alguma outra tabela), isso seria registrado como uma atividade de leitura, não uma gravação. Todas as gravações que você vê são manutenção de índice.

    Como um aparte, aqui está uma maneira um pouco mais eficiente de escrever essa consulta. Eu removi a subconsulta correlacionada referenciada duas vezes e descartei as junções desnecessárias para sys.objectse sys.schemas. Também corrigi algumas pequenas coisas de sintaxe, como prefixar todas as colunas com aliases quando apropriado, colocar palavras reservadas entre colchetes e remover a AS 'column alias'sintaxe. Achei confuso que no meio da SELECTlista você mudou de alias = expressionsintaxe para expression AS aliassintaxe - você deve escolher uma e ser IMHO consistente. Eu os alterei tanto para a maioria na consulta quanto para minha preferência pessoal . :-)

    SELECT name = OBJECT_NAME(s.[object_id])
    , indexname = i.name
    , i.index_id   
    , reads = s.user_seeks + s.user_scans + s.user_lookups   
    , writes = s.user_updates   
    , g.[rows]
    , reads_per_write = CASE
        WHEN s.user_updates < 1 THEN 100.0
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END
    , [drop statement] = 'DROP INDEX ' + QUOTENAME(i.name) 
      + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id])) 
      + '.'    + QUOTENAME(OBJECT_NAME(s.[object_id]))
    FROM sys.dm_db_index_usage_stats AS s  
    INNER JOIN sys.indexes AS i 
      ON i.index_id = s.index_id 
      AND s.object_id = i.object_id   
    INNER JOIN 
    (
      SELECT [object_id], index_id, [rows] = SUM([rows]) 
        FROM sys.partitions GROUP BY [object_id], index_id
        HAVING SUM([rows]) > 2000
    ) AS g
      ON i.[object_id] = g.[object_id] 
      AND i.index_id = g.index_id
    WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
    AND s.database_id = DB_ID()   
    AND i.type_desc = 'nonclustered'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    ORDER BY name, reads;
    • 5

relate perguntas