I have Simple SP to Insert Single Record from Application.
When I executes it manually Response time is within 1 Sec.
But When we try to Test with multiple users Same Time it's Getting Blocked/Deadlock it Self.(we try for 10 users only)
Table Has around 1 billions of rows without partitioning.
and it also have some Indexes
It's Working Fine with Applock and tablock but responce time is around 30 sec. but My requirement response time is less 2 Sec.
OKTransaction (Process ID 178) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
CREATE TABLE [dbo].[tbCustTrxnHistBalance]
(
[biCustTrxnHistBalanceID] [bigint] NOT NULL IDENTITY(1, 1),
[iYYYYMM] [int] NOT NULL,
[iCustTrxnTypeID] [int] NOT NULL,
[tiBalanceTypeID] [tinyint] NOT NULL,
[biAccountID] [bigint] NOT NULL,
[sdtPostDate] [smalldatetime] NOT NULL,
[dcBeginBalance] [decimal] (22, 2) NULL,
[dcTrxnAmt] [decimal] (22, 2) NULL,
[dcEndBalance] [decimal] (22, 2) NULL,
[dcBeginCSCLocationBalance] [decimal] (14, 2) NULL,
[dcEndCSCLocationBalance] [decimal] (14, 2) NULL,
[biCustTrxnDetailID] [bigint] NULL,
[biViolatorTrxnHistID] [bigint] NULL,
[biETCTripTrxnID] [bigint] NULL,
[biIOPIncomingTripTrxnID] [bigint] NULL,
[biIAGIncomingTripTrxnID] [bigint] NULL,
[biIAGIncomingNonTollTripTrxnID] [bigint] NULL,
[iUpdUserID] [int] NOT NULL,
[dtUpdTime] [datetime] NOT NULL CONSTRAINT [DF__tbCustTrx__dtUpd__3296789C] DEFAULT (getdate())
) ON [fgTransData]
GO
ALTER TABLE [dbo].[tbCustTrxnHistBalance] ADD CONSTRAINT [XPKtbCustTrxnHistBalance] PRIMARY KEY CLUSTERED ([biCustTrxnHistBalanceID]) ON [fgTransData]
GO
CREATE NONCLUSTERED INDEX [ixtbCustTrxnHistBalance_biAccountIDPostedDate] ON [dbo].[tbCustTrxnHistBalance] ([biAccountID], [sdtPostDate]) INCLUDE ([biETCTripTrxnID], [biIAGIncomingTripTrxnID], [dcTrxnAmt], [iCustTrxnTypeID]) WITH (ALLOW_ROW_LOCKS=OFF, ALLOW_PAGE_LOCKS=OFF) ON [fgTransIndexes]
GO
CREATE NONCLUSTERED INDEX [IX_tbCustTrxnHistBalance_biETCTripTrxnID_072E8] ON [dbo].[tbCustTrxnHistBalance] ([biETCTripTrxnID]) INCLUDE ([iYYYYMM], [sdtPostDate]) WITH (ALLOW_ROW_LOCKS=OFF, ALLOW_PAGE_LOCKS=OFF) ON [fgTransIndexes]
GO
CREATE NONCLUSTERED INDEX [idx_tbCustTrxnHistBalance_DailyJob] ON [dbo].[tbCustTrxnHistBalance] ([iYYYYMM], [sdtPostDate], [biAccountID]) WITH (ALLOW_ROW_LOCKS=OFF, ALLOW_PAGE_LOCKS=OFF) ON [fgIndexes]
GO
CREATE NONCLUSTERED INDEX [ixtbCustTrxnHistBalance_biCustTrxnDetailIDdcTrxnAmt] ON [dbo].[tbCustTrxnHistBalance] ([biCustTrxnDetailID]) INCLUDE ([dcTrxnAmt]) WITH (ALLOW_ROW_LOCKS=OFF, ALLOW_PAGE_LOCKS=OFF) ON [fgTransIndexes]
GO
CREATE NONCLUSTERED INDEX [ixtbCustTrxnHistBalance_BalanceTypeIDAccountIdBalance] ON [dbo].[tbCustTrxnHistBalance] ([tiBalanceTypeID], [biAccountID], [biCustTrxnHistBalanceID] DESC) INCLUDE ([biCustTrxnDetailID], [dcEndBalance]) WITH (ALLOW_ROW_LOCKS=OFF, ALLOW_PAGE_LOCKS=OFF) ON [fgTransIndexes]
GO
CREATE NONCLUSTERED INDEX [ixtbCustTrxnHistBalance_biAccountID_biCustTrxnHistBalanceID] ON [dbo].[tbCustTrxnHistBalance] ([biAccountID], [biCustTrxnHistBalanceID]) ON [fgTransData]
GO
ALTER TABLE [dbo].[tbCustTrxnHistBalance] ADD CONSTRAINT [FK_tbCustTrxnHistBalance_stbBalanceType] FOREIGN KEY ([tiBalanceTypeID]) REFERENCES [dbo].[stbBalanceType] ([tiBalanceTypeID])
GO
ALTER TABLE [dbo].[tbCustTrxnHistBalance] ADD CONSTRAINT [FK_tbCustTrxnHistBalance_tbCustAccount] FOREIGN KEY ([biAccountID]) REFERENCES [dbo].[tbCustAccount] ([biAccountID])
GO
We have some OLTP Reporting as well as From Application also we have some GET Calls on that Table.So we required those Indexes.
I would be concerned about a response time measured in seconds - would expect milliseconds.
Do you have triggers on this table?
Your SP is receiving balance calculations from the client - are you sure two processes can't try updating the same balance - if so it will get it wrong.
Makes me think you might be holding transactions from the client which could be causing your problems.
Taking 30 secs with tablock suggests that other processes are taking locks preventing this from running.
You should consider moving the reporting to another server or to an aggregate table.
Removing the foreign keys would mean that the insert doesn't have to check (and take locks on) the referenced tables but you would need to ensure that integrity is maintained. I'm assuming that the referenced tables are never updated or deleted.
Is this part of a batch of statements run in a transaction?
It looks like it's keeping historic balances transitions for a customer which suggests it's part of a sequence of statements to implement a payment.
I would have a look at what else is happening on the server at the time and try logging the start and end of this SP to get the timings and you can use that for monitoring what is happening at the time
see http://www.nigelrivett.net/Products/DWBuilder/TraceTable.html
Change this to "=ON". By preventing row locks, you're forcing SQL to lock the entire page. Since the table is clustered on IDENTITY (yuck!), all 10 INSERTs could be going to the same page.
Also, verify that tables dbo.stbBalanceType and dbo.tbCustAccount have indexes that support the foreign keys from this table. And that those other tables do not have excessive locking.
Although this would normally help - I don't think it will in this situation. The OP is utilizing app locks and has stated they are using tablock.
If each insert is locking the table - no wonder it is taking a long time. They are also seeing deadlocks - and they have an uncommittable transaction (probably because the code is not rolling back the transaction when it fails).
A tablock is a single lock and will be quick if it's not blocked. A deadlock will cause the batch to be rolled back as will any error due to the xact abort, you don't need to rollback explicitly here if it's a single atomic statement as that will be handled be the server. I suspect there is more going on though and there is a transaction involved which is probably being handled at a higher level.
