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 |
|
lebedev
Posting Yak Master
126 Posts |
 Posted - 2007-07-18 : 20:00:40
|
| I've been running my simulations against our SQL Server 2005 database all day today and have been noticing a lot of deadlocking. Almost every transaction was deadlocking and retrying.About an hour ago I started SQL Profiler and enabled trace flags using DBCC TRACEON(3605, 1204). However, even though Performance Monitor was indicating high Deadlocks/sec ratio I could not find any evidence of them in SQL Profiler or in SQL Server error log. I restarted my simulations several times, but nothing changed.So, then a miracle happened and the last time I started my simulations all deadlocks disappeared. I did not change any queries and I am puzzled as to what could have happened. The only things I did were starting SQL Profiler and executing DBCC TRACEON commands.Since queries and transactions did not change, could it be that they started executing (and hence releasing locks) faster because of lower network traffic at the end of the day or because the transaction log was written to disk? Could it be anything that SQL Server 2005 does to optimize its own performance?What else could have caused such a drastic change?Thanks.Alec |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-19 : 10:35:15
|
| Take look at snapshot isolation, it may help. |
 |
|
|
|
|
|
Deadlock puzzle