Clustered index scan causing the stored procedure to run slow
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
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.