Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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
0
cynx
Asked:
cynx
2 Solutions
 
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
 
cynxAuthor Commented:
the code is too huge to be posted.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 PletcherSenior 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
 
Jim P.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
 
Docteur_ZCommented:
Has an index "disappear" ?
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now