Say I create a nonclustered index on a table. After a week I look at the sats, e.g. number of seeks, scans, updates etc. E.g. say I have 10 seeks and 10000 updates. How can I measure the cost of the 10000 updates? That is how can I measure the cost of the 10000 updates on the nonclustered index vs 10 extra scans on the clustered index?
I want to learn when the cost of having the index is more than the benefit. How do I measure this?