Index on a highly updated field causing excessive locks in SQL server

Question

I'm in a bit of a conundrum about what the best course of action on this (bad?) index is. I have a table which i use as a notification holder that i watch for database changes with.

CREATE TABLE [dbo].[TrackSessions](
[DataPartID] [int] NOT NULL,
[UserMasterID] [int] NOT NULL,
[RecordID] [bigint] NOT NULL,
[DateChanged] [datetime2](7) NOT NULL,


CONSTRAINT [PK_TrackSessions] PRIMARY KEY CLUSTERED 
(
    [DataPartID] ASC,
    [UserMasterID] ASC,
    [RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

When a record from any number of other tables is updated i use a trigger to record the change. Then a SqlChangeNotification is triggered on the table and i use the following select to get the new info. This is done several times a second constantly.

SELECT 
  [DataPartID], 
  [UserMasterID], 
  [RecordID], 
  [DateChanged] 
from dbo.TrackSessions WHERE DateChanged > @DT ORDER BY DateChanged DESC

Here is the index in question:

CREATE NONCLUSTERED INDEX [DateChanged-cr20151217] ON [dbo].[TrackSessions]
(
    [DateChanged] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Here are the index usage statistics over a 3 week period. (courtesy of [sp_BlitzIndex])

dbo.TrackSessions.DateChanged-cr20151217 (5): 
   Op Stats: 0 singleton lookups; 5,553,690 scans/seeks; 0 deletes; 0 updates; 
   Reads: 5,529,416 (5,529,416 seek) Writes:1,455,333
   Row lock waits: 212,192; total duration: 226 minutes; avg duration: 0 seconds; 
   Page lock waits: 232; total duration: 10 seconds; avg duration: 0 seconds; 
   Lock escalation attempts: 1; Actual Escalations: 0.
   Size: 26,741 rows; 7.3MB

For reference here is one of the triggers used to update the rows.

set transaction isolation level serializable
begin transaction          
    if exists(select *
                from inserted i with (updlock)
                inner join userD u on u.id = i.id
                inner join UserMaster m on m.UserLoginId = u.userid
                inner join TrackSessions s on s.DataPartID = 2 and s.UserMasterID = m.UserMasterID and s.RecordID = i.id)
    begin
        update s set DateChanged = SYSDATETIME()
        from inserted i
        inner join userD on u.id = i.id
        inner join UserMaster m on m.UserLoginId = u.userid
        inner join TrackSessions s on s.DataPartID = 2 and s.UserMasterID = m.UserMasterID and s.RecordID = i.id
    end
    else
    begin
        --First time this event fired. Should only happen once for a given user
        insert into TrackSessions (DataPartID,UserMasterID,RecordID,DateChanged)
        select 2,m.UserMasterID,i.id,SYSDATETIME()
        from inserted i
        inner join userD u on u.id = i.id
        inner join UserMaster m on m.UserLoginId = u.userid
    end
 commit

So my problem is that i'm getting deadlocks on statements associated with updating this table. I can't quite figure out what exactly the cause is but I believe this is because of[DateChanged] is being fought over.

This is by far the most used index on that table and is extremely valuable in terms of that select performing quickly. But it appears to also be incredibly expensive to maintain.

I'm not sure exactly what to do about it. Would changing the dateChanged data type to something like [datetime2](1) help with the expense of updating? How about just a [datetime]? Should i just stop trying to index it and let the select query scan the table instead?

Its ok if the select takes a second or so longer if it means that i have a reduced chance of deadlocks and i don't really care about fractions of a second as long as the select query will pickup the changed rows.

I'm running on SQL server 2012 with the latest updates in a mirror'd configuration. The pulling application is a .NET SqlConnection.

Answer 1

Instead of using the DateChanged column in the where clause perhaps you could add an IDENTITY column, and use it in the .Net client to keep track of processed rows? The .Net client could periodically save the last processed identity value in case it needs to restart.

This would allow you to get rid of the index on DateChanged completely, and queries getting the new rows would be fast since they would be performing a range scan on values greater than the last processed identity value. Something like:

SELECT 
  [TrackSessionID],
  [DataPartID], 
  [UserMasterID], 
  [RecordID], 
  [DateChanged] 
FROM dbo.TrackSessions 
WHERE [TrackSessionID] > @LastTrackSessionID 
ORDER BY DateChanged DESC;

A potential downside to this approach might be the IDENTITY column becoming a source of contention for inserts into the table. That could potentially be mitigated by using several partitions with a partition function that uses a hash function to decide which partition each insert goes into.

Something like this comes to mind:

CREATE PARTITION FUNCTION pf_TrackSessions (INT)
AS RANGE RIGHT FOR VALUES (1, 2, 3);

CREATE PARTITION SCHEME ps_TrackSessions
AS PARTITION pf_TrackSessions
ALL TO ([SessionFileGroup1]
    , [SessionFileGroup2]
    , [SessionFileGroup3]
    , [SessionFileGroup4]);

CREATE TABLE dbo.TrackSessions
(
    TrackSessionsID INT NOT NULL IDENTITY(1,1)
    , TrackSessionsIDModulus AS (TrackSessionsID % 4) PERSISTED
    , DataPartID INT NOT NULL
    , UserMasterID INT NOT NULL
    , RecordID INT NOT NULL
    , DateChanged DATETIME2(7)
        CONSTRAINT DF_TrackSessions_DateChanged
        DEFAULT (GETDATE())
    , CONSTRAINT PK_TrackSessions 
        PRIMARY KEY CLUSTERED
        (TrackSessionsIDModulus, TrackSessionsID)
) ON ps_TrackSessions (TrackSessionsIDModulus);
GO

The above DDL requires 4 filegroups, one for each partition. Rows would be evenly distributed among the 4 filegroups based on the modulus of the identity value. This reduces contention against the allocation bitmap pages by splitting the load across 4 sets of each structure.