I currently have SQL 2000 Standard on a box with a Single 2.4GHz Xeon HT
processor. Would my performance be better served by adding the 2nd CPU or
upgrading the current CPU to a 3.2GHz?
Any thoughts are welcome,
ScottGenerally I would go with a multi proc environment as you could
simultaneously serve mulitple requests faster although individual request ma
y
be served faster by increasing the clock speed of the single processor.
Licensing would also be a factor in my decision.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Scott Cadreau" wrote:
> I currently have SQL 2000 Standard on a box with a Single 2.4GHz Xeon HT
> processor. Would my performance be better served by adding the 2nd CPU or
> upgrading the current CPU to a 3.2GHz?
> Any thoughts are welcome,
> Scott
>
>
Showing posts with label standard. Show all posts
Showing posts with label standard. Show all posts
Friday, March 23, 2012
Monday, March 12, 2012
Performance Problem
we use a SQL Server 2000 (Standard Edition) as Database for our Web-Applicat
ion
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 dow
n
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 brainst
orming.
- 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 BondHi Karl,
From your descriptions, I understood that your are suffering performance iss
ues with your SQL
Server for about 400 connections in peak times. Have I understood you? If th
ere is anything I
misunderstood, please feel free to let me know.
First of all, please understand that performance issues are very hard to tro
ubleshooting 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 d
irect assistance
from a Microsoft Support Professional through Microsoft Product Support Serv
ices. You can
contact Microsoft Product Support directly to discuss additional support opt
ions you may
have available, by contacting us at 1-(800)936-5800 or by choosing one of th
e options listed at
http://support.microsoft.com/defaul...d=sz;en-us;top.
Secondly, many factors may lead into performance issues, such as network per
formance,
bottle-neck of your product server, etc. I would like to recommand you the f
ollowing KB, which
will show you how to troubleshooting performance issues in SQL Server yourse
lf
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/defaul...kb;en-us;298475
Monitoring Server Performance and Activity
http://msdn.microsoft.com/library/d...n_p
erf_
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 st
rongly recommand you
reading it throughly if I may
INF: SQL Server 7.0 Performance Tuning Guide (White Paper)
http://support.microsoft.com/defaul...kb;en-us;322883
Thirdly, The following KB will help you how to exceed the limitation of 2 Gi
gabytes memory of
SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/defaul...kb;en-us;274750
INF: How To Enable Analysis Server to Use 3 GB of RAM
http://support.microsoft.com/defaul...kb;en-us;295443
Fourthly, I would like to show your the difference between SQL Server 2000 s
tandard Edition
and SQL Server 2000 Enterprise Edition. The following document listed all fe
atures between
them
Features by Edition
http://www.microsoft.com/sql/evalua...es/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 d
ocument. Hope it
will help you making the right chooice.
Choosing an Edition of SQL Server 2000
http://www.microsoft.com/sql/techin...eskChooseEd.asp
Last but not the least, it is highly appreciated if you could create one thr
ead per question,
which, I believe, will make us focus on one issue and make better troublesho
oting
Thank you for your patience and cooperation. If you have any questions or co
ncerns, 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/d...-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/defaul...KB;EN-US;243589
224453.KB.EN-US INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Probl
http://support.microsoft.com/defaul...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: examnotes <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
||||Hi Mingqing,
thanks for your reply. You gave me a lot of useful links and I am still work
ing 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 Serve
r
'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/defaul...KB;EN-US;319942
Optimizing Server Performance Using Memory Configuration Options
http://msdn.microsoft.com/library/d...-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/d...-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!
ion
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 dow
n
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 brainst
orming.
- 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 BondHi Karl,
From your descriptions, I understood that your are suffering performance iss
ues with your SQL
Server for about 400 connections in peak times. Have I understood you? If th
ere is anything I
misunderstood, please feel free to let me know.
First of all, please understand that performance issues are very hard to tro
ubleshooting 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 d
irect assistance
from a Microsoft Support Professional through Microsoft Product Support Serv
ices. You can
contact Microsoft Product Support directly to discuss additional support opt
ions you may
have available, by contacting us at 1-(800)936-5800 or by choosing one of th
e options listed at
http://support.microsoft.com/defaul...d=sz;en-us;top.
Secondly, many factors may lead into performance issues, such as network per
formance,
bottle-neck of your product server, etc. I would like to recommand you the f
ollowing KB, which
will show you how to troubleshooting performance issues in SQL Server yourse
lf
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/defaul...kb;en-us;298475
Monitoring Server Performance and Activity
http://msdn.microsoft.com/library/d...n_p
erf_
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 st
rongly recommand you
reading it throughly if I may
INF: SQL Server 7.0 Performance Tuning Guide (White Paper)
http://support.microsoft.com/defaul...kb;en-us;322883
Thirdly, The following KB will help you how to exceed the limitation of 2 Gi
gabytes memory of
SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/defaul...kb;en-us;274750
INF: How To Enable Analysis Server to Use 3 GB of RAM
http://support.microsoft.com/defaul...kb;en-us;295443
Fourthly, I would like to show your the difference between SQL Server 2000 s
tandard Edition
and SQL Server 2000 Enterprise Edition. The following document listed all fe
atures between
them
Features by Edition
http://www.microsoft.com/sql/evalua...es/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 d
ocument. Hope it
will help you making the right chooice.
Choosing an Edition of SQL Server 2000
http://www.microsoft.com/sql/techin...eskChooseEd.asp
Last but not the least, it is highly appreciated if you could create one thr
ead per question,
which, I believe, will make us focus on one issue and make better troublesho
oting
Thank you for your patience and cooperation. If you have any questions or co
ncerns, 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/d...-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/defaul...KB;EN-US;243589
224453.KB.EN-US INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Probl
http://support.microsoft.com/defaul...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: examnotes <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
||||Hi Mingqing,
thanks for your reply. You gave me a lot of useful links and I am still work
ing 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 Serve
r
'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/defaul...KB;EN-US;319942
Optimizing Server Performance Using Memory Configuration Options
http://msdn.microsoft.com/library/d...-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/d...-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!
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!
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!
Performance on Windows 2003 Standard w/ SQL SERVER 2000 Enterprise
I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matter
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and not
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, I read the support article before, but because my installation is on a
windows 2003 server. The article indicates that it already supports up to 4
GB for Windows 2003 server and only need to modify the boot.ini to use more
than 4GB. Am I wrong in reading it that way. Thanks.
"Tracy McKibben" wrote:
> WD wrote:
> > I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> > with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> > configuration is set to use all available resource on the box. But no matter
> > what we throw at it. It's using only 25% of CPU resource and the memory
> > usuage is never over 1.7G. The installation is definately Enterprise and not
> > standard. What am I missing?
> >
> > Thanks!
> The CPU utilization may mean nothing. Did you set the BOOT.INI switches
> for the additional memory?
> http://support.microsoft.com/kb/274750/
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matter
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and not
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, I read the support article before, but because my installation is on a
windows 2003 server. The article indicates that it already supports up to 4
GB for Windows 2003 server and only need to modify the boot.ini to use more
than 4GB. Am I wrong in reading it that way. Thanks.
"Tracy McKibben" wrote:
> WD wrote:
> > I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> > with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> > configuration is set to use all available resource on the box. But no matter
> > what we throw at it. It's using only 25% of CPU resource and the memory
> > usuage is never over 1.7G. The installation is definately Enterprise and not
> > standard. What am I missing?
> >
> > Thanks!
> The CPU utilization may mean nothing. Did you set the BOOT.INI switches
> for the additional memory?
> http://support.microsoft.com/kb/274750/
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Friday, March 9, 2012
Performance on Windows 2003 Standard w/ SQL SERVER 2000 Enterprise
I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard b
ox
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matt
er
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and n
ot
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard b
ox
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matt
er
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and n
ot
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Performance of stored procedure is improved after database is backed up and restored
I have a stored procedure that performs very poorly on SQL Server 2000 standard edition. It takes approximately 40 seconds to execute. In the course of troubleshooting the issue I backed up the database to disk and restored it to another SQLServer and the performance issue was resolved. On a hunch I went to the original server, backed up the database to disk, and then restored the database from the backup I just created and voila the problem was resolved. Why would this resolve the issue? I have another database with the same stored procedure with the same problem. I have tried the following on the stored procedure and all related tables with no success:
DBCC DBREINDEX
UPDATE STATISTICS
sp_recompile my_storedprocedure.
What is the restore doing to resolve the issue? What command can I run to resolve this. I have a feeling it is the query plan but wouldn't update statistics correct this?
Is it possible that the physical database files are getting fragmented?
The restore would then lay the files down contiguously, which would improve performance.
Other than that, the content of the database including statistics would be exactly the same after the restore as it was at the time of backup. SQL backup is a physical copy of every allocated byte in the database files.
Subscribe to:
Posts (Atom)