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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.