1

I have a application, which runs for the entire hour, every hour of every day. It basically has 400ish 'projects' to execute within each hour, and loops over the scripts, which execute stored procedures, to complete the task.

Just as an overview, the application is querying the database for data, extracting that data, and writing to a file. The final stage of that is doing at the application layer.

For whatever reason, today, the database has been suffering from constant connection timeout issues. The time in which they happen varies, but it's often 8 minutes in - so after some 'projects' have completed their cycle. It always fails when the application moves on to find the information relevant to the next 'Project' - which is a basic script which says "what haven't I executed yet?".

I've tried extending the connection timeout from 20 seconds to 60 - this hasn't helped.

I have noticed the Virtual Machine is running low on memory, due to the host running out of physical memory. I'm not sure if memory can cause an issue regarding connections - I have given the VM a little more by taking it away from another VM.

This system has been running for months without issue (apart from maybe the odd connection issue every now and then).

Other than the above, is there anything else I should be looking at and/or thinking of?

Improve this question

1 Answer 1

Reset to default
1

Ignoring both physical and VM memory issues for now.

Queries that take longer and longer to run can be caused by several things, but I will guess that you perhaps do have regular index and stats maintenance?

Improve this answer
2
  • I do. I can't remember off the top of my head how often they do it. I believe the Indexing may be weekly. That being said, the scripts that are currently running (or trying to run), are querying against a table that has just had about 3 million new records, and probably 7 million updated records. There are 18 million records in total. Perhaps the query is really struggling as that table is in need of reindexing.
    – DARKOCEAN
    Commented Jul 28, 2017 at 8:32
  • 1
    Further to the above - manually doing the index rebuilds and stats has solved the issue. As I thought, after reading your answer, the insert and update of millions of records caused the server to be a little upset with me and wanted some tender loving care before querying the data. I will now either increase the frequency of index maintenance, or ensure this task is completed prior to querying the data next month. Thanks!
    – DARKOCEAN
    Commented Jul 28, 2017 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.