Reason To Use Optimizer Hints

While investigating performance problems within an application recently I carried out some tests using SET SHOWPLAN ON.

I had a query like this within a stored procedure:

SELECT MAX(X) FROM Y WHERE Z LIKE @MYVAR

Where @MYVAR was passed in. I discovered that SQL Server did a Table Scan even when Z had an index on it. A problem with 200,000 rows!

If I said

SELECT MAX(X) FROM Y WHERE Z LIKE 'HELLO%'

(i.e., used a constant instead of a variable) SQL Server did use the index correctly and did not do a table scan.

I got around this by rewriting my statement:

SELECT MAX(X) FROM Y (INDEX=MYINDEX) WHERE Z LIKE @MYVAR

in other words by manually specifying the index I had created on the Z column.

Hope this helps someone.