什么时候索引不值得更新

是否有一个公认的阅读与写作的比例，使得一个指数有价值，还是不那么切割和干燥？

我正在使用这个：

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,000次写入和0次读取意味着我们维持一个用于零参考的索引。
我不确定的是活动performance得更加平衡 – 我在哪里做下切，并开始下跌指数？

• 服务器在进行磁盘写入时几乎不可用
• VMWare服务器 – 将文件写入虚拟硬盘驱动器的性能
• SQL Server 2000，大型事务日志，几乎为空，性能问题？
• 我们如何为Nginx创buildBlackbox日志？
• MySQL＆tmpfs：性能

谢谢

乔纳森