SQL Server issue with SELECT and datetime2

Question

I found a big difference of the query execution under MS SQL Server Standart 2019.

T-SQL

DECLARE @atTime datetime2 = '2022-05-04 13:23:20';
DECLARE @startTime datetime2;
DECLARE @shiftTime datetime2;

SET @startTime = @atTime;   
SET @shiftTime = DATEADD(SECOND, -5, @atTime)  

-- SELECT @shiftTime, @startTime
-- 2022-05-04 13:23:15.0000000  2022-05-04 13:23:20.0000000

-- #1 It takes 7 seconds to complete
SELECT TOP(1) * FROM [TrackerPositions] WITH(NOLOCK) WHERE AtTime BETWEEN @shiftTime AND @startTime 

-- #1 It takes 0 seconds to complete
SELECT TOP(1)   * FROM [TrackerPositions] WITH(NOLOCK) WHERE AtTime BETWEEN '2022-05-04 13:23:15.0000000' AND '2022-05-04 13:23:20.0000000'

Note: AtTime colum has datetime2

Please, help to get working fast SELECT #1

Thank you!

UPDATE #1

CREATE TABLE [dbo].[TrackerPositions](
    [ID] [uniqueidentifier] NOT NULL,
    [GPSTrackerID] [int] NOT NULL,
    [AtTime] [datetime2](7) NOT NULL,
    [Lat] [decimal](9, 6) NOT NULL,
    [Lng] [decimal](9, 6) NOT NULL,
    [GeoLocation]  AS ([geography]::STGeomFromText(((('POINT('+CONVERT([varchar](20),[Lng],0))+' ')+CONVERT([varchar](20),[Lat],0))+')',(4326))),
    [SignalLevel] [int] NULL,
    [IPAddress] [nvarchar](40) NULL,
    [Port] [int] NULL,
    [Height] [int] NULL,
    [IsMoving] [bit] NULL,
    [Speed] [decimal](18, 4) NULL,
 CONSTRAINT [PK_TrackerPositions] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TrackerPositions] ADD  CONSTRAINT [DF_TrackerPositions_ID]  DEFAULT (newid()) FOR [ID]
GO

ALTER TABLE [dbo].[TrackerPositions] ADD  CONSTRAINT [DF_TrackerPositions_IsMoving]  DEFAULT ((0)) FOR [IsMoving]
GO

ALTER TABLE [dbo].[TrackerPositions]  WITH CHECK ADD  CONSTRAINT [FK_TrackerPositions_GPSTrackers] FOREIGN KEY([GPSTrackerID])
REFERENCES [dbo].[GPSTrackers] ([ID])
GO

ALTER TABLE [dbo].[TrackerPositions] CHECK CONSTRAINT [FK_TrackerPositions_GPSTrackers]
GO

Out of interest, what are the timings when you reverse the SELECT statements? — DeanOC, May 04 '22 at 07:11
@DeanOC It is about 5-10 seconds but it doesn't matter the SELECT's order, indexes and table size, and even with `datetime` it works the same manner. — NoWar, May 04 '22 at 07:14
Performance questions can seldom be answered without the plan. Please provide us the execution plan here: https://www.brentozar.com/pastetheplan/ — George Menoutis, May 04 '22 at 07:22
The reason I asked is that when you execute (essentially) the same query twice, the second time often takes 0 seconds because SQL Server has cached the query plan from the first one. Put another way, what happens if you append OPTION (RECOMPILE) to each? — DeanOC, May 04 '22 at 07:25
This question is not answerable without query plans, and table and index defintions. — Charlieface, May 04 '22 at 16:52
Why `SELECT *` perhaps you should limit your selected columns, then create an index on `AtTime` covering those columns — Charlieface, May 04 '22 at 16:53
Indeed appears that there is no index to cover the query. Either add more `INCLUDE` columns to `IX_TrackerPositions`, or only select columns available in that index rather than `*`. And remove that `NOLOCK` it will just cause incorrect results. Consider using Snapshot Isolation instead — Charlieface, May 05 '22 at 13:49

score -1 · Answer 1 · answered May 04 '22 at 07:24

-1

The right answer is to use OPTION(RECOMPILE)

SELECT TOP(1) * FROM [TrackerPositions] WITH(NOLOCK) WHERE AtTime BETWEEN @shiftTime AND @startTime  OPTION(RECOMPILE)

answered May 04 '22 at 07:24

NoWar

36,338
80
323
498

1

This will certainly help get the wrong results faster. I suggest @NoWar remove the `NOLOCK` hint to help ensure rows are not missed or duplicated in the results. – Dan Guzman May 04 '22 at 08:51
@DanGuzman I can't exclude `NOLOCK` coz of intensive inserted operations on that table, – NoWar May 05 '22 at 01:13
1

that's why we have snapshot isolation and RCSI (on by default in Azure SQL Database) – Dan Guzman May 05 '22 at 12:56

SQL Server issue with SELECT and datetime2

1 Answers1