sql server 2008 reads blocking writes

Question

I have upgraded a set of databases from sql server 2000 to sql server 2008 and now large reads are blocking writes whereas this wasn't a problem in sql server 2000 (same databases and same applications & reports) Why? What setting in 2008 is different? Did 2000 default to read uncommitted transactions?

(update) Adding with (nolock) to the report views in question fixes the problem in the short term - in the long run we'll have to make copies of the data for reporting either with snapshots or by hand. [sigh] I'd still like to know what about sql server 2008 makes this necessary.

(update 2) Since the views in question are only used for reports 'read uncommited' should be ok for now.

Answer 1

SQL Server 2000 did not use READ UNCOMMITTED by default, no.

It might have something to do with changes in optimizations in the execution plan. Some indexes are likely locked in a different order from what they were in SQL Server 2000. Or SQL Server 2008 is using an index that SQL Server 2000 was ignoring altogether for that particular query.

It's hard to say exactly what's going on without more information, but read up on lock types and check the execution plans for your two conflicting queries. Here's a nice short article that explains another example on why things can deadlock.

Answer 2

Read Committed is the default isolation level in SQL Server 2000, not Read Uncommitted.

http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx

I imagine that something in your app was setting the isolation level - perhaps via one of the connection object properties. Have a look here for the methods used to set Transaction Isolation levels via ADO, ODBC, and OLE DB.

You can do the same in SQL Server 2008, but...are you sure that your app should be running under read uncommitted? Is your app specifically designed to handle data movement and phantom reads?

Answer 3

I'm actually surprised that you didn't run into problems in SQL Server 2000. It seems like every week we were fixing stored procedures that were locking tables because someone forgot nolocks.

Answer 4

You could look into snapshot isolation, this will allow the app to read the older version of the rows whilst the writing threads are still busy updating the rows.

http://msdn.microsoft.com/en-us/library/ms189050.aspx