RE: Performance Problem
From: Bill Cheng (billchng_at_online.microsoft.com)
Date: 07/27/04
- Next message: Jazzer: "RE: Installing SQL Server Web Data Administrator"
- Previous message: adan sanchez: "Help with error 3624."
- In reply to: karl bond: "Performance Problem"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 27 Jul 2004 01:26:01 GMT
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/default.asp?url=/library/en-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.aspx?scid=KB;EN-US;243589
224453.KB.EN-US INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Probl
http://support.microsoft.com/default.aspx?scid=KB;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!TK2MSFTNGXA03.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
|
- Next message: Jazzer: "RE: Installing SQL Server Web Data Administrator"
- Previous message: adan sanchez: "Help with error 3624."
- In reply to: karl bond: "Performance Problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
