Optimized Network design for SQL Server Applications

[libroos]

Hi techies,

Currently I've one SQL Server 2000 which is having very bad performance. There are 30,000 users accessing the SQL database Server everyday. Including via the web over 256K leased line. Everyday the SQL Server would hang whenever there are a lot of synchronization from other systems to the database Server. As a result, the SQL Server needs to be rebooted everyday.

This current SQL Server is running on Pentium 1GHz dual-processor, 1.5GB RAM. However, the access to the SQL Server is still slow.

Are there any good advice to restore the performance of the SQL Server to good health?

Or do we need to configure any load balancing, or clustering network setup for the SQL Server?

All advice are greatly appreciated. Thank you guys..

Rgds,
libroos

 

[tlbroadbent]

You need to monitor the server to determine what is causing the slow performance. Which edition of SQL Server 2000 are you using, Standard or Enterprise?

1) Monitor Processor usage. If processor usage is constantly over 70% for both processors, you may beed to add more processors. You'll need additional processor licenses if you add processors.

2) Cache hit ratio. If the cache hit ratio is low, you may need to add more RAM. I'd recommend doing that not matter what the current numbers show. More memory is good. The amount of memory SQL Server utilizes depends on the edition you have installed.

3) Locking/Blocking. Locking/Blocking can make a server appear slow when the real problem is that connections are waiting for locked resources.

3) Long running queries and procedures. Use SQL Profiler to create traces to monitor which queries and procedures are using a lot of CPU time or taking a long time to complete (duration).

Usaully, poor performance can be fixed by optimizing the applications. This would include proper table design, appropriate indexing of tables, optimization of queries, views and procedures. Optimization of the applications can reduce server load, reduce or eliminate blocking and reduce network traffic.

See my page about SQL Server Performance to find some helpful links and resources.

http://tlbroadbent.home.attbi.com/sql/ss_performance.htm

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[libroos]

Hi Terry,

Thank you for your prompt reply and useful links. : )

I'm using SQL Server 2000 Standard Edition, SP3. All patches installed.

I'm beginning to suspect that the bottleneck could be due to Locking/Blocking.

1. The processor usage ratio for the two processors are quite average, below 70% most of the time, except during synchronization of a lot of workstations, PDAs, systems to the database Server, it'll peak at 90++% or sometimes 100%. The files in use during synchonization are not large.

2. 1.5GB RAM has been installed on the SQL Server.
* I've read on some article that SQL Server would consume all the available memory in the Server and shall release them as and when other resources needs it? Have you come across these articles?

3. Locking-This is the prime suspect that I think. However, how do I determine this and solve for this? Would you like to advise further?

4. Have run the SQL profiler and noticed that there wasn't much querying except during synchronization period. During this period, CPU utilization and memory utilization is exceptionally high.

Are there any methods which I can unlock the SQL Server? I would like to optimize the Server. If else, is clustering network/load balancing setup able to solve for the problem?

I'm suspecting the root of the problem is the SQL application, job itself..

Pls advise further.. thks.

Happy May Day.

Cheers,
libroos

 

[tlbroadbent]

Locking occurs while SQL Server updates tables. You can't force an unlock though you can reduce the impact of locking with the NOLOCK hint on select queries. The best thing to do is to analyze the applications and determine where blocking occurs and why.

See thread962-537348. I provided some links to resources whre you can get some scripts and more information that should be helpful in identifying blocking culprits.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[libroos]

Thank you Terry.

Shall try them out and discuss with my database programmers.. : )

By the way, if were to set up clustering SQL Server, such as setting up MS Advanced Server, and two DB Servers for replication, synchronization, would it help?

Regards,
libroos