We have a scenario where the following select statement performs differently on against two separate instances of our database. This is being executed from a process using ODBC/SQL Server Native driver 10 version 2009.100.2500.00. Databases are
SQL Server 2008 R2.
select count(*) from abc where xyz like '%'
Using the Profiler, in one case we see that AUTO_FETCH is enabled, and the performance of the request is slow, 9+ seconds. In the other case we see that FORWARD_ONLY is enabled, and the performance is fast, < 1 second.
Is there some place where there is a configuration for which is used in the event the select statement itself does not specify?