I have a query that selects from only one table and with one WHERE filter. However it takes a very long time to execute and even times out occasionally. This is likely because it is filtering about 4 million rows out from a table of 13 million rows (the other 9 million records are older than 2019), and it is returning all of the columns, of which there are 101 (a mix of datetime, varchar, and int columns). It has two indexes, a clustered one on its primary key interaction_id, and an unclustered index on interaction_date which is a datetime column that is the main filter. This is the query:

  FROM [Sales].[dbo].[Interaction] 
  year(Interaction_date) >= 2019

Is there anything obvious I can do to improve this query's performance by adding/tweaking indexes or tweaking the query itself? Before I go into an ETL processes or fight back on the group that needs this query (they are a Hadoop sqooping team who insist they need to sqoop all of these records all the time with all of the columns), I want to see if I can make it easier on people by doing something on my end as the DBA.

The query plan by default ignores my non-clustered index on the interaction_date column and still does a full clustered index scan. So I then tried forcing it to use it by including WITH (INDEX(IX_Interaction_Interaction_Date)) in the select.

This forces it into the query plan startign with an index scan of the non-clustered index, with estimated rows 4 million but estimated rows to be read as all 13 million. Then after a short time it spends the rest of the execution on the key lookup of the primary clustered index.

But ultimately, it doesn't seem to speed up the query at all.

  • 2
    Please consider reading about Asking query performance questions – mustaccio Jun 3 at 19:29
  • 3
    You're getting a key lookup because you're using SELECT * and the index doesn't contain all the columns you need. So you may consider reducing the columns you're returning and/or adding some columns to the INCLUDE portion of the NCIX. Partitioning could help too, as Michael suggested, as long as your predicates usually hit partitioned columns, specify both bounds, and as long as you continue proper maintenance (e.g. sliding window). – Aaron Bertrand Jun 3 at 20:25
  • Thanks, so do I understand it right that basically with a select *, you can never benefit from an index unless all of the columns are included in the index? My problem is that they have 101 columns they feel they need to include, even if I can talk them down to half, it sounds like a 50 column index across 13 million rows would be super slow? – datadawg2000 Jun 4 at 4:51
  • 1
    @datadawg2000 it depends -- if the selectivity of the query is such that it was only going to end up with, say, 1000 rows, then SQL would find those 1000 rows in the nonclustered index and then hit the clustered index to get all the data (a "key lookup"). But if SQL estimates it's going to grab a million rows, then it may decide it's cheaper to just scan the entire clustered index. Be sure your statistics are up-to-date as well...if you run the query and show the actual plan, how does the "actual" number of rows compare to the "estimated" # of rows? – Mark Sowul Jun 4 at 14:08
  • The common solution would be to partition the table by date, which groups rows of the same partition. WHERE Interaction_date >= date '2019-01-01' will result in partition pruning/partition elimination – dnoeth Jun 5 at 11:18

Is there anything obvious I can do to improve this query's performance by adding/tweaking indexes or tweaking the query itself?

Yes. First make the predicate sargable.

WHERE Interaction_date >= '20190101'

And then consider partitioning, or a filtered index with included columns. But even if you have an index that can support this query as a simple seek+scan, sending all the columns to the client takes time.

| improve this answer | |
  • Also make the interaction_date the clustered index and the I'd I ex a normal one – mmmmmm Jun 4 at 10:41
  • Thank you for this recommendation. I did try this and yet the query plan stayed with using only the clustered index scan on the ID column, not this interaction_date column. When I try to force it using WITH(INDEX(Index_name_here)), it shows up in the query plan but then executes in the same time as the other runs. Could be be a problem that I am select * from here? I wish I could declare all the columns and have it not be that many, but for now I am stuck needing to grab all 101 columns. – datadawg2000 Jun 4 at 12:28
  • That's just a time-consuming thing to do. select * is the same as listing all the columns in the query. And the execution time includes the time to send the results to the client, which may be a substantial part of it. – David Browne - Microsoft Jun 4 at 12:48
  • I do agree, the problem is the team who runs this process claim they need all of the 101 columns returned. What I didn't know until now is that an index seems to only work if you are returning columns covered by an index. I thought that merely the index on interaction_date (since it filters where this date is equal or greater than 2019) would at least help speed up this select * compared to where it is now with only the primary key index. But it seems like it doesn't and it remains identical, since I can't have an index with 101 include columns. – datadawg2000 Jun 4 at 14:08
  • You could use a different clustered index or a partition scheme to speed up this query. But it would only reduce the CPU/IO cost of the query. The results wouldn't be sent to the client any faster. – David Browne - Microsoft Jun 4 at 15:45

Note: I'm not going to comment on the query itself, but I'm going to instead talk about something which also impacts this, considering the size of this query.

Some basic math, assuming the average size of data inside varchar columns is 30 characters and columns are roughly split between the 3 types:

101 columns * ((30+8+4)/3) = 14 bytes per column * 4,000,000 rows= roughly 5.6 billion bytes

A little over 5 and a half GB, just for the data (it would take about 86,500 UDP packets to transport at full size). And this is probably a conservative estimate considering the average bytes per column probably is significantly higher than 14 bytes. Every additional average byte per column adds another 400 MB of data transfer needed.

Depending on your connection and assuming direct connection with no other traffic, this would take anywhere from 40 seconds (assuming Gigabit Ethernet) to 400 seconds, or almost 7 minutes, using 100Mbit ethernet purely for this data. Any additional latency, data traffic on the line or other interference will also impact this time needed.

I know your client is using Hadoop and Sqoop, but I'm talking about the physical limits of the connection, and there is not that much that software can do about that. It is entirely possible that most of the time the query takes is just getting it from your SQL server to your Hadoop system. You can check how much the impact is of this by trying to manually copy over a 5.6 GB file from the SQL server to whatever it is Hadoop is running on. That would give a good estimate of the time your query will take to be moved from your SQL machine to your Hadoop machine.

| improve this answer | |

The reason why index is not used is that the selectivity is too small - at the estimated 4M rows out of 13M rows in the table it means that 30% of all records are estimated to be read. Instead of looking up 30% of all the data in random access (through key lookup), it's faster to read everything and filter it in DB engine.

There are basically three options to speed up this query:

  • Columnstore index (clustered or nonclustered with all columns included). Column Interaction_date is likely filled sequentially, so years >=2019 will be filled only in newest row groups. Ideal if your table is mostly read, because writing to a columnstore index is definitely slower compared with rowstores.
  • Clustered index - you already write that your table has one, so it's probably a no-go. But if you have just one date column in your table, it's usually best candidate for clustered index. If you e.g. have clustered index on identity column, change it to non-clustered (the performance impact is usually minimal, although test first...) and change the index on date column to clustered. This will get you the best performance by far, as long as you can do it.
  • Partition. Generally a bad answer unless you know what you're doing, and definitely not recommended if you have just 13M rows. But it can speed up this query.
| improve this answer | |
  • I agree with the Reason part.Creating CI on date column won't help because it will increase index cost and you know if CI cost will increase then optimiser has anther reason to not choose index.So CI on Identity column is perfectly OK.May be NCI on date column with DESC order index help. – KumarHarsh Jun 16 at 4:12
  • Keep in mind that CI is the rowstore, so by definition if you want all columns it has to be used. You might get the data via CI seek or CI scan and a scan will actually be used, but with seek predicate limiting the scan to only part of the sorted rowstore. Also - whether index is asc or desc is completely irrelevant as far as optimizer is concerned - index seek works with O(ln(n)) in both cases. – Petr V Jun 17 at 5:48

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.