Blog

Crystal meth helps you lose weight and feel better about yourself.

It’s true.  Wikipedia says so.  Sure, the side effects of methamphetamine are more than just anorexia and euphoria, but if you’re looking to be an upbeat, skinny person, meth gets the job done.  SQL Server has a couple of similar features that might make your queries faster today – but they’ll make you sing the blues tomorrow.

Forcing Index Use with Index Hints

If you’re not happy about the indexes SQL Server chooses to use when executing your query, you can show it who’s boss by forcing it to use specific indexes like this:

SELECT FirstName, LastName
FROM dbo.Employees WITH (INDEX (IX_Lname_Fname))
WHERE LastName IN ('Fritchey', 'Shaw')

This forces the SQL Server engine to use the index IX_Lname_Fname as part of the execution plan rather than looking at all of the available indexes and picking what it thinks is the best choice.  You’re telling SQL Server that you know more about the schema and the data than it does.

Whenever you hard-code an index into a query, you open up a can of performance tuning worms:

  • If you add a better index, the engine won’t use it – you’ll be tied down to that one index until you touch every hard-coded query
  • If that index gets dropped, your query will be in a world of hurt – you can’t consolidate indexes when you run into performance problems
  • DBAs can’t do performance tuning without the developer recompiling the app
  • When SQL Server is upgraded, it may be able to build a better query plan – but not if you forced a specific index

You might get a faster query today – but you can’t react as quickly to the problems of tomorrow.  The engine has another query plan trick, too.

Forcing Execution Plan Guides

Plan guides don’t just force SQL Server to use a specific index; the ambitious DBA can tweak all kinds of things about how the query is executed.  You can force things like table scans.

The nice thing about plan guides is that you can change them without recompiling your code and changing the queries.  The DBA can do performance tuning without getting developers involved every time.

Problem is, when you’re troubleshooting a query that isn’t behaving the way you’d expect, you can see index hints just by looking at the query.  Plan guides, on the other hand, are transparent – they’re happening inside the engine, behind the scenes, and you have to know to look for them.

But wait – it gets worse.  Plan guides exist at the server level.  When you’re troubleshooting the same query on development, QA, and production servers, and you’re getting wildly different results, do you stop to think if there’s a plan guide working behind the scenes?  Are your plan guides set the same across all three servers?  Are you sure?

SQL Server 2008 Query Performance Tuning Distilled

SQL Server 2008 Query Performance Tuning Distilled

A Better Solution: Learn More About the Engine

Rather than telling SQL Server who’s boss, consider picking up Grant Fritchey’s excellent book, SQL Server Query Performance Tuning Distilled.  It explains:

  • How statistics influence the accuracy of the execution plan
  • How to build better, more selective indexes that the engine will be more likely to use
  • How to understand if the execution plan is the problem, or just a symptom

Yes, we also cover this in our book SQL Server Internals and Troubleshooting, but if you’re the kind of person who’s considering index hints and plan guides in production, Grant’s book dives into more detail on these features.

