Controlling query plan for a simple query

[tombos]

I have a table with approximately 800K rows against which I run a common query. For example:

select zip,route_number,city,hh_count from tblRoutes
where event_id='MS020730'
and market_code='AL-ALB'
and job_no='001'

What's causing me trouble is that SQL chooses to execute this query in different ways depending upon the value of these query parameters. In other words, for the above query, the server does an Index Seek, a Bookmark Lookup, and a Select. The query returns three rows in less than a second, and the cost shown in the query plan within Query Analyzer is quite low. No problem.

However, if I run the exact same query with a minor change, as follows:

select zip,route_number,city,hh_count from tblRoutes
where event_id='MS020730'
and market_code='AL-MOB'
and job_no='001'

it returns 9 rows in over 40 seconds! Looking at the query plan in Query Analyzer explains why, because it's doing a Table Scan followed by a negligible Select operation. The cost of that Table Scan operation is a couple of orders of magnitude greater than the Seek and Lookup performed before.

Why would it execute nearly the same query in two different ways, and how can I force it to operate only the first way?

 

[tlbroadbent]

Are the index statistics up-to-date? When were the indexes last rebuilt? If the statistics are out of date, the query optimizer can't make good execution plans. Try executing one of the following to see if it makes a difference in the execution plans generated.

UPDATE STATISTICS tblRoutes

DBCC DBREINDEX('tblRoutes','',0) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tombos]

Yeah, I've updated the statistics and reindexed too. Nothing changed in the behavior.

I'm simply confused why SQL would decide the query is best executed with a table scan for one value but that using the index is best for the other. Is there any way that I can FORCE it to use the index when I know that's best?

 

[tlbroadbent]

Yes, you can use query hints but I recommend that you don't. There must be an underlying problem causing the behavior you see. I have not seen this type of behavior. Which version of SQL Server do you use? Which service pack is installed? Do you have multiple indexes on the table? Do you have a clustered index? Which columns are indexed by each type of index? When the index is used, which columns are in that index?

Example: Use an INDEX hint

select
zip, route_number,
city,hh_count
from tblRoutes (INDEX=ix_event_id)
where event_id='MS020730'
and market_code='AL-MOB'
and job_no='001' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tombos]

Well, I have a new twist today that will sound crazy. I promise that I'm generally competent.

After I read your previous posting, I went and did the Update Statistics and the DBCC DBREINDEX commands. (I would also note that the indexes are rebuilt every Sunday as part of our maintenance plan.) As soon as they completed within Query Analyzer, I re-ran the query and got identical results. That's when I replied again and said, "Yeah I tried that and it didn't help."

However...

Today I come in and run the same query, and SQL uses the index perfectly regardless of the key value I use. I cannot seem to make it do a table scan, and I've checked everything about the table to make sure that no one else has made any changes.

Do the reindex and update statistic operations require some amount of time to take effect? Is it possible that the proper way to execute my query was somehow cached?

This is pretty bizarre. Things don't simply change--I know there's cause and effect somewhere here.

Ideas? Should I just get a job bagging groceries?

 

[tlbroadbent]

Things do get confusing. It seems the more I learn, the less I know. Certainly sounds like you did all the right things.

There may be some delay in updating the statistics though I've not read that this is the case. I think it more likely that the query plans were cached and SQL Server didn't create new query plans after you updated stats and rebuilt indexes. Then when you ran today, the procedure cache had been cleared.

You can use DBCC FREEPROCCACHE to clear procedure cache. SQL BOL states, "Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache." (Emphasis added.) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.