1

I've been tasked with optimizing a somewhat small Microsoft SQL Server 2008 DB that we all feel is under-performing for its size and whose structure is know to have been abused over the years. Now I'm no dedicated DBA, but I do have enough SQL knowledge to CRUD my way through most problems I've so far encountered so I'm looking for advice here that may fill in some gaps in my knowledge.

1) So my first step will be to take a look at the indexing and the fragmentation thereof. One question is, should I rebuild/reorganize existing indexes or should I drop and recreate them?

My next step would be then be to determine if current keys are indexed and if not, create indexes for them.

2) I've noticed many tables contain columns with foreign key values, but very few contain foreign key constraints on these values. Now I know foreign key constraints aim to maintain the relationships between tables, but should every column containing foreign key values have a foreign key constraint? I feel values used in joins for example, should be constrained so those I will be adding without question wherever I find they're missing. I've read adding foreign key constraints doesn't necessarily improve performance as it results in an extra check during write operations, but indexing these added foreign keys may. What are your thoughts on this?

I'm also researching into adding a maintenance plan that could automatically do index maintenance from time to time and have found some great material to help me accomplish this.

Thanks guys!

JP Damstra
  • 545
  • 7
  • 25
  • In addition to indexes, you have to re-organize the queries. Note that if you have many indexes, they will slow down insert and update operations. – i486 Jan 11 '16 at 09:07
  • 1) rebuild/reorganize. 2) a) foreign keys only enforce data integrity/consistency. 2) b) added index(es) on foreign key columns if you look to speed queries that link master with detail tables. A good read on SO [here](https://stackoverflow.com/questions/836167/does-a-foreign-key-automatically-create-an-index). GL – TT. Jan 11 '16 at 09:18
  • Regarding your last question (what should I be reading) is off-topic on Stackoverflow (read [this](https://stackoverflow.com/help/on-topic), off-topic point 4). – TT. Jan 11 '16 at 09:37
  • 1
    No your first step should be to identify poorly performing queries and operations, take metrics, understand why they are performing poorly, apply changes, and take more metrics to prove they have improved. Foreign keys don't make much difference to writes. They can make a huge difference to deletes but importantly they can provide the optimiser with clues about your data. Is your database mainly about writes (OLTP) or reads (OLAP)? The problem could actually be the application (if you have one - we don't know). But you won't know that until you analyse it – Nick.Mc Jan 11 '16 at 10:02
  • you can also check whether the index statistics are up-to-date. Also try to eliminate the unwanted objects (tables , stored procedures etc...) from the db – bmsqldev Jan 11 '16 at 13:29

0 Answers0