3

I'm using sp_blitzIndex to check the health of my database indexation, but I don't understand every column used in the output. I'm talking specifically about "Usage Stats" and "Op Stats". I find many indexes with zero Reads but a thousand writes (example: Reads: 0 Writes: 42,597). Does it mean that the index isn't used at all ? At least not for SELECT statements ? Is it safe to remove it or disable it at first ?

PS: My SQL instance hasn't been rebooted for very long time.

1 Answer 1

4

FYI, here is Brent's support page for sp_blitzIndex.

Scripts like Brent's are based on SQL's built-in "dynamic management views" (DMVs), such as sys.dm_db_index_usage_stats, so let's look at that documentation page:

The counters are initialized to empty whenever the database engine is started. Use the sqlserver_start_time column in sys.dm_os_sys_info to find the last database engine startup time. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

So the statistics displayed by this DMV (and any fancier procs, like Brent's) are only displaying index usage since the last SQL instance restart.

So yes, as long as the server has been running "long enough"*, it is likely that any indexes with all writes and no reads are good candidates for removal.

* Big caveat: you might think a few weeks or a month is "long enough", but are you sure there isn't some high-level manager somewhere that does a big "end of month" (or "end of quarter") report from your database? Are you super duper sure? Indexes that are only needed for that one rare report are much less likely to show up in this DMV.

5
  • Thanks for your answer. I know that the counters are reseted after a reboot or manually. I just want to be sure that the meaning of "Reads" is being not used or rarely used by my ERP queries.
    – dba_maroc
    Oct 4, 2021 at 15:06
  • @dba_maroc Yes, 0 would mean "This index has not been used since last reboot".
    – BradC
    Oct 4, 2021 at 15:11
  • Thank you. One last question, how do you read : "Reads: 16,331 (16,331 seek) Writes: 24,262" AND "0 singleton lookups; 14,528 scans/seeks; 0 deletes; 0 updates; " whats does it mean exactly ?
    – dba_maroc
    Oct 4, 2021 at 15:40
  • The "singleton lookups" column is from the dm_db_index_operational_stats DMV, it describes it as the "Cumulative count of single row retrievals from the index or heap." Not sure that I can elaborate, but hope that helps.
    – BradC
    Oct 4, 2021 at 17:10
  • Thank you BRadC. Your clarifications are good for me.
    – dba_maroc
    Oct 5, 2021 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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