Last night we had a major release that involved a massive data dump and some complex queries. We semi-anticipated the slowness and were able to react quickly to creating some new indexes in SQL Server 2012. However, the site was lagging for quite a long time post-release.

Is there a way to manage and prevent this ahead of time? You can't create indexes before the data is on the production database, but there must be some way to prepare the database.

Part of the problem is not just indexes, but giving the database enough time to get statistics to make the indexes more efficient.

One thought I had was it might have something to do with sharding or replication, or perhaps some sort of db merge.


It's always a tough thing to performance test production systems, companies typically have far lesser systems/hardware for QA/Staging/Dev environments. One method I use is to baseline performance of our QA system before implementing SQL changes, and then check performance after. I am not concerned with the actual times, but the delta of before and after. Unless you have exact hardware/systems between environments, you will be somewhat limited.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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