T-SQL Tuesday #158 –Implementing Worst Practices
T-SQL Tuesday is the blog party started by Adam Machanic (b|t) over a decade ago and is now maintained by Steve Jones (b|t) on tsqltuesday.com. Each month, lots of people blog their thoughts on a common topic.
This month’s T-SQL Tuesday is from Raul Gonzalez (b|t) who is asking us to reveal all about our use of Worst Practices, and explain why we still use them. I’m happy to share a bad practice with Raul and everyone else.
My contribution is related to the use of Query Hints.
In general, you shouldn’t push SQL Server to run queries a specific way – you should concentrate on making the environment for running the query as optimal as possible and let the query optimizer work it’s magic.
Trouble is, there are times when no matter what you do, the SQL Server Optimizer gets it wrong. It’s not as bright as you think.
Sometimes, your knowledge of your data means that you know things that the Optimizer doesn’t, resulting in poor performance.
There are things that you can do with tuning a Query – for example, updating Statistics, adding Statistics or re-working the query to break it up but the Optimizer may still be sub-optimal. If you’ve been through these and others, it might be necessary to use a Query Hint to get you over the line.
For example, you might have a query that ends up doing thousands of Index Seeks, followed by thousands of Key Lookups where it’d be more beneficial to just do a single Table Scan.
The reverse of this situation is often the topic of questions on Stack Overflow – i.e. where people expect the Optimizer to use an Index and it does a Table Scan instead. The Optimizer generally has good reason to use the most efficient method possible. So long as you know what you are doing to check the effect of your “bad practice” then it can be worth pursuing.
Force it, Force it real good
I’ve had reason to use Query Hints a bit more recently, specifically “FORCESEEK” and “FORCESCAN”.
So, when is it acceptable to use a Query Hint?
- It Depends
Here’s an example where I might consider using a Query Hint.
- If a query generally takes 3 seconds to run, sometimes takes 1 second but other times goes off for a minute or more, if a Query Hint causes a consistent time of 2 seconds every time that it’s run regardless of conditions then it might be considered worth using.
Implementation
It might just be a case of hard-coding the Query Hint into the SQL Code or Stored Procedure. If it’s a Parameter Sniffing issue (I hate that term btw), then you might build a Dynamic SQL Statement that has the Query Hint going in under specific conditions. This post is not to tell you how to do it, just to tell you that sometimes it can be OK.
Future Problems
Any changes to the Database platform should cause you to review your decisions. Upgrading SQL Server may cause changes that mean that your Query Hint is preventing the Optimizer taking advantage of the Upgrade. It may no longer perform as it did or indeed it could make performance worse.
You are adding Technical Debt to the system in this way.
I bet you thought I was going to talk about shrinking Databases.
Hope this helps,
Nigel.
