Problem solve Get help with specific problems with your technologies, process and projects.

Optimizer using table indexes

Our client has a query that uses indexes when it runs in a database of two months ago, but it does table scan in the same database two month later. Only if I 'set forceplan on' does the query then use indexes again. I rebuilt indexes DBCC, DBREINDEX and dropped and re-created the indexes, but the query optimizer just refuses to use the indexes in the latest database.

Realizing the difference in the database is one of the table size, I deleted additional rows in one of the tables. Without doing anything else, the query used the indexes again.

Under server configuration option: database size - 10,000 in Maximum. Does this relate to the problem? The platform is Microsoft SQL Server 6.50 - 6.50.416 (Intel X86) and db_size is 10131.00 MB.

Could you explain the reason of this behavior?

The optimizer is going to make a decision between indexes and a table scan based on the data distribution stats that it currently has and the estimated number of rows that would be affected by your query. That means, yes, the query plan that is used is highly dependent on not only volume of data, but also distribution of that data. I'd need to see the table structures, queries being issued, query plans generated, row counts in any affected table, and % of data potentially affected by your queries.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.