Question 1

Answer 1

Transcript

Question

When should you use query hints in SQL Server?

Suggested Answer

Let’s answer question number one. This is kind of a tricky one because if someone asks you about this, you don’t really know, do they like query hints or do they hate query hints?

It could go either way. They could have had really bad experiences with hints or they could use them all the time. With this kind of question you want to tread in the middle and you want to offer a little bit of insight either way.

I would answer this something like this. I evaluate query hints if I have a performance problem with a query and I need to get a stable, well performing plan.

Now query hints do limit flexibility for the SQL Server optimizer, so when possible I prefer to get a stable, high-performing plan just with TSQL rewrites without hints and with proper indexing.

But there are some situations where we need to use a hint to get a stable execution plan.

Now they may ask you about those more situations if you talk about that, so if you say things like that, “of some situations,” have an idea in mind.

If it isn’t one you’ve actually been through, you could even talk about one that you’ve seen in a video where I saw a video about parameter sniffing in SQL Server. Or sometimes a plan would compile fast and sometimes it would compile slow. In a situation like that, perhaps a query hint might stabilize, we’d have to test, to get the fast execution plan while we worked on a TSQL rewrite.

You could work even this class into your answer if you wanted. Don’t ever fake having had an experience, but having the experience of taking a class and learning about it is an experience you can absolutely share in an interview context as well. So feel free to use that and make that experience worthwhile for you also.