Indexed Views

dietmarruef

Hello together,

I have a question regarding the indexes views and the real usage of them. I found a script on the internet that determines the user_seeks, user_updates, etc. for each
index and also the VSIFT indexes of the indexed views.

In a few tables I see that for user_seeks and user_updates (sys.dm_db_index_usage_stats) are the same but the values user_lookups and user_scans are zero.
This seems to me a little bit fishy. Can maybe somebody explain to me if this really means that I can drop these indexed views, they are decreasing heavily the index performance in the tables.

Thanks in advance,
Dietmar

kriki

I also noticed that for indexed views.
I also never saw (server-restarts apart) that indexed views have 0 seeks and 0 scans but have updates.

My conclusion was that the seeks are done for the updates. So if you have that 1 to 1 for seeks and no scans, it just means that it is read to update the indexed view. So you can disable the maintenance for this SIFT.
Also if you have few seeks/scans but a lot of updates, it is best to not maintain the SIFT especially if the last time the seek/scan was a few days (or longer) ago.