Return to Question

[![screenshot of sample data][1]][1]

Thank you for your assistance. [1]: https://i.sstatic.net/JbNBs.png

Thank you for your assistance.

I am looking for some assistance please. I have a query that is running against a fairly big table (2 million records). I have been trying to get the indexes working efficiently. There are a few other queries against this table, but this is by far the most frequent one. I am seriously struggling to get it to execute in under 1second, and often see it running in 3 to 5 seconds using profiler. It might be as fast as can be, but i would appreciate some input to confirm/deny that please. Just a note  : Dev wont change the query or schema at all. Only optimizations can be made in the database only and no schema changes.

Table Data snapshot  : [![enter image description here][1]][1]

I am looking for some assistance please. I have a query that is running against a fairly big table (2 million records). 

I have been trying to get the indexes working efficiently. There are a few other queries against this table, but this is by far the most frequent one. I am seriously struggling to get it to execute in under 1 second, and often see it running in 3 to 5 seconds using profiler. 

It might be as fast as can be, but I would appreciate some input to confirm/deny that please.

Just a note: Dev wont change the query or schema at all. Optimizations can be made in the database only, and no schema changes.

Table Data snapshot:

[![screenshot of sample data][1]][1]

Update Notifications set Status = 'Awarding' OUTPUT deleted.* where ntID = (
select top(1) ntID from Notifications where NotificationType = 'Shift' and (Status = 'Done') and ResponseWindow < '2019-02-04 10:40:03' order by ntID)

CREATE INDEX [IX_Notifications_Status_NotificationType_ResponseWindow_ntID] ON [dbo].[Notifications]([Status] ASC,[NotificationType] ASC,[ResponseWindow] DESC,[ntID] DESC)
CREATE INDEX [IX_Notifications_Status_ScheduledLaunchDate_ntID] ON [dbo].[Notifications]([ScheduledLaunchDate] DESC,[Status] ASC,[ntID] ASC)
CREATE INDEX [IX_Notifications_NotificationType_caNotificationID_NotificationID_ntID] ON [dbo].[Notifications]([NotificationType] DESC, [caNotificationID] DESC, [NotificationID] DESC, [ntID] DESC);

Update Notifications 
  set Status = 'Awarding' OUTPUT deleted.*  
where ntID = (
    select top(1) ntID 
    from Notifications 
    where NotificationType = 'Shift' 
      and (Status = 'Done') 
      and ResponseWindow < '2019-02-04 10:40:03' 
    order by ntID)

CREATE INDEX [IX_Notifications_Status_NotificationType_ResponseWindow_ntID]  
ON [dbo].[Notifications](
   [Status] ASC,[NotificationType] ASC,[ResponseWindow] DESC,[ntID] DESC 
) 

CREATE INDEX [IX_Notifications_Status_ScheduledLaunchDate_ntID]  
ON [dbo].[Notifications](
    [ScheduledLaunchDate] DESC,[Status] ASC,[ntID] ASC 
) 

CREATE INDEX [IX_Notifications_NotificationType_caNotificationID_NotificationID_ntID]  
ON [dbo].[Notifications](
   [NotificationType] DESC, [caNotificationID] DESC, [NotificationID] DESC, [ntID] DESC 
);