↑ Back to top
  1. Out of curiosity: is there anything good that may come out of these features?And if not, then why were they ever implemented by MS into SQL Server?

    • Feodor – two questions there. First, yes, good things can come out of these features but only if you really know what you’re doing, and if you have the time to continually follow up on your work to make sure it’s still correct.

      Second, I don’t have inside info as to why they were implemented, but I bet it was done by user request. There’s high-end DBAs who can actually put these features to good use, but they’re very, very, very few and far between.

  2. Both these features come handy when you have to deal with bugs in the engine. You have to agree there are flaws in the engine, not exactly bugs, but flaws in the algorithms the cost based optimizer uses. Every greedy algorithm is fast but suboptimal. Thats where I’m using hints.

    Just this week I had to hint a query with FORCESEEK for 1000 fold speed up, just to find out that some clients use SQL2000 where this hint is not available so only INDEX hint could speed it. Yes, I could probably revise the indexes on the base tables because update stats (with fullscan) did not yeald anything, but this is a trial and error task that usually takes a lot of development time.

    cheers,
    </wqw>

    • WQW – true, but what happens when a fix comes out and there’s a new version? If there’s an improved query plan, you won’t get it if you’ve forced a particular one. It means you can never stop rechecking your code with every new SQL Server service pack and hotfix.

      • Your point about the possibilities of better optimizations in future releases is true, BUT …

        if you have to choose between using hints to produce stable and efficient plans versus hoping that a future version of SQL Server will fix the problem, which would you really choose?

        I’d rather be efficient (ie, nearly optimal) all the time and get to sleep at night rather than having to live with inconsistent performance for queries that waffle between plans when they recompile. And revisiting hinted stored procedures upon upgrade shouldn’t be that monumental of a task if you don’t use hints (or plans) too much.

        • Chris – unfortunately, if you use index hints to produce stable and efficient plans, you won’t be sleeping at all after you bring in a DBA. See, the DBA needs to build new indexes, consolidate existing ones, and remove the ones that aren’t performing. If you hard-coded index names into hints, you’ll be constantly revisiting that and rereleasing your code every time the DBA needs to tune indexes.

          It sounds great – right up until you learn that it doesn’t, and by that point, you’re in a world of hurt. (And sleeplessness.)

          • None of your worst-case scenarios are inevitable – they are just things you would have to deal with if you implemented this strategy stupidly.

            For example, some places may have workflow division like that, and they would need to redress your concerns with good communication between teams, and you’d hope that the system DBAs could be taught to scan the T-SQL code base for index references before doing drops. That’s easy, so your point is valid, but only to say that people need to do this right, not that people shouldn’t do it.

            In our case (and other places I’ve worked – 7 jobs), we recognize that the application DBAs (the T-SQL developers) are the best experts for tuning and indexing decisions. Our system DBAs (which it sounds like you are referring to) could get fired if they did what you are suggesting without talking to us first. We run our own missing index / unused index reports, and before we drop an index we have a due-diligence checklist that goes beyond “it hasn’t been used in 6 months” and also includes checks for hints.

            So, if you don’t mind me saying so, you’re presenting a straw man, and one that’s very easily overcome. And if the proof is in the pudding? I’ve been working with SQL Server since 4.21a, and I think that the number of problems we’ve permanently solved with hints is probably over 100, and the number of problems we’ve introduced is 0. Not because we’re smarter than everybody else, just because we replaced the FUD-y “never use hints” with the much wiser “use hints judiciously and safely.”

            By the way, in my current job, I have 5 T-SQL developers reporting to me, and another 4 at least in our office, with probably 20 at another site. And I would say that about one-third to one-half of them understand how to use hints wisely and effectively. Not quite as “few and far between” as you might think?

            Cheers,
            Chris

          • Chris – just as you say you’ve never run into that problem, I can say that I’ve very frequently run into this problem. Just because it’s always worked for you doesn’t mean it’s always worked. Best of luck, though!

  3. Gotta love FUD. Thanks, Brent!

  4. I actually won a copy of Grant’s book at the last HASSUG meeting and am about 100 pages into it. I can’t believe I never picked it up before that. Incredibly useful information.

  5. Hi! Can you help-me?

    My English is basic.
    Today, I have this situation:
    select COlumnA, ColumnB, ColumnC,ColumnD,ColumnE,
    ColumnF,ColumnG
    from Table A
    where Data >= getdate()-5 and Data < getdate() and Req = 45

    This table is very large and has a clustered index on the column Data. This query use index for the Column Req and is very slow.
    When I force to use the index to data column is very fast.
    How can I resolve this without force the index?

    Thanks

  6. And if you explicitly use an index name in your code and then some fellow with good intentions comes along and removes a duplicate index or renames one from the DTA 100 character default rubbish that someone blindly implemented, it will break the code.

    Today I was that DBA :(

  7. I started to actively consider a plan guide today (this sounds a bit like a confessional, or we’re at an AA meeting). It’s a stored proc that goes massively, painfully sideways every few weeks or so, because of a classic parameter sniffing issue. Sure, a better option would be fixing the query or redesigning indexes, but its a vendor database and we’re not allowed to touch that sort of schema, but the concept of plan guides I think is considered detached enough to be permissible. We’ve band-aided it by scheduling sp_recompile and an execution of the proc with known good params after the index/stats maintenance, but that’s a messy solution and the data could change making the “known good” parameters give a bad plan, potentially. So that’s why a plan guide seemed to hold some promise.

    Now all that said, it didn’t work because of a curious syntax issue (the @stmt parameter was passed the exact statement, but it was “IF EXISTS(select something)” without the other half of the IF statement and it fails syntax checking. Weird issue, I tried it any number of ways including with the statement gleaned straight from Profiler, and it looks like it isn’t going to work in this situation.

    But I do agree, keep things simple. I think things like this can be beneficial in limited scenarios, but you have to be very, very deliberate about throwing that switch. And if you do implement “nonstandard” solutions like this, document the hell out of it for your fellow and future DBAs!

  8. Hi, Brent. Curious if you’ve seen this sort of thing before:
    We’re running into deadlocking issues on very small tables (like, 200K and below with around 1,650 rows).
    I’m pretty well versed at troubleshooting deadlocks, but the customer isn’t letting me at their db, which is a lot like fixing a car in someone esle’s garage without being able to actually ENTER the garage. Frustrating.

    At any rate, we’re seeing a clustered index scan in the plan that we’re being given, and I’ve determined that the users table is so small, SQL Server just isn’t bothering to use the indexes that are there.

    The developer is insisting that plan guides might be the answer.

    Just curious to hear your general feedback. Forcing an index might lock fewer rows and alleviate deadlocks, I suppose, especially when we’re selecting based on a unique PK.

    • Joe – I’d rather see the execution plans and the deadlocks involved. Generally speaking, plan guides would be the wrong way to solve deadlocks, though.

  9. Hi Brent. I have to respectfully disagree with you and here is why: While in most cases SQL Server picks up the best execution plan, there are plenty of instances in challenging and complex environments when it cannot. There are instances when rewriting sql or tuning statistics simply does not help. In those instances using both index and join hints improves performance 10 and 100 – fold. …And very often it is critical to the business. You make a comment saying that “If that index gets dropped, your query will be in a world of hurt”. Come on – we both know that any half-decent DBA should never drop indexes that are being used without making sure that dropping them won’t break anything. And commenting on your other point: Can the new release of SQL Server provide on its own even a better performance without using the hints ? – Possibly but that is speculative. What is not speculative is that right now we have a performance problem and SQL Server cannot solve it on its own. Of course, what goes without saying is that using the hints should be done only as a last resort and left to highly skilled DBAs. Regards, Jacob Margulis, Senior SQL Server DBA.

    • Jacob – I’ll pick just one line in there to question. You wrote:

      “Come on – we both know that any half-decent DBA should never drop indexes that are being used without making sure that dropping them won’t break anything.”

      There’s a world of difference between “should” and “doesn’t.” I know I shouldn’t eat donuts, I should exercise more, and I shouldn’t have bought this iPhone 6 Plus. But back here in reality…

      • Hi Brent. Thank you for the quick response ! – I know you are a busy man. I do not believe though that I follow you on the philosophical level: The things that you mentioned (eating donuts, exercising, etc.) are personal choices that we are free to make in a free society. Being a DBA, on the other hand, is a job that comes with responsibilities. One of them is to make sure that you do not drop an index that is being used (and we both know that it is very easy to check what indexes are being used and what indexes are not). If a DBA does not fulfill his basic responsibilities, then he or she should simply be dismissed. In my very long career as a dba I do not recall ever making that mistake. My main point as a dba remains: sometimes there is no realistic alternative to using Forced Hints other than suffer degraded performance at business expense. Best regards, Jacob Margulis.

        • Jacob – being a DBA is also a job that comes with choices. Some DBAs have more work than they can possibly take care of during their limited work hours. Sounds like you’ve got plenty of time and are able to attend to even the smallest of details, though, and that’s great for you!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php