Performance Problem

we use a SQL Server 2000 (Standard Edition) as Database for our Web-Application
accessible for approx. 2200 users in a VPN. Web-Server/Application are
balanced by 2 Servers. The database resides on a 3rd Server.
In peak times we register 400 concurrent open user sessions which might
access the database.
Severs are running 24 hours but working hours are from 8 am to 8 pm.
Our problem is that during the day the Database server performance slows down
more and more. Response time for a complex SQL statement takes 1/2 a second in
the morning but after midday it often takes up to 50 seconds.
One thing we found out is that 4 GB physical memory are available and
the max server memory setting in SQL Server defaults to this value although the
standard edition supports only 2 GB of memory.
As servers are administered by our customer themselfes, we are only allowed
to give them some hints. That's the reason why we first have to do a brainstorming.
- How does the server handle the "wrong" memory settings?
- time spans from .5 to 50 seconds seem to be very large, is this usual?
- is a concurrent access of 400 sessions a possible explanation?
- Are there significant differences between Standard and Enterprise Edition
regarding Performance, Memory Management etc.?
- What other reasons for response time increases are possible?
Any statements would be appreciated.
Regards
Karl Bond
Hi Karl,
From your descriptions, I understood that your are suffering performance issues with your SQL
Server for about 400 connections in peak times. Have I understood you? If there is anything I
misunderstood, please feel free to let me know.
First of all, please understand that performance issues are very hard to troubleshooting in the
newsgroup as we need to collect sufficient information, such as performance counter,
SQLDiag, etc., from your SQL Server. Looking at the nature of this issue, it would require
intensive troubleshooting which would be done quickly and effectively with direct assistance
from a Microsoft Support Professional through Microsoft Product Support Services. You can
contact Microsoft Product Support directly to discuss additional support options you may
have available, by contacting us at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top.
Secondly, many factors may lead into performance issues, such as network performance,
bottle-neck of your product server, etc. I would like to recommand you the following KB, which
will show you how to troubleshooting performance issues in SQL Server yourself
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/default...b;en-us;298475
Monitoring Server Performance and Activity
http://msdn.microsoft.com/library/de...l/ad_mon_perf_
00mr.asp
I noticed you are using SQL Server 2000, however, the following white paper is so excellent
that it is still suitable for today's performance tuning, I would like to strongly recommand you
reading it throughly if I may
INF: SQL Server 7.0 Performance Tuning Guide (White Paper)
http://support.microsoft.com/default...b;en-us;322883
Thirdly, The following KB will help you how to exceed the limitation of 2 Gigabytes memory of
SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/default...b;en-us;274750
INF: How To Enable Analysis Server to Use 3 GB of RAM
http://support.microsoft.com/default...b;en-us;295443
Fourthly, I would like to show your the difference between SQL Server 2000 standard Edition
and SQL Server 2000 Enterprise Edition. The following document listed all features between
them
Features by Edition
http://www.microsoft.com/sql/evaluat...s/choosing.asp
Moreover, the following documents will help you finding out which edition of SQL Server is the
most suitale one for you. You could apply your product envirnment with the document. Hope it
will help you making the right chooice.
Choosing an Edition of SQL Server 2000
http://www.microsoft.com/sql/techinf...skChooseEd.asp
Last but not the least, it is highly appreciated if you could create one thread per question,
which, I believe, will make us focus on one issue and make better troubleshooting
Thank you for your patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Karl,
I wanted to post a quick note to see regarding the information I have sent
you. If you would like additional assistance or information regarding this
particular issue, don't hesitate to let us know. We appreciate your
patience and look forward to hearing from you!
Thank you for your patience and cooperation.
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Karl,
Generally Mingqing's reply is very helpful for troubleshooting SQL
performance issues and it is best if the customer can contact Microsoft PSS
if they want better follow-up.
Hereby I would like to answer your specific concerns, so you can provide
some hints to the customer.
- How does the server handle the "wrong" memory settings?
A: I do not understand this question very well. However, SQL Server 2000
Standard Edition does not support more than 2GB memory.
- time spans from .5 to 50 seconds seem to be very large, is this usual?
- is a concurrent access of 400 sessions a possible explanation?
A: It is very likely caused by the concurrent access. I can illustrate with
an example. Say your application needs to execute a stored procedure, which
does the following:
1. update table a
2. update table b
When multiple access is concurrently executing, if 2 sessions are executing
the above stored procedure at the same time, then 1 session has to wait for
another session (they cannot update table a at the same time. It will cause
inconsistency issue.). So session 2 will take twice time as usual to
execute the stored procedure. The more concurrent sessions, the longer it
will take.
- Are there significant differences between Standard and Enterprise Edition
regarding Performance, Memory Management etc.?
A: There is no difference on this part. However, different editions support
different features. Enterprise Edition can support more than 2GB memory.
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_1cdv.asp
- What other reasons for response time increases are possible?
A: You can refer to the following articles also.
243589.KB.EN-US HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0
or La
http://support.microsoft.com/default...B;EN-US;243589
224453.KB.EN-US INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Probl
http://support.microsoft.com/default...B;EN-US;224453
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Bill Cheng
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided as is with no warranties and confers no rights.
| Thread-Topic: Performance Problem
| thread-index: AcRv9lgDP3vhWfdkSqOx0F8Qdi6cJw==
| X-WBNR-Posting-Host: 212.172.117.131
| From: =?Utf-8?B?a2FybCBib25k?= <karl.bond@.nospam.nospam>
| Subject: Performance Problem
| Date: Thu, 22 Jul 2004 07:15:30 -0700
| Lines: 30
| Message-ID: <E9C21026-6124-4D88-90B8-56940BD72EFE@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.157
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:352532
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| we use a SQL Server 2000 (Standard Edition) as Database for our
Web-Application
| accessible for approx. 2200 users in a VPN. Web-Server/Application are
| balanced by 2 Servers. The database resides on a 3rd Server.
|
| In peak times we register 400 concurrent open user sessions which might
| access the database.
|
| Severs are running 24 hours but working hours are from 8 am to 8 pm.
|
| Our problem is that during the day the Database server performance slows
down
| more and more. Response time for a complex SQL statement takes 1/2 a
second in
| the morning but after midday it often takes up to 50 seconds.
|
| One thing we found out is that 4 GB physical memory are available and
| the max server memory setting in SQL Server defaults to this value
although the
| standard edition supports only 2 GB of memory.
|
| As servers are administered by our customer themselfes, we are only
allowed
| to give them some hints. That's the reason why we first have to do a
brainstorming.
|
| - How does the server handle the "wrong" memory settings?
| - time spans from .5 to 50 seconds seem to be very large, is this usual?
| - is a concurrent access of 400 sessions a possible explanation?
| - Are there significant differences between Standard and Enterprise
Edition
| regarding Performance, Memory Management etc.?
| - What other reasons for response time increases are possible?
|
| Any statements would be appreciated.
| Regards
| Karl Bond
|
|||Hi Mingqing,
thanks for your reply. You gave me a lot of useful links and I am still working on it.
Especially, we first started with collecting performance counters.
Maybe you can answer one question from my list:
What ist the best practice for setting Virtual Memory size and the SQL Server
'max/min server memory' ?
Windows 2000 Server is just running SQL Server Standard Edition. We are not using Fulltext search. RAM is 4GB.
Thanks in advance.
Regards,
Karl Bond
|||Hi Karl,
Please understood it's hard to say what is a best practise. The following
documents will show you what might be the most suitable for your SQL
Server.
HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/default...B;EN-US;319942
Optimizing Server Performance Using Memory Configuration Options
http://msdn.microsoft.com/library/de...us/optimsql/od
p_tun_1a_6rn7.asp
I understood that reading documents is really annoying and you want to give
you a excatly answer, such as set max memory 3G and min memory 1G. However,
I cannot do this. I am not familiar with your system environment, I don't
think it will much contribution for your system. If you want to make a
provement on performance, IMO, the most important thing should be upgrade
to SQL Server Enterprise Edition and then configure memory for more than
2GB.
Thanks Bill Cheng, according to his reply below, I noticed that you SQL
Server is standard edition, which will not support 4GB memory. So that I
think you'd better upgrade to SQL Server 2000 Enterprise Edition first. The
following document show the maximun capacity for different version of SQL
Server.
Maximum Capacity Specifications
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_8dbn.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!