2

I have an old SQL Server database that has been migrated a few times since < 2005. Some queries are running very slowly. I have been advised that the following changes could help performance:

  1. Increase the database compatibility level from 90 (2005) to highest possible (the software itself does support the highest).
  2. Set Legacy Cardinality Estimation to ON.
  3. Set Query Optimizer Fixes to ON.

Can these changes be made without taking users offline?

3

Yes the changes can be done without taking users offline. But please note that this is ALTER DATABASE command which will lock the database so if any schema changes are happening it would be blocked. It would be better to run this query when load is relatively very less, just so that it finishes very quickly.

Also note that when you enable legacy cardinality estimation the procedure and plan cache will be flushed and new queries would be recompiled using old CE. This will force SQl Server to do some extra work and you might see slowness for a very small period.

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.