Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
