Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2010-03-29 : 14:43:17
|
| Hi,Even if a SQL2008 database is running under compatability mode 80, are there differences in table locking/isolation levels between SQL2000 and SQL2008? We recently upgraded a database from SQL2000 to SQL2008 (but still with Comptability mode of 80) and we're running into a problem. We have a fairly large query. In SQL2000 it would take a little bit (when the server was under load) but would successfully complete (via a asp.net webpage). After we upgraded to 2008, the page was consistently timing out (when the server was under load). We changed the sproc to set the isolation level to READ UNCOMMITTED and now it works fine in 2008. The hardware for the server has significantly improved so I'm suprised (with roughly the same amount of load) why it would work in 2000 and not in 2008. I'm not concerned with dirty reads for this sproc but I am suprised we needed to change it given that it was working fine in SQL2000. Are there any other settings/differences that could be causing this problem? The timeout for the page is 90 in both environments.On a similiar topic we have also recieved reports of a few deadlock events. In SQL2000 we almost never had deadlocks, but since the transision we've had more of these issues (once again the hardware is significantly improved so I would think if anything these types of issues would decrease instead of increase).Any insights would be appreciated.Nic |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-30 : 06:08:58
|
Probably no help, but we don't update SQL versions until we are ready to move to (and thus take advantage of  ) the native compatibility mode.I figure that we would have to do a complete regression test anyway, so we might as well do that with the native compatibility mode for the SQL version - and then we can take advantage of new features - such as READ_COMMITTED_SNAPSHOT (which may be a significant benefit of SQL 2008 is giving you deadlocks where you never had them before (or if you did have them and have been working around them with NOLOCK hints or similar |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-03-30 : 06:24:19
|
| Kristen mentioned the READ_COMMITTED_SNAPSHOT isolation mode.It would probably be a much better choice for your sp than READ UNCOMMITTED.What's a 'little bit' to you in minutes?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-30 : 08:18:57
|
T.C. READ_COMMITTED_SNAPSHOT not available in SQL 2000 or SQL 2005+ with Compatibility Mode < 90  |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-03-30 : 08:27:53
|
| Ah yeah -- sorry about that. Ignore the suggestion for now.nic -- are you going to migrate your database to a newer compatibility mode eventually? There are a ton of nice features for 2005 (not used many of the new features in 2008).Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
locks and isolation levels (2000 vs 2008)