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?

closed as too broad by Erik Darling, Kin Shah, hot2use, mustaccio, Philᵀᴹ Jul 12 '18 at 20:54

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • A lot of factors--just test them. In general, an index that significantly reduces the I/O and time of an often-used query is worth the overhead on CRUD operations. It's not going to destroy performance by adding one index, so pick the ones that will have the best impact on query performance and start adding them one at a time. In the unlikely event that CRUD performance suffers too much, then just delete the index. – Tony Hinkle Jul 12 '18 at 15:34

You have a lot of questions and unknowns (no info on the size of db, hardware - RAM, CPU, Disk (san or local ssd), is the server VM or physical, do you have baseline as to what looks good or what is acceptable, sp_configure settings, do you run maintenance - update stats, index reorg/rebuild etc) in your question.

should I go on and blindly add indexes

No. Read this first - Don't just blindly create those "missing" indexes!

Above will get you in good shape. If you see any issues, post the question back and we will help you :-)

  • I have no access to the servers. I have no server admins, too. Last conversation were "we just install SQL Server from the CD". Last time I asked about the TempDB configuration the answer has been "Temp what?". And the company is a selling a cloud environment and making billions. Last time I asked to run a health check against one of our customer DBs was 3 months ago, still waiting. I only have a backup of a customer DB stripped of most of it's data. So no, I can't do anything of what you suggested :-D – motoDrizzt Jul 12 '18 at 13:41
  • 2
    So you are basically asked to work with your hands tied ? Good luck ! – Kin Shah Jul 12 '18 at 13:42
  • I will put things on the right track and one day I'll be able to know which CPU we have, but it's like trying to steer a Mriya with sheer strength, takes time :-D And anyway, we are talking about a few gigabytes db, seriously, we could use an mdb file, CPU/RAM/Disk...are all irrelevant. – motoDrizzt Jul 12 '18 at 13:42
  • I just tried to run the code from the first link you posted: indexes have a write:read ratio of...zero :-D – motoDrizzt Jul 12 '18 at 14:07

If the data is all in a heap (no indexes at all) then adding a clustered index will generally help, heaps help nobody unless the table is very small, as you have to table scan EVERY time.

The ones that have indexes on already you want to do a bit more looking into, but from a personal perspective, adding an index to a heap will not degrade performance as at worst you're going to make it do a table scan, which is exactly what its doing now anyways.

You do need to work out what indexes you want however, if theres something obvious like an identity field then great, other tables (and by the sounds of what you've described it might be a few) will be a lot less obvious where you want to create your clustered index, look at the queries that are hitting that table and work from there.

NOTE: adding a clustered index to a heap takes up (near enough) only the space for fill factor, it will replace the heap, not add the full data set to it (also note that doing this action is faster if the DB is not running its usual flow and you can perform the create index in an 'offline' state

Not the answer you're looking for? Browse other questions tagged or ask your own question.