0

I have a simple query but i don't understand the behavior of the Top Clause.

SELECT Top (1) Timestmp 
FROM DataTable 
WHERE [LoadId] = 3104 
  AND [Temp1] > @Setpoint
  AND [Temp2] > @Setpoint
  AND [Temp3] > @Setpoint
  AND [Temp4] > @Setpoint
  AND [Temp5] > @Setpoint 
ORDER BY Timestmp  OPTION (RECOMPILE)

(*query is a easier version of the ones in the Execution Plan, but altogether the same)

  • There is a common Non-Clustered Index on (LoadId, Temp1, Temp2, Temp3, Temp4, Temp5)
  • And a separate Non-clustered Index on Timestmp

The query takes about 1 Min to finish. But if i write Top (100) then it takes some [ms] to finish.

Here are some statistics of the Queries:

  • Top (1) :logical reads 25757671, physical reads 148582, read-ahead reads 17695
  • Top (100) :logical reads 290703, physical reads 0, read-ahead reads 0

Top (1) : https://www.brentozar.com/pastetheplan/?id=B1zwx0klN

Top (100) : https://www.brentozar.com/pastetheplan/?id=Hkx5k0JgE

How can I get Top (1) as fast as Top (100) and why is it that slow? I never saw that behavior before on other Servers, maybe its something wrong on the SQLServer Settings?

archon
  • 13
  • 1
  • 3
  • 3
    Instead of an image, upload your plan xml to https://www.brentozar.com/pastetheplan/ and add the link to your question. Also, upload the `TOP (100)` plan. – Dan Guzman Dec 13 '18 at 12:12
  • 2
    Please [include the actual Execution Plan](https://stackoverflow.com/a/7359705/1260204), you could use [Paste the Plan](https://www.brentozar.com/pastetheplan/) and share the link in your question. Do this for both queries (top 1 and top 100). – Igor Dec 13 '18 at 12:13
  • It seems your top(100) query runs clustered index scan instead of scan+lookup. And it is faster because of using buffer without physical reads. – Denis Rubashkin Dec 13 '18 at 12:15
  • 1
    You should check out the green information message in your SSMS execution plan window - it **tells you** there's an index missing which would reduce query time by over 90% - check that out! – marc_s Dec 13 '18 at 12:24
  • Thank you marc_s that was the solution :) Have created a Index with INCLUDE Timestmp , now it is also fast with top (1) – archon Dec 13 '18 at 12:38
  • Do you maintain your indexes and statistics? Estimated row count for the index on Timestmp is terribly wrong – Denis Rubashkin Dec 13 '18 at 12:43
  • @archon, the query in your question specifies `WHERE [LoadId] = 3104` but the actual query is `WHERE [LoadIdLowInt] = 3104`. The optimal index needs [LoadIdLowInt] as the leftmost index column for the equality predicate (the suggested index). The faster TOP (100) used a different index for the inequality predicates, which was better than the full scan but not as good as the suggested one. The general indexing practice is to specify equality columns first, followed by inequality columns, and finally included columns when needed. – Dan Guzman Dec 13 '18 at 12:45

1 Answers1

0

Solution:

After checking out the green information message in the SSMS execution plan window, there was a missing index with a INCLUDE of the selected Column.

CREATE NONCLUSTERED INDEX [] ON [dbo].[FurnaceData] ([LoadIdLowInt],[LoadIdHighInt],[ControlTempZ1PV],[ControlTempZ2PV],[ControlTempZ3PV],[ControlTempZ4PV],[ControlTempZ5PV]) INCLUDE ([Timestmp])

archon
  • 13
  • 1
  • 3
  • Welcome to Stack Overflow, archon. I'm glad you found your answer. Please take a moment to click the grey check mark next to your answer so that bozos like me don't see the question as unanswered in search and click into it hoping to be able to provide an answer. :) – Eric Brandt Dec 13 '18 at 14:24
  • I will, but i am not allowed to, until the day after tomorrow ;) – archon Dec 13 '18 at 15:48
  • I did not know that! (Guess who’s never answered his own question?) Thanks! – Eric Brandt Dec 13 '18 at 15:50