Let me ask you a question: do you want to boss the SQL Server query optimizer around?
If you answered no: good. You’re willing to let the query optimizer do its job, which is to find the least expensive way to run a query, as quickly as possible.
If you answered yes: you’re a brave person.
Maybe you’ve hit upon the perfect index for a specific query, but for some reason the optimizer won’t use it. But you know it improves performance. How can you make the query optimizer listen to you?
Index hints are a powerful, yet potentially dangerous, feature of SQL Server.
Let’s look at an example. I’m working with AdventureWorks2012. The database has two related tables, HumanResources.Employee and Person.Person. They are related through the BusinessEntityID column. I want to retrieve information about the users and their logins.
|
1 2 3 4 5 |
SELECT PER.FirstName, PER.LastName, EMP.LoginID FROM HumanResources.Employee EMP INNER JOIN Person.Person PER ON PER.BusinessEntityID = EMP.BusinessEntityID; |
Let’s look at the execution plan.
A nonclustered index seek is performed on Employee.AK_Employee_LoginID, a nonclustered index on the LoginID column. A clustered index seek is performed on Person. Note the cost is 0.217439.
I notice that the Employee table has another index, PK_Employee_BusinessEntityID, which is on the BusinessEntityID column. I want to force my query to use this index instead. I can do this by using the WITH (INDEX) hint.
|
1 2 3 4 5 |
SELECT PER.FirstName, PER.LastName, EMP.LoginID FROM HumanResources.Employee EMP WITH (INDEX (PK_Employee_BusinessEntityID)) INNER JOIN Person.Person PER ON PER.BusinessEntityID = EMP.BusinessEntityID; |
Let’s look at this execution plan.
Now, a clustered index scan is performed on Employee. Note, though, that the query cost has increased – to 0.220402.
In your case, the index you force the query to use might get the better cost – for now. But what happens when more data is added to the table, and statistics change? What happens if you update SQL Server, and the query optimizer changes?
Eventually, the index you’re using may not be the best one for the job – but SQL Server is going to continue to use it anyway. You’ve told it to do so, and it’s doing to keep doing it.
Think about how you would get rid of it. Is there an easy way to search all of your code – application code, stored procedures, report definitions – for this specific index hint, to remove it?
Another point to consider is what would happen to that code if the index was disabled or deleted? Would it continue to run? Let’s give it a try. I issue a disable index command.
|
1 |
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee DISABLE; |
Then I run the same query as before, and what happens? I get an error:
Msg 315, Level 16, State 1, Line 1
Index “PK_Employee_BusinessEntityID” on table “HumanResources.Employee” (specified in the FROM clause) is disabled or resides in a filegroup which is not online.
Index hints can be a powerful feature, but use with caution. Look for other ways to optimize your query – perhaps a different index can be created, or your query can be rewritten. If you can’t find a better way, document the use of the index hint and its purpose. When you upgrade SQL Server, test whether that index is as effective.
Remember, the query optimizer does its job really well – let it.
25 Comments. Leave new
Please tell this to Microsoft. Have you seen Sharepoint’s queries? Here’s an example (btw, SQL recommends an index for 90% improvement, but hey, what would it know.. MS Spoint dev: INDEX=AllUserData_PK, there will never be any better index. EVER)
JOIN AllUserData AS t2
WITH
(
NOLOCK,
INDEX=AllUserData_PK
)
AND (t1.SiteId=@SITEID)
ORDER BY UserData.[tp_ID] DESC OPTION (FORCE
ORDER, MAXDOP 1)
SharePoint is like the moody teenager of Microsoft products – whatever it’s supposed to do, it does the exact opposite!
In this sentence “A nonclustered index seek is performed on Employee.AK_Employee_LoginID, a nonclustered index on the LoginID column.”
Shouldn’t “seek” be “scan”?
One place where I get boxed into using index hints is where a stored proc uses table vars and the optimizer assumes there is just one row when in fact there are many more. Any suggestions? I am not a fan of hints either…
Switch your code from using table variables to temp tables! There aren’t a lot of other options. Table variables stink sometimes – and this is one of those times.
Add OPTION(RECOMPILE) to the queries with table variable.
The issue with TV in stored procedures is that the query plans are created when the TV is empty. OPTION(RECOMPILE) forces a new execution plan at the time the query is executed – which creates plan optimal for the TV’s row count.
They are there for a reason. The reason should be the exception rather than the rule.
But I have found specifically filtered indexes often being ignored and have occasionally had the need to use a hint (see how that went? Not a rule to use them!)
Firstly, apologies for posting on an old topic.
We occasionally need to use index hints, due to the large amounts of data in various tables, but we have also seen SQL ignoring these index hints at times.
Have you come across this before?
Alex, it’s going to be hard to give you an answer without more background. You should go to dba.stackexchange.com and fill out a question. Lots of smart people are over there checking it all the time. If you don’t get a good answer in a day or two, post the link here and I’ll see if I can help out!
Thank you for coming back to me.
We have also posted to sqlperformance.com and had a few responses back.
Alex
Agree wholeheartedly but there is a genuine place for index hints – when you have a badly performing query and you want to manually try different query plans.
In this case – you use the hints to find the best plan (hopefully) then you figure out why the optimiser isn’t using this plan.
often is a case of re-updating stats or use of a judicious covering index to overcome the issue.
Speaking of hints, don’t get me started about the noexpand hint – grrr.
it forces you to write breaking code to use the materialized view.
Yes, hints can be great – in a development environment – for testing indexes!
Another reason to use index hints is to avoid deadlocks. E.g. we have a deadlock on clustered index, but even adding a non-clustered index in to a table structure doesn
t make any effect. Query plan still using clustered index and deadlock occurs.s difficult to change complex internal logic(change operation sequence, use transaction/lock hints etc.) so you can carefully use the index hint IMHO.Of course there are always could be another way to evade deadlocks, but sometimes it
Agree that Index hints are not a good idea. Question is what to do when you have an excellent index that the optimiser refuses to use?
I’ve a query that does an Index Seek, Nested Loop Join and then a Key Lookup taking 30 Seconds. When I create a covering index (with or without INCLUDE to cover all columns) the optimiser ignores it, DESPITE it being a SQL Optimiser recommended index. When I use the index hint it does an Index Seek and runs sub-second. See this article on SSC: http://www.sqlservercentral.com/Forums/Topic1706485-2799-1.aspx?Update=1
Interested in your comments.
Leo – as much as we’d love to do personalized query troubleshooting in blog comments, this is a little beyond the scope of what we can do.
one comment on an aspect of this that isn’t covered here
select t1.*,t2.* from t1 join t2 on t1.pk=t2.someKey where t2.someTime >=@param1 and t2.sometime<@param2
has a tendency to estimate the select sqrt tableRows worth of info (in my case 300k rows, rather than 88 when only 1 day of data was wanted); i resolved this issue 2 ways: 1 with an index (NC_someTime) , and the other was with an option (recompile) . runtime went from ~4 mins, down to 12 seconds with the index hint, then down to 3 seconds when i told it to recompile.
The moral of the story; once again, once it has the right assumptions, the query optimizer out-did the dev.
(we don't have 10 years of data, the discrepancy between 300k and 88 is that the different query plan with the correct info applied one of the other predicates differently, nested loops joined onto the range, rather than table-scan with post-filter)
Cardinality anomalies can make index hints very useful. I once inherited a 7TB db on SQL2008 with a table containing 800+ million rows of multi-national e-commerce data. 80% of the data was US and the other 20% was international. We had an index optimized for US data and another used for a different purpose that happened to work for the international data. Since the query optimizer only sampled large tables when updating statistics, we would occasionally see the optimizer choose the international index if the sampling ‘caught’ mostly international data. Performance would go from sub-second to over 4 minutes in a 24×7, global e-commerce application. Since this application was handling customer payments, it would effectively take the site down. Adding a query hint to force the optimizer to use the US index solved the problem and it worked for several years as the cardinality changed slowly and didn’t balance out while I was at the company.
What are your thoughts on using more generic hints like FORCESEEK, HASH JOIN, etc.?
When I see folks using those, I tend to open up query plans and ask folks if they can explain why SQL Server is choosing to do what it’s doing. If they can explain why SQL Server is doing it, where the row estimates come from, why the estimates are wrong, etc., then I’m fine with the use of hints like that.
But between you and me? Most of the time, they don’t know. 😀 In which case, I say it’s probably not a good idea to boss an app around if you don’t even understand what it’s doing.
Can you do conditional hints?
Can you elaborate on what you’re trying to do?
Depending on say two different parameters that come through a stored procedure, either can be null. Whichever isn’t NULL use a specific index and vice versa for the other parameter.
Sure, absolutely, dynamic SQL is great for that kind of thing.
I ran into an issue where SELECT with a TOP clause would refuse to use an index if I used it with a variable, yet it would work fine if I used it with a literal.
Simplified:
CREATE INDEX I_tbl_column1
ON tbl(column1)
WHERE column1 IS NOT NULL;
CREATE INDEX I_tbl_column2
ON tbl(column2)
WHERE column1 IS NULL;
DECLARE @batch int = 1000;
SELECT TOP(@batch) *
FROM (
SELECT TOP(@batch) *
FROM tbl WITH (READPAST, UPDLOCK, ROWLOCK)
WHERE column1 < @condition1
ORDER BY column1
UNION
SELECT TOP(@batch) *
FROM tbl WITH (READPAST, UPDLOCK, ROWLOCK)
WHERE column2 < @condition2 AND column1 IS NULL
ORDER BY column2
) t;
As is, ROWLOCK does not work, it locks the entire table (I do realize it can theoretically lock (@batch * 2) rows, but I am testing with @batch being way lower than total rows). If I replace @batch with a literal, query optimizer uses both indexes, and only selected rows are locked. Using index hints works too.
Am I doing something wrong? Am I stuck between using a literal instead of a variable or providing an index hint?
You don’t usually want to parameterize top, no: https://www.erikdarlingdata.com/sql-server/a-parameterization-puzzle-with-top-part-1/