Blocking by Insert

Question

Blocking by Insert

Rechana Rajan

SSCertifiable

Points: 7709
More actions
October 16, 2016 at 1:40 am

#322116

Found the below query causing blocking from different SPID.
declare @0 bigint,@1 bigint,@2 varchar(50),@3 bit,@4 varchar(50),@5 datetime2(7),@6 varchar(50),@7 datetime2(7)
INSERT [dbo].[Transaction]
([TransactionId], [ConId], [TransactionStatus], [Status], [CreatedUserId], [CreatedDate], [UpdatedUserId], [UpdatedDate])
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
SELECT [Oid]
FROM [dbo].[Transaction]
WHERE @@ROWCOUNT > 0 AND [Oid] = scope_identity()
can anyone help me understand why ? The execution plan showing its using clustered index on OID.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 1

Rechana Rajan (10/16/2016)
Found the below query causing blocking from different SPID.
declare @0 bigint,@1 bigint,@2 varchar(50),@3 bit,@4 varchar(50),@5 datetime2(7),@6 varchar(50),@7 datetime2(7)
INSERT [dbo].[Transaction]
([TransactionId], [ConId], [TransactionStatus], [Status], [CreatedUserId], [CreatedDate], [UpdatedUserId], [UpdatedDate])
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
SELECT [Oid]
FROM [dbo].[Transaction]
WHERE @@ROWCOUNT > 0 AND [Oid] = scope_identity()
can anyone help me understand why ? The execution plan showing its using clustered index on OID.

It's because ALL INSERTs take at least a row lock.

If anything is a problem in the code, it's the fact that the SELECT is doing a totally unnecessary lookup on the Transaction Table.

You said this code was blocking another SPID. What does the code look like there? The reason I ask is because it's not likely the INSERT from the code above is the actual problem. There's a pretty good chance that the code that was blocked is the actual problem. Post that code and attach an actual execution plan and let's find out.

--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)

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 2

Jeff Moden (10/16/2016)
Rechana Rajan (10/16/2016)
Found the below query causing blocking from different SPID.
declare @0 bigint,@1 bigint,@2 varchar(50),@3 bit,@4 varchar(50),@5 datetime2(7),@6 varchar(50),@7 datetime2(7)
INSERT [dbo].[Transaction]
([TransactionId], [ConId], [TransactionStatus], [Status], [CreatedUserId], [CreatedDate], [UpdatedUserId], [UpdatedDate])
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
SELECT [Oid]
FROM [dbo].[Transaction]
WHERE @@ROWCOUNT > 0 AND [Oid] = scope_identity()
can anyone help me understand why ? The execution plan showing its using clustered index on OID.
It's because ALL INSERTs take at least a row lock.
If anything is a problem in the code, it's the fact that the SELECT is doing a totally unnecessary lookup on the Transaction Table.
You said this code was blocking another SPID. What does the code look like there? The reason I ask is because it's not likely the INSERT from the code above is the actual problem. There's a pretty good chance that the code that was blocked is the actual problem. Post that code and attach an actual execution plan and let's find out.

Thanks Jeff the TSQL under the other SPID was the same statement.

Enclosed Execution Plan. Changed extension from .xml to .txt

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

There are at least two issues here which will unnecessarily prolong an INSERT transaction using this process.

Firstly, as Jeff pointed out, the SELECT. Use the OUTPUT clause instead, like this:

DECLARE

@0 bigint,

@1 bigint,

@2 varchar(50),

@3 bit,

@4 varchar(50),

@5 datetime2(7),

@6 varchar(50),

@7 datetime2(7)

INSERT dbo.Transaction ([TransactionId], [ConID], [TransactionStatus], [Status], [CreatedUserId], [CreatedDate], [UpdatedUserId], [UpdatedDate])

OUTPUT inserted.[Oid]

VALUES (@0, @1, @2, @3, @4, @5, @6, @7)

One less read of the table.

Secondly, the Transaction table has a couple of foreign keys:

Transaction.TransactionId has to match a PK value in BTrans (BTrans. oid)

