I'm trying to do a bit of a clean up/review of the database I'm currently working on (which I didn't design, by the way). The DB is 'big' in term of objects (hundreds of tables, hundreds of SP, functions, a thousand triggers, looooots of columns, etc.) and it's designed, well...not that nicely. Not nicely at all. So, while I'm going through the long backlog of performances related issues that the customers have uselessly complained for in the last three years, I'd like to do some "easy fix" too.
Now, I've found an interesting number of things like 40% of the tables having no primary keys, 30% of tables having no indexes, and a lot of other really funny things (could go on for hours), but this question is focused on indexes.
I've been working on DBs for a while and I know that indexes can (and do) slow down the system during writes, no big surprise here. And yes, indexes still have to be stored somewhere so they increase the DB size, but...is there anything else aside from that?
The DB is ridiculously small, and the number of writes per seconds could probably be counted in writes per minutes without the need to go double digits, so I'm not concerned about those points.
I've written a bit of TSQL to find tables without indexes and 'lot' of records (like a table with four million records and not a single index or a primary key /facepalm), plus I've used sys.dm_db_missing_index_* to find what indexes the DBE think are needed.
So, bottom line: should I go on and blindly add indexes, or do I risk to kill system performances for some reason I'm not foreseeing?