Interesting real-world example of what goes on under the hood of SQL Server the other day.
We have a routine that locks rows (using REPEATABLE READ) in a transaction that can take some time to run (we simulated 5 minutes).
The same tables are accessed from a couple of websites, however as long as they weren't accessing THOSE rows they would be fine to proceed as normal. I know we're mixing and matching our locking models here, but I can't see how it would work (nor do I have the time to investigate any further!) if we were to use an optimistic locking strategy for our update routine. (As an aside, perhaps breaking it into chunks an committing rather than a 5-minute long transaction would help I'm sure)
Anyway, we tested in isolation and all was well, however as soon as we try it with the actual system then it fails- the system stops responding.
Looking at the query plan, we saw the problem straight away - one of the queries being run by the website was actually doing a table scan.
But why? These tables were all indexed where the join was happening.
Turns out that SQL Server is clever enough to know when a Table Scan is more efficient than a random access via an index.
Because our test data only had a handful of rows, this meant that a Table Scan happened every time. Running the same query on a database with 100000s of records and the table scan disappears, replaced with an Index seek.