1

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?

6
  • 4
    You should really check Brent Ozar's sp_blitzindex and check his videos brentozar.com/blitzindex – Danielle Paquette-Harvey Jan 16 at 14:02
  • Do you have a performance problem with your updates to this table? Presumably you made this index for a reason - did it help with it? It’s important not to worry about the wrong metrics – Andrew Sayer Jan 16 at 19:21
  • @AndrewSayer. I have solved the problem with the table with the other indexes I have created. I just want to learn to make these decisions. E.g. the CI now has 100000 seeks everyday and performance is a lot better. I can't even put a number on the difference... But I want to learn when the cost of having the index is more than the benefit. – xhr489 Jan 16 at 23:26
  • 1
    Do you have query store enabled? – Ali Razeghi Jan 17 at 14:40
  • @AliRazeghi. No, what can I find there to help with my quesiton? – xhr489 Jan 17 at 19:34
3

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.

1
  • Thanks I just wait to see if others have a better answer before I accept. Good idea with statistics IO. – xhr489 Jan 16 at 23:32
1

It really depends on ... what fields, how big index etc.... The ratio is not great but ... Is it a simple logging table with insert only? how often do you insert vs how often do you query. If you query only once a week but do a big select on a multimilion wide table then even the 10 index scans can be ok..... To summarize, I would ask what select is running and using the index and what is the performance without the index.

Not the answer you're looking for? Browse other questions tagged or ask your own question.