This is question 1 of 2 related to
OPTION (FAST 1);
We've just upgraded our ERP database from SQL 2000 EE to 2008 R2 EE and we've been noticing increased blocking in the database. I've narrowed it down to what I believe to be the offending statement in the vendor's code which is:
SELECT MAX(column) FROM [table] WHERE <condition> OPTION (FAST 1);
The spid leaves an open transaction and takes a lock on the table, blocking all other clients. However, the calling client no longer seems to be interacting with the server to tell the server that it's received the data to end the session.
Reading the documentation on Query Hints, I saw this statement
Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.
This makes me wonder if the client has somehow broken communication, would the server keep the transaction open, processing the full result set after the first
n rows are returned and leave the transaction open? The process is an internal process so I can't really watch an end-user execute the session to do it, and this is not something that happens every time the internal process occurs. However, it is only ever used by the internal process.
Having read Remus' answer on SO it seems like it is overkill for the simplicity of the query. Looking at the query, if they're receiving more that one result from an ungrouped
MAX then something's very fishy.
So, as I prepare to work with the vendor, I was wondering if I could begin to accurately pin our blocking issues on the fact that this query hint is being used.
Please feel free to edit/request edits as I know this may actually be unclear.