index scan vs seek

Question

index scan vs seek

Snargables

SSCrazy Eights

Points: 8848
More actions
October 11, 2013 at 10:40 am

#284494

i have a table that has 600 mill recs. i have an update to it that modifies 79 k records a day. Up to last saturday at 8am this process ran in 20-30 mins. Now it's taking 3 hours. I defraged the index it's using because it was fraged 40 %. I just updated stats. Is there anything else i should do?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Snargables (10/11/2013)
i have a table that has 600 mill recs. i have an update to it that modifies 79 k records a day. Up to last saturday at 8am this process ran in 20-30 mins. Now it's taking 3 hours. I defraged the index it's using because it was fraged 40 %. I just updated stats. Is there anything else i should do?

Can you post the actual execution plan? That is the first place to start.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Snargables SSCrazy Eights Points: 8848 More actions · Answer 2

Snargables

SSCrazy Eights

Points: 8848

October 11, 2013 at 11:15 am

#1657984

attached plan

Snargables SSCrazy Eights Points: 8848 More actions · Answer 3

Snargables

SSCrazy Eights

Points: 8848

October 11, 2013 at 11:16 am

#1657985

actually. thats the estimated exe plan

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Snargables (10/11/2013)
i have a table that has 600 mill recs. i have an update to it that modifies 79 k records a day. Up to last saturday at 8am this process ran in 20-30 mins. Now it's taking 3 hours. I defraged the index it's using because it was fraged 40 %. I just updated stats. Is there anything else i should do?

Yes... if the UPDATE are causing individual rows to grow, you need to consider the fill factor of each index (especially the clustered index). If it's 0 or 100%, you'll need to change it to help prevent page and extent splits.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Snargables (10/11/2013)
i have a table that has 600 mill recs. i have an update to it that modifies 79 k records a day. Up to last saturday at 8am this process ran in 20-30 mins. Now it's taking 3 hours. I defraged the index it's using because it was fraged 40 %. I just updated stats. Is there anything else i should do?

That is a sufficiently small fraction such that the most efficient way to do the update is with an index seek. Ensure whatever column(s) you use to interrogate the base table on for the update has a non-clustered index on it/them (or the clustered index if it matches already) and it should be very efficient and fast. If the index exists and IS being used then check for blocking with sp_whoisactive. If the index exists but is NOT being used - force it with a hint.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

Snargables (10/11/2013)
Up to last saturday at 8am this process ran in 20-30 mins. Now it's taking 3 hours.

obviuosly kevin ans sean will give here better idea

something from my side too

sudden performance degrade happens because of sudden activity happened prior. like data migration, cache clean. dropping any index (or even statistics)

any idea if something had happened ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Snargables SSCrazy Eights Points: 8848 More actions · Answer 7

figured i'd close the loop on this one. The issue started a week ago. the update wouldnt do an index seek which i assume it was doing before because it ran considerably faster. Regardless i noticed the index was at 40% fragmentation. i also noticed it was automatically defraged a few hours before all my issues started.

So i defraged the indexs. but still could only get the proc to use a seek if i rewrote the update. I didn't have a problem doing that however really wanted to know what caused the issue. Nonetheless, once the weekend hit i rebuilt the clustered then the nonclustered the updatre was using. i instantly noticed the update using the index seek. It went from 3 hours to 9 mins with this mornings run. The process ended up failing down the line anyhow do a different step growing the tran log so large we ran out of space on a drive. It's like the wild west around here. One thing at a time please

thanks for your help