Deadlock and Slowness in SQL 2005

• TechChoc

  Valued Member

  Points: 71

  More actions

  October 12, 2009 at 5:14 am

  #212033

  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.

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  October 12, 2009 at 5:43 am

  #1064666

  Try this for deadlock analysis

  http://www.sqlservercentral.com/articles/deadlock/65614/

  Try these for slow-downs

  http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

  http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

  ---

  
  Clear Sky SQL
  My Blog[/url]

• TechChoc

  Valued Member

  Points: 71

  More actions

  October 12, 2009 at 6:41 am

  #1064690

  Thanks..

  Can you tell me if there could be dependent issues like, JVM / Memory leak / any other.?

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  October 12, 2009 at 8:21 pm

  #1065006

  As mentioned in the article provided, many times better or more appropriate indexing can clear up deadlock issues.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question