I have encountered a problem with a query execution plan on MS SQL Server 2008. It is a simple query on a single table. The table has a primary key RNUM (number(10)) with a clustered index. The query is executed via ODBC using fast forward cursors and is constructed
select [field_list_here] from table_name where RNUM>@P and TYPE='A' order by RNUM.
The field TYPE has 2 possible values and is not indexed. The table has about 2 000 000 rows of static data (only reads, no inserts and updates).
For some time my query executes using the efficient query execution plan.
Below a copy from Management Studio from an ad-hoc query:
SELECT (0%) <- Clustered Index Seek (100%)
But after 2 days of executing other type of queries SQL Server starts to use other execution plan (live copy):
Fetch query (0%) <- Clustered Index Seek [CWT_PrimaryKey] (0%)
Fast forward (0%) <- Population quer
View Complete Post