Suddenly timeout error occur in my application

Question

1.00/5 (1 vote)

See more:

we are using mssql standard edition version
suddenly the company database website getting slower and sometimes result a timeout error/

Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

we already have a large data. it almost 4 TB. and we are connecting to multiple database in one instance. ive read that sql standard edition has a limit in terms of memory usage and others. do you think if i will upgrade the sql standard to enterprise can resolved the problem?

What I have tried:

we already tune up the code. but no lock.

Posted 25-May-21 22:14pm

Member 12960626

Updated 25-May-21 22:27pm

Add a Solution

Comments

Chris Copeland 26-May-21 4:36am

I would suggest that a database becoming progressively slower over time would indicate some sort of database connection leak issue. Have you run any SQL on the database to check on the number of active connections to make sure you're not running out of connection resources? Here is an example

Member 12960626 26-May-21 4:46am

yeah we already do that. we already have many user everyday and the system is running 24/7. we suspecting the large amount of data which maybe sql standard difficult to execute query to the client. but when we notice something when we are testing in the midnight which is we are sure no client accessing the system, it run smoothly and faster. any suggestions?

Chris Copeland 26-May-21 4:56am

I'd suggest also looking at the resources of the machine that the server is hosted on as well. Increased users slowing down the server is usually a sign that either the machine itself or the version of SQL Server just isn't able to cope with the increased throughput.

This article seems to summarise quite well the differences between the versions. SQL Server Standard seems to support up to 4 CPU sockets or 24 cores, and 100GB+ memory for caching, so is your machine utilising these maximums? If not then you should consider the machine being upgraded before upgrading the SQL Server version.

SQL Server Enterprise simply unlocks a lot of the limitations found in Standard, so if you're not pushing the Standard edition to the maximum then there's no point in upgrading.

Member 12960626 26-May-21 5:06am

yeah the machine is higher the core and memory, which maybe the sql standard cant max the capacity of the machine because of its limitation, we are planning to upgrade to enterprise but our bosses need an explanation why we should need to upgrade. they are suspecting the network but our network administrator positive to the design of the network.

Chris Copeland 26-May-21 5:13am

Upgrading is fine if the extra performance can be squeezed out of it. But care needs to be taken to investigate all other avenues as well, as an upgrade could be costly and not actually improve anything.

Consider also looking at things like: running queries and their average execution times, table and row locks, average connection lifetime, I/O read and write times to the hard-drive. All these things can generally be monitored in SQL Server and may help identify points and resources which may need addressing.

I'm not trying to dissuade your from the upgrade, just that it could be disappointing if you upgrade and very little changes. Best to be prepared.

Member 12960626 26-May-21 20:05pm

thank you for the advice we will try to proper monitoring.

Chris Copeland 26-May-21 4:57am

That being said, I also want to point out that it may not just be hardware. There's also considerations like your network quality, what is your bandwidth like? Slow connection requests + responses could be causing locks on the database.

SeanChupas 26-May-21 8:04am

Make sure you have proper indexes. Use monitoring tools to find the slow queries.

Member 12960626 27-May-21 6:59am

maybe this will be the solution thank you a lot for all the replies we will check it by indexing the database properly.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)