Transaction.ConId has to match a PK value in DOCons (DOCons.oid)

In each case, the PK is clustered and the plan shows a clustered index seek. This is an unnecessarily wide index when all you want to do is check that the PK exists. A more efficient index for this purpose would be a unique non-clustered index on the PK alone. Shared locks are taken during these reads so this may be significant.

There are implicit conversions from DATETIME2 to DATETIME for two of the parameters, I don't think this is significant.

The version of the plan which you posted wasn't retrieved from cache so may not be the same as the version which is causing the problem.

Interesting: both of the clustered index seek operators (for the FK lookups) have "forced index" = True in their property sheets.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 4

ChrisM@Work (10/17/2016)
There are at least two issues here which will unnecessarily prolong an INSERT transaction using this process.
Firstly, as Jeff pointed out, the SELECT. Use the OUTPUT clause instead, like this:
DECLARE
@0 bigint,
@1 bigint,
@2 varchar(50),
@3 bit,
@4 varchar(50),
@5 datetime2(7),
@6 varchar(50),
@7 datetime2(7)
INSERT dbo.Transaction ([TransactionId], [ConID], [TransactionStatus], [Status], [CreatedUserId], [CreatedDate], [UpdatedUserId], [UpdatedDate])
OUTPUT inserted.[Oid]
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
One less read of the table.
Secondly, the Transaction table has a couple of foreign keys:
Transaction.TransactionId has to match a PK value in BTrans (BTrans. oid)
Transaction.ConId has to match a PK value in DOCons (DOCons.oid)
In each case, the PK is clustered and the plan shows a clustered index seek. This is an unnecessarily wide index when all you want to do is check that the PK exists. A more efficient index for this purpose would be a unique non-clustered index on the PK alone. Shared locks are taken during these reads so this may be significant.
There are implicit conversions from DATETIME2 to DATETIME for two of the parameters, I don't think this is significant.
The version of the plan which you posted wasn't retrieved from cache so may not be the same as the version which is causing the problem.
Interesting: both of the clustered index seek operators (for the FK lookups) have "forced index" = True in their property sheets.

Thanks Chris.. that was awesome. I didnt know there was so much behind this script.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

No problem Rechana. Whilst Googling for [Forced Index = True] I came across this wonderfully succinct article by Grant Fritchey[/url]. A great starting point for reading and interpreting execution plans. Give it a try.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 6

ChrisM@Work (10/17/2016)
There are at least two issues here which will unnecessarily prolong an INSERT transaction using this process.
Firstly, as Jeff pointed out, the SELECT. Use the OUTPUT clause instead, like this:
DECLARE
@0 bigint,
@1 bigint,
@2 varchar(50),
@3 bit,
@4 varchar(50),
@5 datetime2(7),
@6 varchar(50),
@7 datetime2(7)
INSERT dbo.Transaction ([TransactionId], [ConID], [TransactionStatus], [Status], [CreatedUserId], [CreatedDate], [UpdatedUserId], [UpdatedDate])
OUTPUT inserted.[Oid]
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
One less read of the table.
Secondly, the Transaction table has a couple of foreign keys:
Transaction.TransactionId has to match a PK value in BTrans (BTrans. oid)
Transaction.ConId has to match a PK value in DOCons (DOCons.oid)
In each case, the PK is clustered and the plan shows a clustered index seek. This is an unnecessarily wide index when all you want to do is check that the PK exists. A more efficient index for this purpose would be a unique non-clustered index on the PK alone. Shared locks are taken during these reads so this may be significant.
There are implicit conversions from DATETIME2 to DATETIME for two of the parameters, I don't think this is significant.
The version of the plan which you posted wasn't retrieved from cache so may not be the same as the version which is causing the problem.
Interesting: both of the clustered index seek operators (for the FK lookups) have "forced index" = True in their property sheets.

Thanks Chris.

I tried creating unique NC index on the oid column but the execution plan is still using the clustered index. I am doing something wrong.

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 7

