Azure SQL query never completes

Question

I am having an issue with a SQL query that runs and runs but never completes. The database is an Azure SQL database.

My table has tens of millions of rows and looks like this:

CREATE TABLE [dbo].[MyData](
[CustomerId] [int] NOT NULL,
[TagName] [nvarchar](100) NOT NULL,
[TagValue] [real] NULL,
[TimeStamp] [datetime2](7) NOT NULL,
[status] [int] NULL,
CONSTRAINT [PK_MyData] PRIMARY KEY CLUSTERED 
(
  [CustomerId] ASC,
  [TagName] ASC,
  [TimeStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

My select statement looks like this:

DECLARE @StartDate as datetime
DECLARE @EndDate as datetime

Select @StartDate = LastReadDT from MyData_LastDT_Read
Select @EndDate = dateadd(minute,30,@StartDate)

SELECT   CAST([TagName] as varchar(100)) as [tag], 
         CAST(isnull(TagValue, 0) as real) as tagvalue, 
         CAST([TimeStamp] as datetime) as [DataTimeStamp], 
         CAST(192 as int) as [status]
FROM     [dbo].[MyData]
where    CustomerID = 1 
and      TimeStamp <= @EndDate 
and      TimeStamp > @StartDate
order by TimeStamp asc

The strange thing is if I remove the @StartDate and @EndDate variables and instead just hard-code the actual TimeStamp values in the where clause, the query will return results in less than 1 second. Why would hard-coding the TimeStamp values in the query make such a dramatic difference compared to the variables? Any suggestions on how to improve the performance? Unfortunately I need to use the variables as this query is executed repeatedly with different time ranges.

score 3 · Accepted Answer · answered Jun 21 '17 at 18:26

This has a scent of a parameter sniffing problem. See what I did there? Punny, right?

Anyway, Paul White, wrote up a great article on the various approaches available to resolve this issue, so I'm not going to go into detail when you should just read his article instead. What you can try quickly though, because this is an ad-hoc statement, is to force a recompile using the OPTION (RECOMPILE) hint. If this fixes your issue, you definitely are running into what I suspect. However the best way to properly resolve this is to convert your statement into a Stored Procedure and then execute the Stored Procedure passing in the date parameters. A parameterized Stored Procedure won't fall prey to the current issue and should be reusable as well.

Also if you typically access all tags for a customer within a time range, then changing the index key order to ([CustomerId], [TimeStamp],[TagName]) would be better. Or given the current key structure, you could partition the table by TimeStamp. — David Browne - Microsoft, Jun 21 '17 at 19:11

paparazzo · Answer 2 · 2017-06-21T20:39:34.573

This is nondeterministic unless that table has 0 or 1 row

Select @StartDate = LastReadDT from MyData_LastDT_Read

Why not just join

SELECT CAST([TagName] as varchar(100)) as [tag] 
     , CAST(isnull(TagValue, 0) as real) as tagvalue 
     , CAST([TimeStamp] as datetime) as [DataTimeStamp], CAST(192 as int) as [status]
FROM [dbo].[MyData] 
join MyData_LastDT_Read
  on CustomerID = 1 
 and TimeStamp <= dateadd(minute, 30, LastReadDT)  
 and TimeStamp >  LastReadDT 
order by TimeStamp asc

Azure SQL query never completes

2 Answers2