Click here to Skip to main content
15,312,406 members
Please Sign up or sign in to vote.
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
Updated 25-May-21 22:27pm
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900