FAST n Query Hint

Just because a doctor says take two aspirin, taking an entire case won’t make you better. If one is good and two is better, 2 million might not be best.  What the heck am I talking about? We have an application that for one reason and another, has some overly complex procedures and queries. A lot of time and effort has been spent getting these queries to work well. Some of that time & effort came from one Microsoft consultant, Bill Sulcius. He’s a great guy, very smart, very helpful and I learned a lot from him. On a few of the queries that Bill helped tune, he added some query hints, FAST 1 and KEEPFIXED PLAN. Where he added these hints, he could demonstrate, with good sets of data and thorough tests, a real benefit to their use. Excellent. Bill left the project and moved on. Unfortunately, because the project had gone to production and we had already put our very best efforts into it, my team sort of took our eye off it for a while. Oops.

After Bill left, leaving behind two aspirin in the form of those query hints, on a few queries in a few stored procedures, the developers got hold of them. These hints had been introduced because the optimizer was a bit overwhelmed by the complexity of some of these queries and had used HASH or MERGE joins in places where a LOOP join would have been better. The FAST 1 hint pretty much forces a LOOP join, everywhere.  The intent of the hint is to sacrifice time spent in the optimizer trying to find an optimal plan and instead, get a plan that returns the first row as fast as possible, letting the rest of the processing and the rest of the rows come when they’re available. This can be a very useful hint. Unfortunately, our developers looked at it, saw it was useful, knew that, in some, and in their minds, all, joins work better as LOOP’s, so they decided to put it everywhere. I mean, EVERYWHERE. It was on every single SELECT statement in every single procedure in the entire project. Be afraid.

Lo and behold, over the last few weeks, we’ve been getting all kinds of time-outs on queries that have been working “fine” for a couple of years.  Now, understand, “fine” is a very relative word. In this case, it means, the business users weren’t complaining. These queries have had cruddy performance for a long time. I can’t post all the code because it’s way too long and it’s private. I’ll just show some parts of the execution plan.

Here’s part of the plan that is using FAST 1:

If you click on it to see it larger, you’ll see a series of Index Seeks with Nested Loop Joins. The cost relative to the batch is 0%. Nothing could be better. However, this is the FAST 1 execution plan. Behind this plan, you have no idea what is being done to retrieve the rest of the data. In fact, according to Brad McGehee(scroll down to the bottom), there’s a whole second plan masked behind this one, retrieving the rest of the data after the FAST 1 is complete.

If I remove the FAST 1 hint, two things happen. First, the execution plan changes:

 

Looking at this new plan, it appears to be more costly. The percentage of the batch went to 3% and instead of nice nested loops, we’re seeing a merge. However, the number of scans were cut in half from 4822 to 2142 and the reads were the same going from 15151 to 7249. Overall execution time was reduced as well. This is a faster query.

It is possible in similar circumstances, using the types of queries outlined above, to acheive performance increases. The Query Optimization Team at Microsoft explains how in their blog. You should carefully test this in your own code and remember, the execution plan you see isn’t the total picture.