We have database with only 1 table, which conducts Inserts and Selects. There are No Updates in the table.
Table has lot of Concurrency; with many simultaneous Insert and Select queries occurring. Multiple Selects can cause Lock escalation, which lead to Page to Tables locks, etc .
Would Read Committed Snapshot Isolation reduce the locking/blocking in table with only Selects and Inserts? Current isolation level is Read Committed. My assumption is No. Inserts would not block Selects, since Selects Only Read Committed Data. Selects should not block Inserts, since Inserts are occurring on tail end of table with Identity Clustered and NewSequential Id.
[My thought: If we actually had Updates, RCSI would help since, before every begin transaction, those lines would be stored in tempdb for the Read queries.]
create table dbo.Customer
(
Customerid int primary key identity(1,1),
CustomerGuid uniqueidentifier default (newsequentialid()),
PersonName varchar(255)
)
create nonclustered index ix_CustomerGuid on Customer(CustomerGuid) include (PersonName)
create nonclustered index ix_PersonName on Customer(PersonName) include (CustomerGuid)
Select and Insert Queries
select CustomerGuid from dbo.Customer where PersonName = @PersonNameSelectVar
select PersonName from dbo.Customer where CustomerGuid = @CustomerGuidSelectVar
insert into dbo.Customer(CustomerGuid, PersonName) (@CustomerGuidInsertVar, @PersonNameInsertVar)