Several questions about sp_BlitzIndex

Question

When sp_BlitzIndex does index suggestions, the question in my head is as below:

Yes the suggested index would bring a lot of speed benefit when doing select statements. But there are also another costs for these indexes which are delete, insert, update queries

Do sp_BlitzIndex script take into consideration delete, insert, update costs as well? or only it does evaluate how much speed benefit would come with select statements?

Moreover, how can i list bad indexes via sp_BlitzIndex? and how are they determined to be bad?

Thank you very much for answers

Answer 1

and welcome to Stack Exchange. Going forward, try to break questions out individually. Here's how to write a great question.

Do sp_blitz script take into consideration delete, insert, update costs as well?

sp_BlitzIndex has several sections based on SQL Server's diagnostic management views.

• The missing indexes are based on sys.dm_db_missing_index_details, which don't take into account delete/update/inserts.
• The existing indexes list is based on sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats, which do take into account deletes/updates/inserts.

Moreover, how can i list bad indexes via sp_blitz? and how are they determined to be bad?

sp_BlitzIndex doesn't call indexes bad. It treats indexes a lot like human behaviors - for example, hoarding isn't necessarily bad in small amounts, or agoraphobia, or kleptomania, or being a furry. All kinds of behaviors are okay in small doses. It's up to you, not the script, to determine if an index is bad.