Clustered index scan causing the stored procedure to run slow

Hi,

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

Slow_Plan.pesession (386.9 kB)
avatar image By Robin35 1 asked Feb 15 at 08:11 PM
more ▼
(comments are locked)

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 abertrand@sentryone.com if you really need to protect table names etc.

10|10000 characters needed characters left

1 answer: sort voted first

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

avatar image By Robin35 1 answered Feb 22 at 08:03 PM
more ▼
(comments are locked)
avatar image Robin35 4 days ago

I saw in MSDN that non clustered column store is non updateable in SQL 2014.

avatar image Robin35 4 days ago

but clustered column store is updateable in SQL 2014

Yes sorry I missed that it was non-clustered. So, have you considered clustered columnstore?

avatar image Robin35 4 days ago

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.

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.

avatar image Robin35 3 days ago +

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.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x527
x343
x13
x3
x1

asked: Feb 15 at 08:11 PM

Seen: 44 times

Last Updated: 3 days ago