Reduce locking for Insert statement?

Question

Reduce locking for Insert statement?

sqlsurfing

SSCarpal Tunnel

Points: 4883
More actions
September 10, 2014 at 1:48 am

#311482

Hi Everyone,
There are 2 tables which need to have data inserted into them for auditing purposes.
The number of inserts per minute seems be at least 50-100.
I wanted to get some feedback/suggestions on how to reduce locks during inserts.
There are 2 tables
Table1
ID - Surrogate Key/identity Column
SomeColumn1
SomeColumn2
SomeColumn3
SomeColumn4_timestamp
clustered index on ID column
Table2
ID Column ..... there's a call to get id from SCOPE_IDENTITY()
SomeColumn1
SomeColumn2
clustered index on ID column
NC idx on SomeColum1
NC idx on SomeColum2
A Sproc has the following code:
I changed the names to protect the innocent 🙂
CREATE PROCEDURE [dbo].[logging_sp]
@Audit BIGINT,
@varT1_1 NVARCHAR (50),
@varT1_2 NVARCHAR (64),
@varT1_3 INT,
@VarTime DATETIME,
@varT2_1 NVARCHAR (50),
@varT2_2 NVARCHAR (1024),
AS
BEGIN TRY
BEGIN TRANSACTION
IF @Audit IS NULL
BEGIN
INSERT INTO Table1 (SomeColumn1
SomeColumn2,
SomeColumn3_timestamp)
VALUES (@varT1_1,
@varT1_2,
@varT1_3,
@VarTime);
SELECT @Audit = SCOPE_IDENTITY ();
END
INSERT INTO Table2 (auditid, Some_Column1, Some_Column2)
VALUES (@Audit, @varT2_1, @varT2_2);
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrorMessage NVARCHAR (4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE (),
@ErrorSeverity = ERROR_SEVERITY (),
@ErrorState = ERROR_STATE ();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Kinda hard without more information.

From the looks of that code, it should take single-row locks on the two tables (locking the inserted rows) and hold them until the end of the transaction. That's about the smallest locking scope and range possible, so very hard to reduce.

50-100 inserts/sec is reasonably low, nothing in the code jumps out as being problematic to that end.

What is the actual problem here?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lempster SSCoach Points: 15651 More actions · Answer 2

Are you using the default Transaction Isolation Level of READ COMMITTED or have you changed it?

Regards

Lempster

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 3

GilaMonster (9/10/2014)
Kinda hard without more information.
From the looks of that code, it should take single-row locks on the two tables (locking the inserted rows) and hold them until the end of the transaction. That's about the smallest locking scope and range possible, so very hard to reduce.
50-100 inserts/sec is reasonably low, nothing in the code jumps out as being problematic to that end.
What is the actual problem here?

Thanks Gail and Lempster for reply!

The main problem is Im trying to research into helping reduce the amount of locking that occurs. The sproc that inserts the records block each other at times. But I was kind of stumped and seeking out ideas. Incidentally looking at it again, it turns out there's other reason for the blocking to occur as well. Sometimes there's another sproc that searches for duplicates joining both those 2 audit tables - size of audit tables are approx 200Mill rows each. That duplicate check sproc performs a self join. (I don't know the business rules around it-but have asked developer)

The code is using the default read committed isolation level. I think i'll focus on working w/developer on any tweaks first on that blocking sproc first.

Thanks for replies-will update the post when I get info

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

sqlsurfing (9/10/2014)
I think i'll focus on working w/developer on any tweaks first on that blocking sproc first.

Good idea. Can't see anything in the code you posted that looks like a blocking problem, unless it's part of a larger transaction

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 5

GilaMonster (9/11/2014)

sqlsurfing (9/10/2014)
I think i'll focus on working w/developer on any tweaks first on that blocking sproc first.

Good idea. Can't see anything in the code you posted that looks like a blocking problem, unless it's part of a larger transaction[/quote

I think blocking also, thanks Gail for replies. I have not heard back from the developer, I think they are too busy to take a look. Maybe I can convince them to change the data types or use compression to save some io to see if it helps since changes to code are not coming anytime soon it seems :rolleyes:

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