SP with Cursor all of sudden very slow

I have a SP which loads about 1.7 million records into a temp table, and then fetches each of those records and performs few checks to update records in physical tables

The part which loads records in temp table is quick - 1 min. however the remaining part is taking very long all of sudden.

Previously, entire SP used to query records in 3 mins. Now it takes 4-5 hours.

Any idea what could be causing this and what should I look for to get this sorted?

Thanks
LVL 1
cynxAsked:
Who is Participating?
 
Jim P.Connect With a Mentor Commented:
If it worked fine before look at what else has changed in the environment as well.

Is a new server connected to the same SAN?

Are you running low on disk space? Even for the tempdb?

When was the last reboot?

Has there been a jump in data?

Has a new column like a varbinary(max) been added even if it is not used?

Just some things to look at.
0
 
Scott PletcherSenior DBACommented:
Impossible to know without seeing query plans.

But you may be able to avoid the cursor and give yourself vastly better speed overall anyway.  Can't say for sure w/o seeing the code.
0
 
cynxAuthor Commented:
could there be issues with memory on the server?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cynxAuthor Commented:
the code is too huge to be posted.
0
 
Koen Van WielinkIT ConsultantCommented:
Like Scott said, it's impossible to know what the problem is without seeing your queries and plans.
The code "being too huge to be posted" is never a good sign... Can you save it to file and attach it?
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You'll have to review at least the estimated query plan (and much more preferably the actual execution plan) to determine where the performance issues are.


>> could there be issues with memory on the server? <<

Most likely is some type of I/O issue, but there "could" be ANY issue on the server.  Can't tell at all w/o a query plan.
0
 
Docteur_ZCommented:
Has an index "disappear" ?
0
All Courses

From novice to tech pro — start learning today.