Why Sql Server fails to use index?

Question

Why Sql Server fails to use index?

mbayraktar

SSC Rookie

Points: 25
More actions
March 12, 2011 at 12:43 pm

#389036

Hello,
I use Sql Server 2005 Enterprise.
When I ran the following query, Sqlserver fails to use the Primary key index on [id] column.
SELECT * FROM REQUEST_LOG WHERE id = 343323 AND Content = '';
If I drop the Content = '' criteria, result comes up immediately.
Do you have a comment on this issue? And how to improve Sqlserver 's default query plans mechanism without giving index hint.
Thanks in advance,
-Mehmet

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738713 More actions · Answer 1

Likely the statistics for the Content column indicates the is might be enough rows with a '', that it's worth just scanning the table. Even if you think it's quicker to use the index, it's possible that SQL Server might not.

If ID is the clustered index, I might leave it alone, but if you run this query often, I might add a nonclustered index on ID and include Content either as an indexes column, or an include.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Is ID the only primary key column? Is it clustered?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mbayraktar SSC Rookie Points: 25 More actions · Answer 3

Thanks for quick reply.

ID is the only primary key field, it has non-clustered index.

Content has no index or is not part of any index.

Since ID is primary key, and since table has more than 200million records, it is hard to understand how SqlServer comes up with wrong query plan.

Given this bad decision of query executing, I have doubts in other queries.

By the way, I have executed this sample query to test since recently waits are increased in SqlServer. So it is important that I have confidence SqlServer does its best.

Regards,

-Mehmet

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Where's the clustered index? Any reason the PK was made nonclustered?

Can you post the execution plan please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

The bottom level of nodes in the index is called the leaf nodes

In a clustered index, the leaf nodes contain the data pages of the underlying table.

Read this article and learn how to use indexes.

http://technet.microsoft.com/en-us/library/ms177443.aspx

Your quote:

Given this bad decision of query executing, I have doubts in other queries.

It is not a bad decision of the database engine, but rather a bad decision(s) on whomever designed the table(s).

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

mbayraktar SSC Rookie Points: 25 More actions · Answer 6

Hello,

Strangely, same query brings result immediately now.

In the meantime, I have gotten estimated query plan and realized query plan (though I had to abort query after 30 sec since this is a high transaction system) a few times.

Estimated query plan showed index seek and row lookup correctly, but It was doing something I guess. Doing these must have helped SqlServer correct itself, I guess.

I have checked msdn links. But there is no obvious advantage for this query btw clustered and nonclustered index. It will go to one row via index from root to leaf where it will find the row data in clustered index whereas it has to make one more visit in nonclustered index.

Thanks all for your time.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 7

But there is no obvious advantage for this query btw clustered and nonclustered index.

Maybe not. But you're really going to hate yourself if this table get's any depth to it because you can't rebuild nonclustered indexes unless there's a clustered index on the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Jeff Moden (3/12/2011)
...because you can't rebuild nonclustered indexes unless there's a clustered index on the table.

????

You can't rebuild the table (to eliminate forwarding pointers or compact pages) if there's no cluster, but the nonclustered indexes can still be rebuilt

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 9

Boy, do I need some sleep or what. You're, of course, spot on, Gail. I'm not sure where my comment came from. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)