Indexed View - how to verify if it is worthy?

Question

I have the following indexed view:

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)

I have created it on a different database file, different filegroup.

This database is a subscriber, this table is highly updated. I can tell this by running the following select:

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')

How can I calculate the cost of having this indexed view?

The overload on inserts/updates/deletes?

The stats that I could look at so that I can decide if is it worth having it?

Answer 1

This Script contain few columns

 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

on the basis these column stats you can figure out either index is useful or not.

Actually Index are inserted to, update to, or deleted on every DML statement so don’t worry if index show updates for a particular index. Typically don't add index unless it respond more than 20% \ enough fast for the query.

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'

Verify script :

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

More on How to determine if an Index is required or necessary