Siusic Dot Com

I was told by various documentations and technical articles that SQL Server Query Optimizer does a good job on selecting which index to use when establishing

a query execution plan. And it is in rare cases that you will find justification to use index hint. I believe I encountered this kind of rare cases a number of times over the years. Most recently this is the situation that I countered.

I have the following query:

select a.*, b.*
from a with (nolock)
join b with (nolock) on a.id=b.id
where
b.col1=’some value’

There are non clustered indexes built on the id columns of both tables. And there is a non clustered index built on the column col1 of table b. The query runs really fast. It returns about 200 rows of data almost instantly when it runs. I want to limit the result set to return just one record and I know I can achieve it by adding condition b.col2=’some value’ to the where statement.

select a.*, b.*
from a with (nolock)
join b with (nolock) on a.id=b.id
where
b.col1=’some value’ and
b.col2=’some value’

There is an index built on b.col2 also. However when I add that condition to the query it took forever to return. I didn’t border to fine the cause because the database was maintained by software vender by contract. The only thing I want is to get my information quick and I knew without the condition on b.col2 the query ran quick. So Query Optimizer must be selecting a different index when I added the condition on b.col2. To force it to use the index on b.col1 I try the following.

select a.*, b.*
from a (nolock)
join b with (index(b_col1_index),nolock) on a.id=b.id
where
b.col1=’some value’ and
b.col2=’some value’

And yes this query return the row that I want almost instantly

Related Posts:

The Ultimate Strategy of Tuning Store Procedure Performance

MySQL Bugs

How to Separate Data and Indexes of a Table into Different Files

Trouble Shooting SQL Server Connection Problems, Tricks and Solutions

NULL Value and Aniti Semi Join in SQL - Watch out the Pitfall