Can changes to cardinality optimization be made without taking users offline? I have an old SQL Server database that has been migrated a few times since &lt; 2005. Some queries are running very slowly. I have been advised that the following changes could help performance:


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


Can these changes be made without taking users offline?
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.