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?
Best Answer
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?