Performance issues with SP

Question

Performance issues with SP

sree-226516

SSCrazy

Points: 2590
More actions
December 3, 2013 at 7:38 am

#285642

Hi,
We have a stored procedure which updates table A every 5 mins whole day(9AM to 7PM).Table A has only one day worth data and end of the day it moves all data to Table B.Table B have 80 million rows
which mainiatins all historical data.SP usually finishes in 3 seconds but randomly everyday the execution time increases to 23 seconds and users started getting timeout.I have to run updates stats on table A and table B and recompile SP to get execution back to 3 second.Execution plan is same before and after the issue.We run reindex and updates stas with full scan on both tables everyday morning.Any help is appreciated.Please let me know if you need more details.
Thanks,
Sree
Thanks,
SR

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

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

- look for blocking processes during the moments the stored procedure takes a long(er) time to complete.

- capture the actual query plan of the stored procedure for both executing times (both a 'fast' and a 'slow' run of the stored procedure. Analyse the query plans regarding the estimated and actual rows.)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

sree-226516 SSCrazy Points: 2590 More actions · Answer 2

Thanks for such a quick response.I don't see any locking or blocking during the issue.I also compared execution plan both before and after and its looks exactly same.

Thanks,
SR

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

What happens when you recompile the query after it executed with slow response. Does that generate another queryplan once executed again? P.S.: don't update stats in between...

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

sree-226516 SSCrazy Points: 2590 More actions · Answer 4

Recompile is not creating new execution plan also its not solving the issues.Only after I run update stats,execution time comes back to normal.

Thanks,
SR

Lowell SSC Guru Points: 323518 More actions · Answer 5

sree-226516 (12/3/2013)
Hi,
We have a stored procedure which updates table A every 5 mins whole day(9AM to 7PM).Table A has only one day worth data and end of the day it moves all data to Table B.Table B have 80 million rows
which maintains all historical data.SP usually finishes in 3 seconds but randomly everyday the execution time increases to 23 seconds and users started getting timeout.I have to run updates stats on table A and table B and recompile SP to get execution back to 3 second.Execution plan is same before and after the issue.We run reindex and updates stats with full scan on both tables everyday morning.Any help is appreciated.Please let me know if you need more details.
Thanks,
Sree

it sounds like the statistics are getting stale on those two heavily updated tables.

you can actually check the statistics throughout the day to confirm with DBCC SHOW_STATISTICS Table1, but i think adding a new job, which updates statistics just for those two tables several times a day, in addition to your already existing jobs, is what you need in this case.

Especially on large million row tables, It only takes a small percentage of updates to affect performance due to stale stats.

UPDATE STATISTICS dbo.Table1 WITH FULLSCAN ;

UPDATE STATISTICS dbo.Table2 WITH FULLSCAN ;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

sree-226516 (12/3/2013)
Recompile is not creating new execution plan also its not solving the issues.Only after I run update stats,execution time comes back to normal.

Then you may need to schedule that stats update to run more than once a day. If a stats update fixes the problem and a recompile doesn't, the problem is stale stats and the solution is updating the statistics.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass