Clustered index scan causing the stored procedure to run slow

Robin35 2017-02-15 20:11:40


I'm planning to tune the stored procedure that is running slow and i can see a clustered index scan on one of the main table that's pulling huge amount of data and causing the report to run slow.

I'm trying to create an additional non clustered index to eliminate scan but kind of confused about onwhat columns to choose and that table is selecting lot of columns and i'm not sure if we can create index on all columns.

any help will be greatly appreaciated.

Thanks in advance

Aaron Bertrand 2017-02-22 19:18:37
Hey Robin, there seem to be a couple of glitches with your plan – most importantly, the only resource-intensive statement doesn't have any plan details. Any chance you can generate another actual plan, and this time don't anonymize it? You can send it to me directly at if you really need to protect table names etc.
Robin35 2017-02-22 20:03:18
Thanks Aaron for the details.I will send you the actual execution plan.

One more thing, I have created a non clustered column store index on this table and it significantly reduced the I/O cost from 73% to 1.09%. We are in SQL 2014 enterprise as you know we NC columnstore is non update able.

Thanks again.,

Robin35 2017-02-22 20:19:51
I saw in MSDN that non clustered column store is non updateable in SQL 2014.
Robin35 2017-02-22 20:20:27
but clustered column store is updateable in SQL 2014
Aaron Bertrand 2017-02-22 20:22:19
Yes sorry I missed that it was non-clustered. So, have you considered clustered columnstore?
Robin35 2017-02-22 21:07:57
Btw, i have sent you the actual execution plan.


Robin35 2017-02-22 21:07:37
Np. I created non clustered colum store index, i dont want to change the clustered index. also this table has so 181 columns. so i'm not sure if we can create clustered column store index.
Aaron Bertrand 2017-02-22 21:11:00
I didn't see a costly clustered index scan on the updated plan you sent – only a clustered index seek with residual I/O. Is this the table that has 181 columns? And this seek only outputs four columns? Certainly sounds like a clustered columnstore index is worth a shot (but you need to measure impacts to other portions of your workload, rather than just this query alone). I would also suggest indexed #temp tables as opposed to table variables.
Robin35 2017-02-22 21:58:16
Thanks Aaron. It is doing clustered index scan and scanning more than 2 million rows. Yes, this table has 181 columns and lot of foreign keys in it. But clustered column store is for all columns rights ? Not sure what's the impact of inserts.
Aaron Bertrand 2017-02-23 18:27:37
You'd have to test impact on write workload. But one of the big benefits of columnstore is much less I/O when you're only selecting a subset of columns – doesn't matter how wide your table is.