Concurrent SQL Insert Are Blocking to a Table

Question

I am using the SQL 2005 for an application. In my case, numbers of requests are being generated through different processes and inserting the Record to one Table. But when I examine the processes running in database by sp_who2 active procedure, I find the Inserts are being blocked by other Inserts Statements and causing the process slower. Is there any way to avoid the blocking / deadlocks in concurrent inserts to one table. Below is the structure of my table.

`CREATE TABLE [dbo].[Tbl_Meta_JS_Syn_Details](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EID] [int] NULL,
    [Syn_Points_ID] [int] NULL,
    [Syn_ID] [int] NULL,
    [Syn_Word_ID] [int] NULL,
    [Created_Date_Time] [datetime] NULL CONSTRAINT [DF_Tbl_JS_Syn_Details_Created_Date_Time]  DEFAULT (getdate()),
 CONSTRAINT [PK_Tbl_JS_Syn_Details] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]`

@Andomar Can you please explain this "A primary key on ID might help" . I am already using Primary Key on `ID` Column — Awais Amir, Sep 27 '13 at 12:37
check the below link if it helps you: [enter link description here][1] [1]: http://stackoverflow.com/questions/14913953/sql-server-simultaneous-inserts-to-the-table-from-multiple-clients-check-lim — satyajit, Sep 27 '13 at 12:55

score 0 · Answer 1 · answered Sep 27 '13 at 12:33

There is always blocking if many processes are trying to insert into one table. However, some settings can be used to limit the amount of time.

What isolation level are you using? The default? http://technet.microsoft.com/en-us/library/ms173763.aspx

Can you include the following information into this post:

1 - How many processes are running (inserting) at the same time?

2 - What type of disk sub-system are you using? RAID 5 or just a simple disk.

3 - What version of SQL Server you are on?

4 - What are the growth options on the database?

5 - How full is the current database?

6 - Is instance file initialization on?

Given answers to the above questions, you can optimize the insert process.

Concurrent SQL Insert Are Blocking to a Table

1 Answers1