Indexes reduces (not always) the performance of Insert, Update, Delete and SELECT statements.
As a general rule, try to have around 5 indexes per tables, 5 columns per index. This is not a hard and fast rule. Just a general good starting point.
Indexes reduce the performance of SELECT statement even when an index is not being used.
When you set fill factor below 100%, you are actually setting fragmentation.
You’re purposely leaving empty space on every page in an index, which:
Spreadsheet inventory of existing indexes
You need to run the below script under each database:
sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2
Inventory of missing indexes
sp_BlitzIndex @GetAllDatabases = 1, @Mode = 3
Find large unused indexes
/* To find large, unused, junk drawer objects: */
sp_BlitzIndex @Mode =2 , @sortorder = 'size' sp_BlitzIndex @Mode =2 , @sortorder = 'rows'
/* For management overview before & after doing index tuning*/ sp_BlitzIndex @Mode=1
Database Blog 