Thanks Roy, for explaining that.

We have been running into some deadlocks lately, due to one main sort of Lookup form our app uses. The number of rows generally returned has doubled recently due to some data additions, and this is locking the select statement long enough to cause drama for users who are trying to update individual records, some of which appear in the Select.

I recently changed the Select to use WITH (NOLOCK) much to the chagrin of several DBAs out there who are scared of "dirty reads".

In the situations where we are using our data, switching the database settings to read-committed row-versioning would probably be a much better long-term solution.

Although I am still rather concerned about the hit the TempDB would take as a result of this. At peak times, our SQL Server is already chewing through somewhere around 6Gb of memory, and I'm afraid this would push it over the edge.

Has anyone done any particular load testing on how the TempDB is impacted by such a setting change?