Will creating more indexes lead to deadlock?

• Santhoshkumar KB

  SSCertifiable

  Points: 5066

  More actions

  September 8, 2009 at 6:57 am

  #133363

  Hi,

  We have table which is used frequently(it gets inserted/updated about 300times a day, being half the activity at noon hours)

  currently there are 4 indexes created on ID, AB_NO, CI_NO and LAST_NAME

  we are planning to add an index on AB_SSN column. we are afraid whether this may lead to deadlocks.

  Currently the table has aorund 17lac records. --edited

  Please advice.

  CREATE TABLE [dbo].myTable(

  ID [int] IDENTITY(1,1) NOT NULL, --Primary Key Clustered

  AB_NO [char](16) NULL, --Nonclustered Index

  CI_NO [char](16) NULL, --Nonclustered Index

  CreateDate [datetime] NOT NULL CONSTRAINT [DF_DATA_CREATE_DT] DEFAULT (getdate()),

  ArcDate [datetime] NULL,

  LAST_NAME [varchar](50) NULL, --Nonclustered Index

  FIRST_NAME [varchar](25) NULL,

  [CASX_MID_NAM] [varchar](25) NULL,

  AC_NO [char](5) NULL,

  AD_NO [char](10) NULL,

  BD_No [int] NULL,

  DOB [datetime] NULL,

  AB_SSN [varchar](9) NULL,

  AB_LB [char](3) NULL,

  AB_PName [varchar](15) NULL,

  AB_AMT [money] NULL,

  CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED

  (

  [ID] ASC

  )

  ) ON [PRIMARY]

  GO

  Thanks,

  KB

  Thanks,
  Santhosh
  

  ---

  Human Knowledge Belongs To The World !!

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  September 8, 2009 at 9:03 am

  #1050578

  Indexes really don't have much of anything to do with the creation of deadlocks although the addition of an index can sometimes increase performance well enough to eliminate some deadlocks.

  My recommendation would be to lookup "Deadlocks" in Books Online and see what actually causes them and some of the methods to use to prevent them. One of the methods is to keep transactions (usually refers to explicit multistatement transactions) very short and proper indexing can help in that area.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)