Take a Hint About Query Hints

Last month, I told you that I was scheduled to present my first seminar on SQL Server 2012, and I even gave you a list of new features that I thought I would talk about. However, I ended up getting very sick the day after Thanksgiving and had only a few days to prepare. Thanks to Aaron Bertrand’s copious blog posts about new SQL Server 2012 features I had a head start and was able to deliver a relatively successful three-hour session on December 14. I mentioned many of the new engine features and included eight short demos and two longer demos. The more involved demos introduced the contained databases and columnstore indexes features.

Related: Do you Need a SQL Server Query Hint?

However, when planning this month’s commentary, I went back and reread what I wrote last month and realized I hadn’t even mentioned one of the new features I had thought would be the easiest and most straightforward to talk about—the new index hints, including FORCESCAN and enhancements to FORCESEEK. I have always found optimizer hints to be a very interesting topic for a couple of reasons. Not too many versions ago, the SQL Server team at Microsoft seemed to imply that as the optimizer got better and smarter, the need for hints in your queries should practically disappear. Instead, they started adding more and more hints so that now there are more than two dozen table hints and almost the same number of query hints. I actually call the query hints “option hints” because they’re specified in the OPTION clause, and I think that all hints are query hints. Plus, as of SQL Server 2008, most of the table hints can be specified as an OPTION hint. So it’s pretty obvious that hints aren’t going away any time soon because more and more are added in each new version and sometimes in between versions. (In fact, the FORCESCAN hint that many people list as a new SQL Server 2012 feature was actually added in SQL Server 2008 R2 SP1.)

The second reason I find the whole subject of hints to be fascinating is that at first glance, a lot of less-experienced SQL Server users think that hints will make their jobs easier. They think it is easier to get the performance they’re looking for in their queries if they can specify how SQL Server should process those queries. But, of course, those of you with just a bit more experience are well aware that the truth is just the opposite. To use hints effectively, you really need to understand a lot about how SQL Server works and why one plan might be better or worse than another. In fact, some of the worst performance you might ever encounter could arise when a hint is used to force SQL Server to use an index seek in a case in which the query is going to return a very large number of rows. Many people think that one of the worst steps in a query plan is a table scan, but in my experience, using a nonclustered index seek inappropriately can give far worse performance. Here’s quick example. In the AdventureWorks2008 sample database, there’s a table called Sales.SalesOrderHeader with a nonclustered index on the column CustomerID. If we run a query that returns only a few rows, the nonclustered index is a good choice. The following query returns 33 rows (out of 31,465 in the table). uses the nonclustered index, and requires 112 logical reads:

SET STATISTICS IO ONSELECT * FROM Sales.SalesOrderHeaderWHERE CustomerID < 11011;

However, if we look for CustomerID values less than 12022 instead of using the CustomerID value 11011, SQL Server returns 3028 rows. It scans the table and requires 686 logical reads. If we saw that table scan in a query plan and thought there was something wrong, we could force SQL Server to use an index seek, by using index hints as the following query shows:

SELECT * FROM Sales.SalesOrderHeader WITH (FORCESEEK, INDEX (IX_SalesOrderHeader_CustomerID))WHERE CustomerID < 12022;

We would get an index seek as requested, but the number of logical reads is 9289, more than 10 times the number of pages in the table! Because nonclustered index seeks are usually only useful when the number of rows returned is quite small, forcing SQL Server to use this index is not a good idea and is much worse than allowing SQL Server to choose a table scan. The FORCESEEK hint was added in SQL Server 2008 and enhanced in SQL Server2008 R2 SP1 to let you additionally specific the column or columns to be used for the seek if the index is a composite index. It’s also possible that SQL Server might choose to use an index on its own when a table scan might have been better. In SQL Server 2012 you can force SQL Server to perform a scan either on the table or on a particular index. To force a scan of an index, the query above could replace the FORCESEEK with the word FORCESCAN. To force a scan of the table, you can use the construct INDEX(0), as in the following:

SELECT * FROM Sales.SalesOrderHeader WITH (FORCESCAN, INDEX (0))WHERE CustomerID < 11011;

Of course, because this table has a clustered index, and a clustered index scan is exactly the same as a table scan, we could also get the same behavior by specifying the name of the clustered index, as the following shows:

SELECT * FROM Sales.SalesOrderHeader WITH (FORCESCAN, INDEX (PK_SalesOrderHeader_SalesOrderID))WHERE CustomerID < 11011;

There are many more subtleties to using index hints, both in the enhancements in SQL Server 2012 and in the use of hints in general. But the biggest hint I can give you for effective use of hints is to learn all you can about tuning SQL Server queries and about the SQL Server optimizer. Also, search for blog posts about SQL Server hints; I recommend starting here.