Sql-server – Why Whole Table Scan (Clustered Index)

execution-planindexsql server

I have a big table with 315M rows. with these columns & Index :

[key_] (int & IDENTITY(1,1)), [time_] (datetime), [value_] (float), [quality_] (int)

PK & Clustered-Index: key_ , Non-Clustered-Index on: time_ + quality_

Actually the main select that we use here is as bellow. This select gives good result, in 2Secs, but when I change the range & make it wider (like 36 hours), it responds in 6Minutes!

SELECT TOP (1000)
     ,[time_]
     ,[value_]
     ,[quality_]
    FROM [DataHubRev2].[dbo].[tb_HPGR_120MI01ME01_Power]
    WHERE quality_=192 AND time_ >' 2020-03-27 19:00:01'AND time_ <' 2020-03-28 07:00:00'
                           --- 12 hours: Good Result
--- WHERE quality_=192 AND time_ >' 2020-03-27 07:00:01'AND time_ <' 2020-03-28 19:00:00'
--- 36 hours: Bad Result

It is clear that by using this select & where, SQL-Engine should go for NonClusteredIndex (at least I think so). When I look at the 'Actual Execution Plan' the first one goes directly to the NonClustIndex Seek, but the second one goes for ClusteredIndex Scan & the readed rows tells 315M (the entire table)!!
Interesting part is that when I remove ',[value_]' from the select or add the Hint to force NonClustIndex use (With (INDEX = [IX_TimeQuality_Index])) response time & exec-plan is solved. But I need a more deep solution & actually why this is happening.

Also tell me that: making my table as Heap & not having any Clustered Index on ID which I don't use on my selects is a good practice or not?

Edit:
These are the Actual Execution Plans, you could see that the second one scans all the table (Number of rows read).

Best Answer

Wild Guess only: Use quality_ + time_ instead time_ + quality_ and include (value_) ?