Index with few seeks and many updates 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?
What I would do is
SET STATISTICS IO ON

and then I would test the SELECT statement that produces the seek and the UPDATE statement.
Finally, I would compare the number of pages read by the SELECT with the number of pages written by the UPDATE.
But in general this ratio between seek and updates seems to suggest that your nonclustered index is not valuable. But it really depends on your query needs.