According to Not-Australians, there used to be a trace flag that would get queries to ignore any supplied hints. It doesn’t work anymore, which sucks, kinda.
Because people do lots of stupid things with hints. Real stupid things. Things you wouldn’t believe the stupid of.
Let’s say, for example, hypothetically of course, that your front end application would add an index hint to every query.
That index hint may or not be helpful to your query in any way. But there it is.
Let’s also posit, using the very depths of our imaginations, that the front end developer was unlikely to change that behavior.
We’ve got a couple indexes:
CREATE INDEX r ON dbo.Users(Reputation) WITH(MAXDOP = 8, SORT_IN_TEMPDB = ON); CREATE INDEX c ON dbo.Users(CreationDate) WITH(MAXDOP = 8, SORT_IN_TEMPDB = ON);
And we’ve got a query that, via an index hint, is being forced to use the wrong index.
DECLARE @Reputation int = 2; EXEC sp_executesql N'SELECT * FROM dbo.Users WITH (INDEX = c) WHERE Reputation = @Reputation;', N'@Reputation int', @Reputation;
The ensuing query plan makes no sense whatsoever.
The things are all backwards. We scan the entire nonclustered index, and do a lookup to the clustered index just to evaluate the @Reputation predicate.
The idea is bad. Please don’t do the idea.
There are two things we could do here. We could hint the query to use the index we want, sure.
But what if we change something about this index, or add another one to the table? We might want the optimizer to have a bit more freedom to choose.
I mean, I know. That has its own risks, but whatever.
We can add a plan guide that looks like this:
EXEC sp_create_plan_guide @name = N'dammit', @stmt = N'SELECT * FROM dbo.Users WITH (INDEX = c) WHERE Reputation = @Reputation;', @type = N'SQL', @module_or_batch = NULL, @params = N'@Reputation int', @hints = N'OPTION(TABLE HINT(dbo.Users))';
If we were writing proper queries where tables are aliased, it’d look like this:
EXEC sp_create_plan_guide @name = N'dammit', @stmt = N'SELECT u.* FROM dbo.Users AS u WITH (INDEX = c) WHERE u.Reputation = @Reputation;', @type = N'SQL', @module_or_batch = NULL, @params = N'@Reputation int', @hints = N'OPTION(TABLE HINT(u))';
When we re-run our query, things look a lot better:
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- SQL Server 2017 CU 30: The Real Story With SelOnSeqPrj Fixes
- Getting The Top Value Per Group With Multiple Conditions In SQL Server: Row Number vs. Cross Apply With MAX
- Getting The Top Value Per Group In SQL Server: Row Number vs. Cross Apply Performance
- Multiple Distinct Aggregates: Still Harm Performance Without Batch Mode In SQL Server