ChrisM@Work (10/17/2016)
No problem Rechana. Whilst Googling for [Forced Index = True] I came across this wonderfully succinct article by Grant Fritchey[/url]. A great starting point for reading and interpreting execution plans. Give it a try.

Thanks Again :-). Will go through the same.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Rechana Rajan (10/17/2016)
ChrisM@Work (10/17/2016)
There are at least two issues here which will unnecessarily prolong an INSERT transaction using this process.
Firstly, as Jeff pointed out, the SELECT. Use the OUTPUT clause instead, like this:
DECLARE
@0 bigint,
@1 bigint,
@2 varchar(50),
@3 bit,
@4 varchar(50),
@5 datetime2(7),
@6 varchar(50),
@7 datetime2(7)
INSERT dbo.Transaction ([TransactionId], [ConID], [TransactionStatus], [Status], [CreatedUserId], [CreatedDate], [UpdatedUserId], [UpdatedDate])
OUTPUT inserted.[Oid]
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
One less read of the table.
Secondly, the Transaction table has a couple of foreign keys:
Transaction.TransactionId has to match a PK value in BTrans (BTrans. oid)
Transaction.ConId has to match a PK value in DOCons (DOCons.oid)
In each case, the PK is clustered and the plan shows a clustered index seek. This is an unnecessarily wide index when all you want to do is check that the PK exists. A more efficient index for this purpose would be a unique non-clustered index on the PK alone. Shared locks are taken during these reads so this may be significant.
There are implicit conversions from DATETIME2 to DATETIME for two of the parameters, I don't think this is significant.
The version of the plan which you posted wasn't retrieved from cache so may not be the same as the version which is causing the problem.
Interesting: both of the clustered index seek operators (for the FK lookups) have "forced index" = True in their property sheets.
Thanks Chris.
I tried creating unique NC index on the oid column but the execution plan is still using the clustered index. I am doing something wrong.

No, you're not. For the constraint to pick up the new index, you will need to drop and recreate it. The IndexID is held in the system view sys.foreign_keys.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 9

ChrisM@Work (10/17/2016)
Rechana Rajan (10/17/2016)
ChrisM@Work (10/17/2016)
There are at least two issues here which will unnecessarily prolong an INSERT transaction using this process.
Firstly, as Jeff pointed out, the SELECT. Use the OUTPUT clause instead, like this:
DECLARE
@0 bigint,
@1 bigint,
@2 varchar(50),
@3 bit,
@4 varchar(50),
@5 datetime2(7),
@6 varchar(50),
@7 datetime2(7)
INSERT dbo.Transaction ([TransactionId], [ConID], [TransactionStatus], [Status], [CreatedUserId], [CreatedDate], [UpdatedUserId], [UpdatedDate])
OUTPUT inserted.[Oid]
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
One less read of the table.
Secondly, the Transaction table has a couple of foreign keys:
Transaction.TransactionId has to match a PK value in BTrans (BTrans. oid)
Transaction.ConId has to match a PK value in DOCons (DOCons.oid)
In each case, the PK is clustered and the plan shows a clustered index seek. This is an unnecessarily wide index when all you want to do is check that the PK exists. A more efficient index for this purpose would be a unique non-clustered index on the PK alone. Shared locks are taken during these reads so this may be significant.
There are implicit conversions from DATETIME2 to DATETIME for two of the parameters, I don't think this is significant.
The version of the plan which you posted wasn't retrieved from cache so may not be the same as the version which is causing the problem.
Interesting: both of the clustered index seek operators (for the FK lookups) have "forced index" = True in their property sheets.
Thanks Chris.
I tried creating unique NC index on the oid column but the execution plan is still using the clustered index. I am doing something wrong.
No, you're not. For the constraint to pick up the new index, you will need to drop and recreate it. The IndexID is held in the system view sys.foreign_keys.

Thanks a lot Chris.. You are awesome. I dropped the foreign keys(it was one hell of task as there were lot of tables involved ) and recreated them, as mentioned by you now the Exeuction plan shows the usage of new NC Indexes created on Primary Key.