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 |
|
harin25
Starting Member
3 Posts |
 Posted - 2009-10-12 : 07:19:36
|
| Hi,We recently migrated to SQL Server 2005 with out client and our application is web-based using Java technology. The Client has two identical servers (One Production and other Disaster Recovery) with the following configuration;Intel Xeon 2.13GHz - Dual Core, 4 GB RAM, 400 GB RAID HDD. (S/w: JDK 1.4.2)The installed applications are also identical at both the applications. Both the servers have both Application & Database on the same server.**The Problem:**We have multi-threaded processes being hit on the database (Multiple Inserts) and there are simultaneous select operations performed on the table.In the production server, the multi-threaded application console, shows Deadlock messages and asking user to re-run the transaction, whereas at Disaster site, there are no such issues and operation is fast.Since the "Deadlock" is seen on the main server, the entire server slows down, dis-allowing any user operation on the server.I checked through forums and a configuration, ISOLATION level has to be set explicitly in SQL 2005. Now this setting **SET READ_COMMITTED_SNAPSHOT ON** is set on our database.Any other suggestions to help to check the slowness of the application/database during parallel inserts / updates?Please help.Regards,Hari N |
|
|
SreenivasBora
Posting Yak Master
164 Posts |
Posted - 2009-10-13 : 13:47:50
|
| Hari,Please verify, 1. Any Transactional Control statements are using in between INSERT & UPDTAES...Example: Begin Tran Insert table A Update Table B2. Please check the ORDER of exeuction..3. Verify the time to INSERT the record and check any unnecessary INDEXES causing delay in INSERTION / UPDATES4. Use NOLOCK on Select statements.With RegardsBSR |
 |
|
|
SreenivasBora
Posting Yak Master
164 Posts |
Posted - 2009-10-13 : 13:49:15
|
| and also reduce no.of treads and verify the output |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
harin25
Starting Member
3 Posts |
Posted - 2009-10-14 : 09:33:33
|
| Thanks Sreenivas and Tkizer. Will revert after checking.Since it is a Production system, deadlock re-occurrence are hard to be re-tested and verified..Regards,Hari N |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-10-18 : 11:38:42
|
| How much memory you have for your server? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Deadlock & Slowness in SQL 2005