2

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

1 Answer 1

6

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.

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.