Optimizer Forcing

Some hints can harm performance

If you’re here, it’s most likely because sp_BlitzCache warned you about index or seek/scan forcing.

Index forcing

Index forcing has some pretty harmful side effects. Aside from the fact that it will stop SQL from making missing index requests, your queries will start to fail if the index gets disabled or dropped. It can also cause bad things to happen if someone changes the index definition. Imagine if someone removes a key or included column from the definition that the query needed.

Forcing access type

Some people get really caught up in the idea that seeks are always better, and start forcing them when SQL doesn’t do what they expect. You can also force scans.

The problem in either scenario is that the hint may not always make sense. Sure, it may have been a good idea a few years back, when you only had a few thousand rows, but now that there’s a few million rows, the server is weeping.

Unless you have a really good reason to be using these, you should reevaluate them.