THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Override the optimizer or give it an easy task?

When your query must always return in a certain amount of time, trusting the optimizer might not be the best choice. Suppose your select must return in one second no matter what, and it usually returns in 50 ms, but sometimes, once in 100K calls, it takes full five seconds to complete, and clearly such query does not meet the requirement to return in one second. Sounds familiar? Sure, optimizers do behave like this from time to time.  It is very tempting to use hints or even plan freezing. However, neither hints nor plan freezing are long term maintenance-free solutions, and the reason is simple: what today looks like a great plan might at some later time become counterproductive.

In cases like this

index covering really shines.

Scanning a range of a covering index is a very simple and efficient plan. Even better, it is one and the same plan for all possible combinations of parameters, so you may not expect unpleasant surprises from a confused optimizer. Of course, if the amount of data increases, the response time may increase too, but you may monitor it and react accordingly if needed. If you do not load huge amounts of data, your response time will not jump from 50 ms to five seconds any time soon, as long as the optimizer chooses the same range scan over and over again.

Responding to the

recent post by SQL server MVP Tibor Karaszi, I think that not all queries are born equal - some must respond in a certain time even if that slows down other activity on the server. This means that in some cases it is OK if index covering slows down your modifications, provided that the modifications have lower priority.
Published Tuesday, June 16, 2009 10:43 PM by Alexander Kuznetsov



jchang said:

the key for a fixed plan is the data distribution statistics and the compile parameters. MS gives us the OPTIMIZE FOR option, setting the parameter values used to generate the plan. But if statistics are updated, the distribution may change, including whether you are on a key row, or a range row.

You could disable statistics on that table, which has other consequences.

I have also practiced the technique of forcing a specific plan, and I do not mean just sticking on join, order or index hints. I mean the specific join inner and outer source combination.

But it does not seem like many people are interested in this, none that I know of actually.

We could ask MS for the option in UPDATE STATISTICS to force a certain value to be a key row, not a range row. this would make the OPTIMIZE FOR more stable.

If managing SQL Server performance was easy, would you be making the big money? Be careful about asking MS to make SQL Server too simple to manage.

June 17, 2009 12:59 AM

Alexander Kuznetsov said:


I am not a consultant, I design and develop a system that provides data to my clients. I make better money and have more time when my system is healthy and stable. I'd rather have less performance tuning, because it is an overhead - I am paid for health, not for illness. So from where I sit it is cheaper to use trivial index covering than to use advanced techniques like plan freezing, if plan freezing is what you are speaking about. Right now to me it is more important to avoid emergencies than to squeeze all possible performance from my hardware.

June 17, 2009 9:35 AM

Joe said:

sorry, I saw Override the optimizer and jumped in,

on the matter of covering indexes, it is a good idea to distinguish included columns versus fat key. back in the good/bad old days of S2K, building fat keys was tricky in when to insert the cluster key before non-search columns.

anyways, covering indexes preferably with judicial use of included columns solves many problems. But do pay attention to space used by really long strings in big tables, and remember the 4:1 ratio for key lookup row versus scan page.

but there are many problems not solved by covered indexes, and that is why we have plan freeze, and good table design

June 17, 2009 11:15 AM

Alexander Kuznetsov said:


Can you please elaborate on 4:1 ratio for key lookup row versus scan page?

June 17, 2009 11:19 AM

Rick Caminiti said:

I've been doing this with the INCLUDE clause.  What's the difference between indexing the ORDER_DATE and AMOUNT columns together vs indexing ORDER_DATE and INCLUDing the AMOUNT column?


June 17, 2009 12:30 PM

Alexander Kuznetsov said:


Your index is quite narrow, so you will not see much difference one way or another. Still, the index without INCLUDE clause might have more levels, so lookups might take more time. You can benchmark and see for yourself.

June 17, 2009 2:17 PM
New Comments to this post are disabled

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on and Currently he works as an agile developer.

This Blog


Privacy Statement