7

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

FAST number_rows

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.

Community
  • 1
swasheck
  • 10,755
  • 5
  • 48
  • 89

1 Answers1

6

If it were a query that returned more than 1 row I'd speculate that someone at the vendor (way back when, given the version of SQL Server) stumbled on the query optimiser producing a preferable plan when FAST was specified as a hint.

As it's returning just 1 row, the explanation probably has more in common with the infinite monkey theorem than reasoned judgement. A junior saw a hint for FAST and decided that was preferable to his query not being fast.

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 query returns 1 row so there is no further result set to process. I'm inclined to suspect there is other code further up the stack that's causing the problem.

Mark Storey-Smith
  • 31,860
  • 9
  • 90
  • 125