是否有可接受的读写比率使索引值得，或者它是否比这更简单？
我正在使用这个：
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 次读取意味着我们正在维护一个零引用索引。
我不确定的是活动在哪里显示为更平衡 - 我在哪里进行削减并开始降低索引？
谢谢
乔纳森
