1

I'm using SQL Server 2016

Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64) Jul 31 2020 18:47:07 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

I have this table that has two Datetime fields, a StartDate and an EndDate. Here's a simplified version.

CREATE TABLE Seg (
   Id int not null identity(1,1), 
   StartDate datetime not null,
   EndDate datetime not null,
   ModeType tinyint not null,
   TripId int not null,
   constraint Pk_Segs primary key clustered  (Id))

CREATE INDEX [Ix_1] ON [dbo].Seg (ModeType, EndDate, StartDate) include (TripId)
CREATE INDEX [ix_2] ON [dbo].Seg ( [EndDate], [StartDate], [ModeType] ) INCLUDE ( TripId) 
CREATE INDEX Ix_3 ON [dbo].Seg ( [StartDate], [EndDate] ) INCLUDE ( Id)


INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-11 14:57:33.000', '2019-11-11 19:53:28.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-11 19:53:28.000', '2019-11-11 23:29:00.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-11 23:29:00.000', '2019-11-12 05:00:00.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-12 05:00:00.000', '2019-11-13 05:00:00.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-13 05:00:00.000', '2019-11-14 05:00:00.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-14 05:00:00.000', '2019-11-14 19:23:49.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-14 19:23:49.000', '2019-11-15 05:00:00.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:27:54.000', '2019-11-15 22:28:59.450',1,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:28:59.450', '2019-11-15 22:29:13.000',3,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:29:13.000', '2019-11-15 22:29:13.000',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:29:13.000', '2019-11-15 22:31:21.800',1,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:31:21.800', '2019-11-15 22:31:45.000',3,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:31:45.000', '2019-11-15 22:31:46.000',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:31:46.000', '2019-11-15 22:36:56.000',1,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:36:56.000', '2019-11-15 22:37:23.700',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:34:23.000', '2019-11-15 22:42:30.400',1,262853)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:37:23.700', '2019-11-16 01:20:36.000',3,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-16 01:20:36.000', '2019-11-16 01:20:39.750',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-16 01:20:39.750', '2019-11-16 01:20:41.000',3,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-16 01:20:41.000', '2019-11-16 01:20:42.000',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-16 01:20:42.000', '2019-11-16 01:30:09.000',1,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-16 01:30:09.000', '2019-11-16 01:31:28.150',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-19 18:38:00.000', '2020-12-19 20:12:13.290',1,284621)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-19 20:12:13.290', '2020-12-19 20:13:30.000',3,284621)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-19 20:13:30.000', '2020-12-19 21:52:19.250',1,284621)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-19 22:05:48.000', '2020-12-19 22:08:35.450',1,284622)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-19 22:09:22.000', '2020-12-20 02:26:18.710',1,284620)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-20 02:56:58.000', '2020-12-20 05:00:00.000',1,284619)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-20 05:00:00.000', '2020-12-20 07:06:13.157',1,284619)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-23 13:49:15.000', '2020-12-23 14:05:18.577',1,284586)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-23 15:40:21.000', '2020-12-23 16:01:23.640',1,284587)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-23 18:08:36.000', '2020-12-23 18:15:48.300',1,284603)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 13:40:32.540', '2020-08-26 13:44:19.000',3,281725)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 13:44:19.000', '2020-08-26 13:44:49.600',1,281725)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 10:22:24.000', '2020-08-26 17:17:48.300',1,281726)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 17:39:36.000', '2020-08-26 17:42:33.600',1,281727)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 17:43:55.000', '2020-08-26 17:46:14.750',1,281728)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 18:00:21.000', '2020-08-26 18:01:18.500',1,281729)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 18:01:18.500', '2020-08-26 20:35:54.000',3,281729)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 20:35:54.000', '2020-08-26 20:51:27.960',1,281729)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 20:26:57.000', '2020-08-26 20:52:26.560',1,281730)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 17:46:46.000', '2020-08-26 21:08:10.550',1,281731)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 20:53:17.000', '2020-08-26 21:18:17.057',1,281732)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 21:59:01.000', '2020-08-27 04:00:00.000',1,281733)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 04:00:00.000', '2020-08-27 04:18:42.063',1,281733)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 06:51:17.000', '2020-08-27 07:43:41.857',1,281734)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 07:52:51.000', '2020-08-27 07:55:01.100',1,281735)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 08:00:06.000', '2020-08-27 09:36:30.210',1,281736)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 13:37:20.000', '2020-08-27 13:51:20.210',1,281737)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 10:51:09.000', '2020-08-27 14:26:19.077',1,281739)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 14:26:19.077', '2020-08-27 14:30:44.000',3,281739)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 14:19:13.000', '2020-08-27 14:31:42.510',1,281738)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 14:30:44.000', '2020-08-27 14:39:14.127',1,281739)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 14:47:25.000', '2020-08-27 14:52:59.150',1,281740)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-25 23:03:36.000', '2020-08-27 15:08:29.000',3,281742)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 12:54:53.000', '2020-07-31 18:25:59.000',3,281565)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 18:25:59.000', '2020-07-31 18:32:30.000',3,281566)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 16:48:17.000', '2020-07-31 18:52:14.000',3,281098)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 20:37:33.000', '2020-07-31 20:45:18.850',1,280884)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 21:13:38.000', '2020-07-31 21:15:49.400',1,280885)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 21:20:55.000', '2020-07-31 21:25:23.350',1,280886)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 22:43:12.000', '2020-07-31 23:17:46.900',1,280887)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 21:39:19.000', '2020-07-31 23:47:38.030',1,280888)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 00:23:50.000', '2020-08-01 04:00:00.000',1,280889)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 04:00:00.000', '2020-08-01 04:59:16.980',1,280889)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 05:14:49.000', '2020-08-01 07:20:22.377',1,280890)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 07:39:50.000', '2020-08-01 07:42:20.650',1,280891)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 07:54:16.000', '2020-08-01 11:18:42.240',1,280892)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 11:18:42.240', '2020-08-01 11:20:43.000',3,280892)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 11:20:43.000', '2020-08-01 11:46:42.987',1,280892)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 13:35:33.000', '2020-08-01 13:44:34.950',1,280893)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 13:54:45.000', '2020-08-01 13:55:59.850',1,280894)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 13:58:03.000', '2020-08-01 14:03:40.350',1,280895)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 14:11:21.000', '2020-08-01 14:13:16.000',1,280896)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 14:28:20.000', '2020-08-01 14:36:25.740',1,280898)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 14:36:25.740', '2020-    08-01 14:36:27.000',3,280898)

