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!

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

  • 1
    My guess is that, because [value] is not part of the index, SQL Server has decided that a table scan is cheaper than an index-scan, plus lookups for the [value] column. I'd be curious to know what the estimated number of rows are for the large time-slice (without the [value] column. On a test server, you could try adding the [value] column as an included column of the index. Also, make sure statistics are up-to-date for the table in question. – Scott Hodgin Sep 10 '20 at 9:35
  • Clustered Index vs. Heap in SQL Server has some good information. – Scott Hodgin Sep 10 '20 at 9:44

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

  • But as mentionned by Grant, it's hard to give a good answer with so few information. Please provide the execution plan so that we could have a better understanding of what is happening. P.S. 2 seconds is not that fast for a database P.S #2 : Don't use "With (nolock)" unless you really know what it does and you really need it. – Dominique Boucher Sep 10 '20 at 12:36
  • Few points to notice: 1. In Good case there are still 398,119 rows to read 2. It still do Key Lookup, that means your index not covering all the fields needed 3. In Bad case there are 315,614,942 rows, and matched 1,194,188 (large) So, what are your indexes? – redsimon Sep 14 '20 at 1:43

Without seeing the execution plan to see the row estimates, it's going to be difficult to give you a precise answer.

Probably, the expanded range results in a broad enough set of rows that the optimizer, based on the statistics on your indexes, has determined that a seek is no longer efficient, so it's doing a scan instead. One of the reasons for this is outlined in the comments. Since your non-clustered index doesn't cover the query (cover = have all columns needed either in the key or as part of an INCLUDE), it will have to do a key lookup operation in the clustered index. In general terms, this is a 3:1 read addition to a query. So, as you expand the set of rows, the optimizer figures, it's faster to scan the index and retrieve stuff than to go through the key lookup process.

As to heaps vs. clustered indexes, I have a very simple default. Every table gets a clustered index. Yes, there are exceptions, but the exceptions are just that, exceptional, and well documented why, exactly, they should be heaps. SQL Server is, by and large, engineered around the clustered index. So, best to use them. There are a gajillion details around this that are just too much to cover here. TLDR: Prove you need a heap.

  • Thnx for your nice explained answer, I will edit & put the two Actual-Exec-Plans. About the Clustered, is it really useful when you don't use that column in all your queries, I mean that does it give any performance gain? For example, here we have big tables that only Insertion is done & selects, no delete/update. – MShNajar Sep 10 '20 at 14:08
  • Picking a heap or a clustered index determines data storage. SQL Server is optimized around storing data in a clustered index. The best clustering key is the most common path to the data. If you're doing lots of reads, and you say you are, a clustered index, generally, not always, will outperform a heap. – Grant Fritchey Sep 11 '20 at 11:53
  • You've posted pictures of execution plans, not execution plans. There's not much more to say beyond what I said above based on what you provided. You can see that it is doing a key lookup instead of a scan. The main point is, fewer rows in the range means it can choose the nonclustered index rather than scan the bigger one. This is pretty standard behavior. – Grant Fritchey Sep 11 '20 at 11:56
  • Didn't get what you meant @Grant, these are the actual exec-plans, do you mean the estimated exec-pans? Also the wider range has 3 or 4 times more rows, actually the narrow range includes 300 Thousand & the wider 1M rows. So the data count is not much more, comparing 315M rows for the table! So why should it really go for Table Scan(Clustered) instead of Index Seek(NonClustered)? It still hard to understand – MShNajar Sep 11 '20 at 16:22
  • No, those are just pictures of execution plans. The XML that defines an execution plan has a ton of data (all available in the properties of the objects in the plan) that expose all the underlying info. So, just posting a plan shape in a picture is not sharing the plan. That's what I meant by that bit. – Grant Fritchey Sep 14 '20 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.