索引視圖 - 如何驗證它是否值得？

我有以下索引視圖：

CREATE view VProcessStatus
WITH SCHEMABINDING
AS
select processstatus, 
     StatusCount = COUNT_BIG(*) 
     FROM DBO.tbltaxtransaction
     GROUP BY processstatus


CREATE UNIQUE CLUSTERED INDEX IDX_VProcessStatus ON VProcessStatus (processstatus)

我在不同的數據庫文件、不同的文件組上創建了它。

這個數據庫是一個訂閱者，這個表是高度更新的。我可以通過執行以下選擇來說明這一點：

select * from sys.objects
where object_id = object_id ('VProcessStatus')
go
select * 
from sys.dm_db_index_usage_stats 
where OBJECT_ID = object_id ('VProcessStatus')

如何計算擁有此索引視圖的成本？

插入/更新/刪除的過載？

我可以查看的統計數據，以便我決定是否值得擁有它？

此腳本包含幾列

1. UserSeek : Number of seeks by user queries          
2. UserScans: Number of scans by user queries
3. UserLookups: Number of bookmark lookups by user queries
4. UserUpdates: Number of updates by user queries

根據這些列統計資訊，您可以確定索引是否有用。

實際上，索引會在每個 DML 語句中插入、更新或刪除，因此如果索引顯示特定索引的更新，請不要擔心。通常不添加索引，除非它對查詢的響應速度超過 20% \ 足夠快。

SELECT DB_name() DBName,
                       '[' + Sch.name + '].[' + vi.[name] + ']' AS vileName,
                       Ind.type_desc,
                       Ind.[name] AS IndexName,
                       SUBSTRING(( SELECT  ', ' + AC.name
                                   FROM    sys.[views] AS v
                                           INNER JOIN sys.[indexes] I ON v.[object_id] = I.[object_id]
                                           INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
                                                                                AND I.[index_id] = IC.[index_id]
                                           INNER JOIN sys.[all_columns] AC ON v.[object_id] = AC.[object_id]
                                                                              AND IC.[column_id] = AC.[column_id]
                                   WHERE   Ind.[object_id] = I.[object_id]
                                           AND Ind.index_id = I.index_id
                                           AND IC.is_included_column = 0
                                   ORDER BY IC.key_ordinal 
                                 FOR
                                   XML PATH('') ), 2, 8000) AS KeyCols,
                       SUBSTRING(( SELECT  ', ' + AC.name
                                   FROM    sys.[views] AS v
                                           INNER JOIN sys.[indexes] I ON v.[object_id] = I.[object_id]
                                           INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
                                                                                AND I.[index_id] = IC.[index_id]
                                           INNER JOIN sys.[all_columns] AC ON v.[object_id] = AC.[object_id]
                                                                              AND IC.[column_id] = AC.[column_id]
                                   WHERE   Ind.[object_id] = I.[object_id]
                                           AND Ind.index_id = I.index_id
                                           AND IC.is_included_column = 1
                                   ORDER BY IC.key_ordinal 
                                 FOR
                                   XML PATH('') ), 2, 8000) AS IncludeCols ,
                       usg_stats.user_seeks AS UserSeek,
                       usg_stats.user_scans AS UserScans,
                       usg_stats.user_lookups AS UserLookups,
                       usg_stats.user_updates AS UserUpdates,
                       getdate()  StatsTimestamp                 
               FROM    sys.[indexes] AS Ind
                       INNER JOIN sys.[views] AS vi ON vi.[object_id] = Ind.[object_id]
                       INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = vi.[schema_id]
                       LEFT OUTER JOIN sys.dm_db_index_usage_stats AS usg_stats ON  Ind.index_id = usg_stats.index_id
                                                   AND Ind.[OBJECT_ID] = usg_stats.[OBJECT_ID]  and usg_stats.database_id = DB_ID(''
                             + db_name() + '' )
               WHERE  Ind.type_desc <>'HEAP'
               and vi.[name] ='VProcessStatus'
               and Ind.[name]='IDX_VProcessStatus'

驗證腳本：

select * from dbo.VProcessStatus WITH (NOEXPAND) where processstatus='active'

更多關於如何確定是否需要或需要索引

引用自：https://dba.stackexchange.com/questions/86292