2 Features of SQL Server You Should Avoid

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')

The kids with Erector Sets were all jealous of Timmy.

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

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.