Quando é um índice que não vale a pena atualizar

Question

há uma proporção aceita de leituras para as gravações que faz um índice valer a pena ou é menos cortada e seca do que isso?

Estou usando isso:

WITH    UnusedIndexQuery ( Object_ID, ObjectName, IndexName, Index_ID, Reads, Writes, Rows )
          AS ( SELECT
                s.object_id ,
                objectname = OBJECT_NAME(s.OBJECT_ID) ,
                indexname = i.name ,
                i.index_id ,
                reads = user_seeks + user_scans + user_lookups ,
                writes = user_updates ,
                p.rows
               FROM
                sys.dm_db_index_usage_stats s
               JOIN 
                sys.indexes i
               ON
                i.index_id = s.index_id
                AND s.OBJECT_ID = i.OBJECT_ID
               JOIN 
                sys.partitions p
               ON
                p.index_id = s.index_id
                AND s.OBJECT_ID = p.OBJECT_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 p.rows > 10000
             ),
        IndexSizes ( schemaname, tablename, object_id, indexname, index_id, indextype, indexsizekb, indexsizemb, indexsizegb )
          AS ( SELECT
                sys_schemas.name AS SchemaName ,
                sys_objects.name AS TableName ,
                sys_objects.[object_id] AS object_id ,
                sys_indexes.name AS IndexName ,
                sys_indexes.index_id AS index_id ,
                sys_indexes.type_desc AS IndexType ,
                partition_stats.used_page_count * 8 AS IndexSizeKB ,
                CAST(partition_stats.used_page_count * 8 / 1024.00 AS DECIMAL(10,
                                                              3)) AS IndexSizeMB ,
                CAST(partition_stats.used_page_count * 8 / 1048576.00 AS DECIMAL(10,
                                                              3)) AS IndexSizeGB
               FROM
                sys.dm_db_partition_stats partition_stats
               INNER JOIN sys.indexes sys_indexes
               ON
                partition_stats.[object_id] = sys_indexes.[object_id]
                AND partition_stats.index_id = sys_indexes.index_id
                AND sys_indexes.type_desc <> 'HEAP'
               INNER JOIN sys.objects sys_objects
               ON
                sys_objects.[object_id] = partition_stats.[object_id]
               INNER JOIN sys.schemas sys_schemas
               ON
                sys_objects.[schema_id] = sys_schemas.[schema_id]
                AND sys_schemas.name <> 'SYS'
             )
    SELECT
        [IndexSizes].[tablename] ,
        [IndexSizes].[indexname] ,
        [IndexSizes].[indextype] ,
        [IndexSizes].[indexsizekb] ,
        [IndexSizes].[indexsizemb] ,
        [IndexSizes].[indexsizegb] ,
        UnusedIndexQuery.Reads ,
        UnusedIndexQuery.Writes ,
        CAST(CASE WHEN [Reads] = 0 THEN 1
                  ELSE [Reads]
             END / CASE WHEN [Writes] = 0 THEN 1
                        ELSE writes
                   END AS NVARCHAR(8)) + ':1' AS [Benefit Ratio (Read:Write)] ,
        UnusedIndexQuery.[Rows]
    FROM
        UnusedIndexQuery
    INNER JOIN IndexSizes
    ON  UnusedIndexQuery.object_id = IndexSizes.object_id
        AND UnusedIndexQuery.index_id = IndexSizes.index_id
    ORDER BY
        CASE WHEN [Reads] = 0 THEN 1
             ELSE [Reads]
        END / CASE WHEN [Writes] = 0 THEN 1
                   ELSE writes
              END ,
        reads ,
        [Writes] DESC ,
        [indexsizemb] DESC

para ter uma ideia do estado do benefício dos meus índices.

Nas duas extremidades dos resultados, sou claro - 1.000.000 de leituras e 0 de gravações = um bom índice para acelerar a recuperação de dados, 1.000.000 de gravações e 0 de leituras significa que estamos mantendo um índice para referência zero. O que não tenho certeza é onde a atividade é mostrada como mais equilibrada - onde eu faço o corte e começo a baixar os índices?

obrigado

Jonathan

    

Answer 1

Eu não acho que faça sentido basear a decisão no número de leituras / escritas sozinho (a menos que você leia == 0, mas então por que você tem a tabela?: -)).

Considere isso:

• mesmo que haja poucas leituras, elas podem consumir muito tempo sem o índice
• as leituras podem ser mais críticas que as gravações, portanto, um índice pode valer a pena, apesar do desempenho de gravação reduzido
• o desempenho de gravação não precisa necessariamente sofrer; Eu diria que o mais moderno DBMS pode atrasar a atualização do índice até que seja necessário, por exemplo, muitos INSERTs em sequência devem causar apenas uma atualização de índice

Em suma, como sempre, o único conselho é: perfil antes de otimizar. Não há um atalho fácil: - /.

    

Answer 2

O que você está tentando alcançar? você está tentando melhorar o desempenho de i / o? você está com pouco espaço em disco? Otimização prematura é a raiz de todo o mal!

Fique com os ganhos rápidos, como 0 lê & 100.000.000 de gravações. Tudo o resto é um trade off. Se o seu servidor tiver espaço livre, mas não tiver espaço em disco, comece a retroceder a partir da proporção mais baixa de leituras para gravações e fique de olho no desempenho.

Pode ser mais sensato explorar outras alternativas, como otimizar os procedimentos / consultas, adicionar compactação de página, adicionar espaço em disco / RAM etc.