Blitz Result: Queries Forcing Order Hints or Join Hints

Think you know better than SQL Server?  Think you should be bossing it around?

Your queries are doing just that.  Since the last SQL Server restart, someone’s been using query hints to force orders or join methods.  This part of our SQL Server sp_Blitz script checks sys.dm_exec_query_optimizer_info looking for the number of times order or join hints have been used since the last restart.

While order or join hints may not be as bad as index hints, they’re still risky.  You’re gambling that your forced execution plan is always going to be better than the one SQL Server would come up with.  If you’re just now taking over a SQL Server from somebody else, this means you might have trouble tuning queries by just implementing indexes or changing server settings.

To Fix the Problem

Rewrite your queries to stop using order or join hints.

No, just kidding.  Kind of.  This isn’t a problem you’re going to fix quickly, but you’ll want to work with the developers on this server to find out why they’re using order hints.  Make sure their use is documented, because SQL Server’s engine may have improved since the queries were originally written, and you may no longer need the hints.

If you simply rip out all the hints, you might get better performance – or it might go right down the drain.  Tread carefully here.  To monitor whether things are improving, keep track of the server’s overall health with Perfmon and monitor runtime of queries that you tweak.

Return to sp_Blitz or Ask Us Questions