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.