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).