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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2006-01-16 : 19:11:07
|
| My company is having issues with deadlocks in the software it uses to run basically the whole company, and I think it's because we're using long-running reports without READ UNCOMMITTED. The reports are generally for historical data so I'm not concerned about the very slim chance that a row being pulled is also being updated. Eventually we'll separate the reporting data from the production data, but in the meantime I'd like to add "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" to the beginning of all the stored procs for the reports. Would this be sane? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-01-16 : 20:42:41
|
| >>Would this be sane?Yeswhen (relatively slow) reporting SPs are in the same environment as an OLTP app, setting isolation level to read uncommitted (for the report SPs) is a good thing to do. It sounds like you are aware and ok with the ramifications of dirty reads.Be One with the OptimizerTG |
 |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2006-01-17 : 01:44:57
|
| Ideally there should be a different database for reports. As reports consist of long running queries, it can be a performance bottleneck. Its a good idea to use Read uncommited data or with hint as WITH(NO LOCK).RegardsSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
|
|
|
|
READ UNCOMMITTED for reports