I have a query that does a clustered index scan on a very large table, the scan is causing timeouts in some scenarios. Need help understanding why it is not using the defined nonclustered indexes.
Here is the query:
DECLARE @StartDate datetime = '2023-03-16 00:00:00';
DECLARE @TerminalIds [dbo].[udtBigInt]; -- user defined table with a BIGINT col
INSERT INTO @TerminalIds ([Id])
SELECT [EquipmentId]
FROM #mechanicsTerminal;
SELECT [DataRecId]
, [RawData]
, [RecordingTime]
, [EquipmentId]
FROM [dbo].[Data]
WHERE [EquipmentId] IN (SELECT [Id] FROM @TerminalIds)
AND [RecordingTime] >= @StartDate
ORDER BY [DataRecId] DESC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;
Here is table definition:
CREATE TABLE [dbo].[Data](
[DataRecId] [bigint] IDENTITY(1,1) NOT NULL,
[RawData] [nvarchar](max) NOT NULL,
[CreatedDateUTC] [datetime] NOT NULL,
[RecordingTime] [datetime] NOT NULL,
[EquipmentId] [bigint] NOT NULL,
[DataSetId] [uniqueidentifier] NULL,
[SourceType] [nvarchar](50) NULL,
[Name] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED ( DataRecId] ASC)
GO
ALTER TABLE [EJ].[Data] WITH CHECK ADD CONSTRAINT [chk_Data_RawData] CHECK ((isjson([RawData])=(1)))
GO
Here are the indexes:
CREATE INDEX [nc_Data_DataSetId_includes]
ON [dbo].[Data] ( [DataSetId] ) INCLUDE ( [DataRecId], [RawData], [RecordingTime]);
GO
CREATE INDEX [nc_Data_EquipmentId_includes]
ON [dbo].[Data] ( [EquipmentId] ) INCLUDE ( [DataSetId], [RawData]);
GO
CREATE INDEX [nc_Data_EquipmentId_RecordingTime_Name_includes]
ON [dbo].[Data] ( [EquipmentId], [RecordingTime], [Name] ) INCLUDE ( [DataRecId], [RawData]);
GO
Here is the actual execution plan:
https://www.brentozar.com/pastetheplan/?id=B1oq7TDD3
With this particular data, the query executes sub-second.
However there is a scenario where there is only three records in @TerminalIds
, but no matching records in [dbo].[Data]
, the query never finishes. Here is the plan after 45 sec has passed.
https://www.brentozar.com/pastetheplan/?id=rJJMRavDn
What I've tried:
- Updating stats and recompiling the main proc
- Doing
INNER JOIN
on@TerminalIds
instead of doing subquery withIN
clause
ORDER BY [DataRecId] DESC
can you order byRecordingTime
instead?