What impact do indexes have on the performance of table modifications?
As @mrdenny said, every index will need to be changed with INSERT/UPDATE/DELETE operations.

If you are weary about an index is actually be utilized to its fully capacity, and that it's not just a performance degrade, you should query the sys.dm_db_index_usage_stats.

Here are some important fields that are returned:


user_seeks
bigint
Number of seeks by user queries. 
user_scans
bigint
Number of scans by user queries. 
user_lookups
bigint
Number of bookmark lookups by user queries. 
user_updates
bigint
Number of updates by user queries. 
last_user_seek
datetime
Time of last user seek 
last_user_scan
datetime
Time of last user scan. 
last_user_lookup
datetime
Time of last user lookup. 
last_user_update
datetime
Time of last user update.