sql-server – 什么时候索引不值得更新

是否有一个可接受的读写比率,使得一个指数值得,或者它的削减和干燥程度是否低于那个？

我用这个：

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

了解我的索引的好处的状态.

在结果的两端我很清楚 – 1,000,000次读取和0次写入=加速数据检索的良好索引,1,000次写入和0次读取意味着我们维护零参考索引.
我不确定的是活动显示为更平衡 – 我在哪里进行切割并开始删除索引？

谢谢

乔纳森

解决方法

考虑一下：

>即使读取很少,如果没有索引,这些也可能非常耗时
>读取可能比写入更具时间关键性,因此尽管写入性能降低,但索引可能是值得的
>写性能不一定受损;我认为大多数现代DBMS都可以延迟更新索引,直到需要它为止,例如顺序中的许多INSERT应该只导致一个索引更新

简而言之,与往常一样,唯一的建议是：优化前的配置文件.没有简单的捷径： – /.

（编辑：佛山站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!