The sample table is not quite big but I achieve the same query plans that in production. This table has about 50 million of rows. We have a query for a report that's taking quite a long time to run.

SELECT s.TripId, s.StartDate
FROM Seg s
WHERE s.StartDate <= '2020-08-01 04:00:00' AND s.EndDate > '2020-08-01 04:00:00' 
AND s.ModeType in (1,2)

The query by itself doesn't take "that" long to execute but the server is very busy and from time to time I get deadlocks with other queries from the system.

Query plan

I would like to be able to able to remove the predicate and only have a seek predicate, and reduce the number of reads because right now it reads over 3 millions rows to retrieve 155 rows.

reads

Here is the query plan (from the production database)

I could be able to change things in the code or the database in order to make this work. Any help would be appreciated. I've already tried to fine tune the index or play with the query but I'm not getting anywhere.

************************ UPDATE ******************************************

I've tried the query proposed by J.D.

It does give me a different execution plan but the estimates are still way off. Here is the query plan.

Maybe a query hint would help? Database compatibility level is 130.

I've tried to use the query hint

OPTION (FAST 100)

The cardinality estimates are better, since I tell basically SQL to expect 100 rows, but the reads doesn't change. Updating statistics doesn't change anything (they are updated every night).

Maybe there's no way to have a better result with such a big table?

******************************* EDIT 2 my solution ********************************

Just in case it can help someone with the same problem. Limiting the date range as proposed in the second solution of this question solved my problems. I'll also re-write the query as J.D. proposed so I'll mark his answer as the answer.

WHERE '2020-08-01 04:00:00' BETWEEN s.StartDate AND s.EndDate AND s.StartDate >= '2020-07-01 04:00:00' AND s.EndDate <= '2020-09-01 04:00:00' ;
Danielle Paquette-Harvey
  • 2,099
  • 1
  • 17
  • 31

1 Answers1

2

If you re-write your query like this, does it get better?

SELECT s.TripId, s.StartDate
FROM Seg s
WHERE s.StartDate <= '2020-08-01 04:00:00' AND s.EndDate > '2020-08-01 04:00:00' 
AND s.ModeType = 1

UNION ALL


SELECT s.TripId, s.StartDate
FROM Seg s
WHERE s.StartDate <= '2020-08-01 04:00:00' AND s.EndDate > '2020-08-01 04:00:00' 
AND s.ModeType = 2

Your execution plan is showing a huge cardinality estimate issue. It's estimating 2.5 million rows to be returned by that index seek operation when it really only returns 155 rows.

This is just a guess from looking at your predicates, but the IN keyword is syntactically equivalent to using OR. Sometimes using OR based logic can cause cardinality estimate issues (though in your current query it's rather simple logic, so I'm not sold it's the issue yet).

The above query should be logically equivalent to your previous one, but could fix your cardinality estimate issue by being a more set-based solution, and it should generate a different execution plan at least.

J.D.
  • 40,776
  • 12
  • 62
  • 141