CODEWITHSUNDEEP
sqlsql-serverconcurrencyparallel-processinginsert
AndCode
AndCode

Reputation: 488

What is the maximum number of concurrent INSERT queries for Microsoft SQL Server?

If multiple different services send INSERT queries for the same table simultaneously to SQL Server, using their own connections, how many INSERTS can be committed by SQL Server in parallel? Or SQL Server can only process them sequentially?

Upvotes: 1

Views: 1086

Answers (1)

gotqn
gotqn

Reputation: 43646

You can have up to 32,767 user connections. The INSERT behavior depends on factors like the Types of concurrency.

How fast is the insert operation depends on the table columns (count, type), its indexes, its foreign keys. The engine may need to check a lot of things in order to leave the database in a consistent state.

Let's say you are performing only INSERT operations and not other ones on this table with one column and primary key:

CREATE TABLE [dbo].[DemoUsers]
(
    [UserID] INT CONSTRAINT [PK_DemoUsers] PRIMARY KEY 
);

INSERT INTO [dbo].[DemoUsers] ([UserID])
VALUES (101)
      ,(102)
      ,(103);

Then in two separate query windows execute the following statements:

BEGIN TRANSACTION;

    INSERT INTO [dbo].[DemoUsers] ([UserID])
    VALUES (107);

--COMMIT TRANSACTION;

BEGIN TRANSACTION;

INSERT INTO [dbo].[DemoUsers] ([UserID])
VALUES (108);

--COMMIT TRANSACTION;

It's like two user are trying to insert a row which is taking some time (because the transaction is not committed):

enter image description here

You can see that IX lock on the same PAGE of the first transaction is not blocking the IX on the page of the other one:

enter image description here

In theory the inserts are not blocking each other and can be executed at the same time.

Upvotes: 1

Related Questions