performance monitoring: edition

Showing posts with label edition. Show all posts

Wednesday, March 28, 2012

Performance really poor if calling data from sql clients

Hello,
I have installed SQL2005 developer edition on a laptop (2 GB RAM), VS2005 on
another computer. The database is quite small (nearly no data or some test
data). The performance is still really bad:
xxx each time you open the server explorer in VS2005 and you use "show table
data" (takes at least 20 sec., sometimes time out).
xxx each time I read from tables within my application (I'm not reading the
full table, I'm selecting the data, number of records < 5)
What can be done in order to determine the performance consuming
element/object? The network the computer are attached consists of less than 5
users (1 GBit backbone).
Are there any configuration aspects which can help to improove the
performance?
Network pack size = 4096
Max. number of concurrent connections = 0
Boost SQL server priority is not checked
Max. server memory = 2 GB
Thanks,
Stephan
I can't vouch for any of the VS components when it comes to performance
since I don't use them for database access. But you should be able to use
profiler to see what is happening on the sql server end. Also what does the
query plan look like? Is it optimal? Did you run sp_updatestats?
Andrew J. Kelly SQL MVP
"Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
> Hello,
> I have installed SQL2005 developer edition on a laptop (2 GB RAM), VS2005
> on
> another computer. The database is quite small (nearly no data or some test
> data). The performance is still really bad:
> xxx each time you open the server explorer in VS2005 and you use "show
> table
> data" (takes at least 20 sec., sometimes time out).
> xxx each time I read from tables within my application (I'm not reading
> the
> full table, I'm selecting the data, number of records < 5)
> What can be done in order to determine the performance consuming
> element/object? The network the computer are attached consists of less
> than 5
> users (1 GBit backbone).
> Are there any configuration aspects which can help to improove the
> performance?
> Network pack size = 4096
> Max. number of concurrent connections = 0
> Boost SQL server priority is not checked
> Max. server memory = 2 GB
> Thanks,
> Stephan
|||Hello Andrew,
good hint with the profiler.
You know I'm "fighting" since a while to follow the documented
recommendation of using TCP instead of Named Pipes to get performance.
I see now if I using Named Pipes connection in VS2005, everything is going
faster in VS2005 (and I assume this will also happen for my application if I
do the same).
So, what is the problem with TCP as connection type? Already known issues by
Microsoft SQL developer teams?
Yours
Stephan
"Andrew J. Kelly" wrote:

> I can't vouch for any of the VS components when it comes to performance
> since I don't use them for database access. But you should be able to use
> profiler to see what is happening on the sql server end. Also what does the
> query plan look like? Is it optimal? Did you run sp_updatestats?
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
>
>
|||Ok, the performance is not stable:
it is true that with named pipes. it is going a bit faster, but each time I
select another table to be displayed, it is still waiting for something I
cannot see even in the profiler. There is an overhead I can't actually find
out.
Any idea?
Yours
Stephan
"Stephan (Germany)" wrote:
[vbcol=seagreen]
> Hello Andrew,
> good hint with the profiler.
> You know I'm "fighting" since a while to follow the documented
> recommendation of using TCP instead of Named Pipes to get performance.
> I see now if I using Named Pipes connection in VS2005, everything is going
> faster in VS2005 (and I assume this will also happen for my application if I
> do the same).
> So, what is the problem with TCP as connection type? Already known issues by
> Microsoft SQL developer teams?
> Yours
> Stephan
> "Andrew J. Kelly" wrote:
|||Running of sp_updatestats fails. Error: stats_ver_current is not a built-in
function.
"Andrew J. Kelly" wrote:

> I can't vouch for any of the VS components when it comes to performance
> since I don't use them for database access. But you should be able to use
> profiler to see what is happening on the sql server end. Also what does the
> query plan look like? Is it optimal? Did you run sp_updatestats?
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
>
>
|||What is the compatibility level of the db? If it was a db that was upgraded
it may be in 80 compatibility mode.
Andrew J. Kelly SQL MVP
"Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...[vbcol=seagreen]
> Running of sp_updatestats fails. Error: stats_ver_current is not a
> built-in
> function.
> "Andrew J. Kelly" wrote:
|||If you run the same query from SSIS on the server does it run fast or slow?
Sounds like network issues or problems with name resolution.
Andrew J. Kelly SQL MVP
"Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
message news:E1285720-7132-4B56-B754-B98E9594F466@.microsoft.com...[vbcol=seagreen]
> Ok, the performance is not stable:
> it is true that with named pipes. it is going a bit faster, but each time
> I
> select another table to be displayed, it is still waiting for something I
> cannot see even in the profiler. There is an overhead I can't actually
> find
> out.
> Any idea?
> Yours
> Stephan
> "Stephan (Germany)" wrote:
|||Hello Andrew,
what do you mean with SSIS ?
I have checked DNS, WINS and address resolution. The laptop where the
database is running can be found via ping and nslookup.
If I make another test (creating new logins in SQL management studio), it is
not possible to see any other domain rather than the laptop itself, this is a
bit confusing.
Maybe it has nothing to do with my problem but it is not really good.
"Andrew J. Kelly" wrote:

> If you run the same query from SSIS on the server does it run fast or slow?
> Sounds like network issues or problems with name resolution.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:E1285720-7132-4B56-B754-B98E9594F466@.microsoft.com...
>
>
|||The db was installed completely new. I have uninstalled SQL 2000 first.
But how can I find out finally which compatibility mode the SQL server is
running now? I see no property in management studio nor server configuration
manager.
Yours
Stephan
"Andrew J. Kelly" wrote:

> What is the compatibility level of the db? If it was a db that was upgraded
> it may be in 80 compatibility mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...
>
>
|||Maybe this can help you:
the most time consuming period (waiting time) will arise if I connect the
server. If I pause a while and send another SQL command, the reestablishing
of the connection (same connecting string) needs again a while and then I can
fire different sql commands which will be executed in an acceptable speed.
Yours
Stephan
"Andrew J. Kelly" wrote:

> What is the compatibility level of the db? If it was a db that was upgraded
> it may be in 80 compatibility mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...
>
>
sql

Friday, March 23, 2012

performance problems with SQL 2K SP3

I am having big performance problems afters applying SP3 for SQL Server 2000 Enterprise Edition.
We have a large query generated by Crystal Reports involving about 20 tables and views with joins and outer joins.
The largest table have 100000 rows and is 20 Mb large.
If I run this query on SQL server without service pack, it takes less than 1 second, with SP3 it takes more than 2 minutes!
I found a workaround, copying all data selectes by the larger view into a table and using the table instead of the view in the query.
I looked at execution plans and it seems an optimizer bug of SP3.
Someone could help me, please?Have you checked SP3a readme for any fix.|||Have you verified you applied SP3a NOT SP3?

SP3 had an Slow ODBC bug in the client part of it.

Tim S

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!

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!

Friday, March 9, 2012

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.

Performance of SQL2k5 vs SQL2k

I have SQL 2k5 RTM Developer Edition. Today I started to compare performance
of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
worse performance in comparision with SQL2k. The tests is simply simulate
load of our production database. It is call of stored procedures which do
inserts/update/deletes. Why in most of tests SQL2k5 have worse performance? I
restored database from backup, updated statistics, rebuilded indices.
Additionally - I see what average CPU usage of SQL2k is near 90%, and SQL2k5
is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
where located OS, log and data. Default trace disabled.Hi,
How are you comparing speed? Have you checked the query plans and see how
they compared?
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>I have SQL 2k5 RTM Developer Edition. Today I started to compare
>performance
> of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
> increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
> worse performance in comparision with SQL2k. The tests is simply simulate
> load of our production database. It is call of stored procedures which do
> inserts/update/deletes. Why in most of tests SQL2k5 have worse
> performance? I
> restored database from backup, updated statistics, rebuilded indices.
> Additionally - I see what average CPU usage of SQL2k is near 90%, and
> SQL2k5
> is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
> And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
> where located OS, log and data. Default trace disabled.|||You are trying to do performance tests on a dual core Pentium with a single
IDE drive with everything on that same drive? I would look at the disks as a
bottleneck first.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>I have SQL 2k5 RTM Developer Edition. Today I started to compare
>performance
> of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
> increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
> worse performance in comparision with SQL2k. The tests is simply simulate
> load of our production database. It is call of stored procedures which do
> inserts/update/deletes. Why in most of tests SQL2k5 have worse
> performance? I
> restored database from backup, updated statistics, rebuilded indices.
> Additionally - I see what average CPU usage of SQL2k is near 90%, and
> SQL2k5
> is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
> And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
> where located OS, log and data. Default trace disabled.|||Agree!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eflnySL7FHA.3588@.TK2MSFTNGP15.phx.gbl...
> You are trying to do performance tests on a dual core Pentium with a
> single IDE drive with everything on that same drive? I would look at the
> disks as a bottleneck first.
>
> --
> Andrew J. Kelly SQL MVP
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>>I have SQL 2k5 RTM Developer Edition. Today I started to compare
>>performance
>> of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
>> increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
>> worse performance in comparision with SQL2k. The tests is simply simulate
>> load of our production database. It is call of stored procedures which do
>> inserts/update/deletes. Why in most of tests SQL2k5 have worse
>> performance? I
>> restored database from backup, updated statistics, rebuilded indices.
>> Additionally - I see what average CPU usage of SQL2k is near 90%, and
>> SQL2k5
>> is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is
>> stopped.
>> And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
>> where located OS, log and data. Default trace disabled.
>|||Well, actually I do not benchmark tests, but preparations for benchmark tests
- writing scripts, test programs etc. In December we will run the benchmark
tests on good hardware, and with EMC CX500 as IO system. Depending on results
of the tests, possibly will be started project to move database from SQL2k to
SQL2k5. But if SQL2k5 will not show better performance in the tests, no
chances what we will migrate from SQL2k to SQL2k5. And first tests from dual
core Pentium with a single IDE drive is not show good results. Ok, IO system
most likely bottleneck here. But why it is less bottleneck for SQL2k?
"Andrew J. Kelly" wrote:
> You are trying to do performance tests on a dual core Pentium with a single
> IDE drive with everything on that same drive? I would look at the disks as a
> bottleneck first.
>
> --
> Andrew J. Kelly SQL MVP
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
> >I have SQL 2k5 RTM Developer Edition. Today I started to compare
> >performance
> > of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
> > increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
> > worse performance in comparision with SQL2k. The tests is simply simulate
> > load of our production database. It is call of stored procedures which do
> > inserts/update/deletes. Why in most of tests SQL2k5 have worse
> > performance? I
> > restored database from backup, updated statistics, rebuilded indices.
> > Additionally - I see what average CPU usage of SQL2k is near 90%, and
> > SQL2k5
> > is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
> > And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
> > where located OS, log and data. Default trace disabled.
>
>|||I run scripts from several threads, from 1 to 20 threads. And I measure time
from start of the tests till end of the tests. Additionally I measure number
of long transactions - production our systems is very performance sensitive.
Long transaction - it is transaction with duration much more than some
threshold. I measured number of transaction with duration above 50 ms in the
tests. Our production system (SQL2k) currently have more than 99% of
transactions with duration below 50 ms. I see longer execution time and more
long transactions in SQL2k5 in comparison with SQL2k. Note what is my tests,
in case of one thread, total execution time of tests in SQL2k5 was ~60% worse
than SQL2k. In case of 20 threads, it was about same time, with very little
advantage of SQL2k5. So in most of tests, SQL2k5 had worse performance.
"GregO" wrote:
> Hi,
> How are you comparing speed? Have you checked the query plans and see how
> they compared?
> kind regards
> Greg O
> --
> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
> AGS SQL 2005 Utilities, over 20+ functions
> http://www.ag-software.com/?tabid=38
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
> >I have SQL 2k5 RTM Developer Edition. Today I started to compare
> >performance
> > of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
> > increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
> > worse performance in comparision with SQL2k. The tests is simply simulate
> > load of our production database. It is call of stored procedures which do
> > inserts/update/deletes. Why in most of tests SQL2k5 have worse
> > performance? I
> > restored database from backup, updated statistics, rebuilded indices.
> > Additionally - I see what average CPU usage of SQL2k is near 90%, and
> > SQL2k5
> > is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
> > And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
> > where located OS, log and data. Default trace disabled.
>
>|||Start with the execution plans as suggested by Greg.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>I have SQL 2k5 RTM Developer Edition. Today I started to compare performance
> of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
> increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
> worse performance in comparision with SQL2k. The tests is simply simulate
> load of our production database. It is call of stored procedures which do
> inserts/update/deletes. Why in most of tests SQL2k5 have worse performance? I
> restored database from backup, updated statistics, rebuilded indices.
> Additionally - I see what average CPU usage of SQL2k is near 90%, and SQL2k5
> is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
> And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
> where located OS, log and data. Default trace disabled.|||Hi
Ok, so you have a deve version of SQL 2005 what very of SQL 2000 do you
have. Are they configured the same? In terms of memory settings and mode ?
The next thing I would look at is not times because time can be miss
leading. While times are important to end users you need to see what SQL is
doing to cause these time. I would be looking at reads and query plans and
seeing what the difference is in them. If SQL 200 is doing less read
(physical or logical ) then SQL 2005 then you would think the query plan and
therefore the index usage is different. I understand that you restored the
DB but there can still be differences in what he optimiser does. If the
reads are the same and the plans are the same then it is the transport
medium that is different. If the transport layer is the same and the plans
are the same as well as the IO then yes MS has stuffed up. But just looking
at the number of tranactions over 50ms is not going to give you the tru
impression of whats going on.
Now you said that you had 20 threads does anyone know if dev of SQL 2005 has
a connection limit?
kind regards
Greg O
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B1D8A275-BCC5-4D44-BCCF-1941765C9458@.microsoft.com...
>I run scripts from several threads, from 1 to 20 threads. And I measure
>time
> from start of the tests till end of the tests. Additionally I measure
> number
> of long transactions - production our systems is very performance
> sensitive.
> Long transaction - it is transaction with duration much more than some
> threshold. I measured number of transaction with duration above 50 ms in
> the
> tests. Our production system (SQL2k) currently have more than 99% of
> transactions with duration below 50 ms. I see longer execution time and
> more
> long transactions in SQL2k5 in comparison with SQL2k. Note what is my
> tests,
> in case of one thread, total execution time of tests in SQL2k5 was ~60%
> worse
> than SQL2k. In case of 20 threads, it was about same time, with very
> little
> advantage of SQL2k5. So in most of tests, SQL2k5 had worse performance.
> "GregO" wrote:
>> Hi,
>> How are you comparing speed? Have you checked the query plans and see
>> how
>> they compared?
>> kind regards
>> Greg O
>> --
>> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
>> AGS SQL 2005 Utilities, over 20+ functions
>> http://www.ag-software.com/?tabid=38
>> "andsm" <andsm@.discussions.microsoft.com> wrote in message
>> news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>> >I have SQL 2k5 RTM Developer Edition. Today I started to compare
>> >performance
>> > of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
>> > increase of performance of SQL2k5. But for most of tests, SQL2k5 have
>> > 60%
>> > worse performance in comparision with SQL2k. The tests is simply
>> > simulate
>> > load of our production database. It is call of stored procedures which
>> > do
>> > inserts/update/deletes. Why in most of tests SQL2k5 have worse
>> > performance? I
>> > restored database from backup, updated statistics, rebuilded indices.
>> > Additionally - I see what average CPU usage of SQL2k is near 90%, and
>> > SQL2k5
>> > is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is
>> > stopped.
>> > And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE
>> > disk,
>> > where located OS, log and data. Default trace disabled.
>>|||"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7C1D93E4-2724-4582-AA9F-22CA290E3D04@.microsoft.com...
> Well, actually I do not benchmark tests, but preparations for benchmark
> tests
> - writing scripts, test programs etc. In December we will run the
> benchmark
> tests on good hardware, and with EMC CX500 as IO system. Depending on
> results
> of the tests, possibly will be started project to move database from SQL2k
> to
> SQL2k5. But if SQL2k5 will not show better performance in the tests, no
> chances what we will migrate from SQL2k to SQL2k5. And first tests from
> dual
> core Pentium with a single IDE drive is not show good results. Ok, IO
> system
> most likely bottleneck here. But why it is less bottleneck for SQL2k?
>
One thing is that with SQL2005 is _much_ easier to spot and resolve
bottlenecks in a workload. With SQL2000 you had to run a trace and do a lot
of analysis to find your "top 10" queries by Total CPU, Average CPU, Total
Reads and Average Reads. With 2005 these are built-in reports in SQL Server
Management Studio. The Database Engine Tuning Adviser is great, and there
are many new features for tuning an application.
For tuning when you can change the queries:
-Common Table Expressions
-OUTPUT clause
-Ranking functions
-CLR Procs and Functions
-APPLY operator
For tuning when you cannot change the queries:
-indexes with included columns (these are awesome!!),
-improvements to Indexed Views,
-query plan guides.
So, if the baseline performance is close I would much rather have SQL2005
because it's so much easier to analyze and improve the performance.
David|||SQL 2005 is different than 2000 in many ways. For instance tempdb is used a
lot more in 2005 than 2000 so it is very important to ensure tempdb is on a
disk subsystem that is properly configured to handle the intended load. This
is true of coarse for all the data and log files as well. It is useless to
do any testing and make any decisions on hardware that is not what you will
be running on in the end.
--
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7C1D93E4-2724-4582-AA9F-22CA290E3D04@.microsoft.com...
> Well, actually I do not benchmark tests, but preparations for benchmark
> tests
> - writing scripts, test programs etc. In December we will run the
> benchmark
> tests on good hardware, and with EMC CX500 as IO system. Depending on
> results
> of the tests, possibly will be started project to move database from SQL2k
> to
> SQL2k5. But if SQL2k5 will not show better performance in the tests, no
> chances what we will migrate from SQL2k to SQL2k5. And first tests from
> dual
> core Pentium with a single IDE drive is not show good results. Ok, IO
> system
> most likely bottleneck here. But why it is less bottleneck for SQL2k?
> "Andrew J. Kelly" wrote:
>> You are trying to do performance tests on a dual core Pentium with a
>> single
>> IDE drive with everything on that same drive? I would look at the disks
>> as a
>> bottleneck first.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "andsm" <andsm@.discussions.microsoft.com> wrote in message
>> news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>> >I have SQL 2k5 RTM Developer Edition. Today I started to compare
>> >performance
>> > of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
>> > increase of performance of SQL2k5. But for most of tests, SQL2k5 have
>> > 60%
>> > worse performance in comparision with SQL2k. The tests is simply
>> > simulate
>> > load of our production database. It is call of stored procedures which
>> > do
>> > inserts/update/deletes. Why in most of tests SQL2k5 have worse
>> > performance? I
>> > restored database from backup, updated statistics, rebuilded indices.
>> > Additionally - I see what average CPU usage of SQL2k is near 90%, and
>> > SQL2k5
>> > is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is
>> > stopped.
>> > And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE
>> > disk,
>> > where located OS, log and data. Default trace disabled.
>>|||i'm getting similar results. sql2005 much slower.
however, for one particular query, i was able to get very similar speeds
after running the query through the index tuning wizard in 2005. of
course, it made me do quite a few things to get similar performance.
the worst things it had me do were to make huge covering indexes using
the "include" option on the create index statement. if i have to go
through this for every query against every database, it will be years
before i'll be able to upgrade to 2005.
andsm wrote:
> I have SQL 2k5 RTM Developer Edition. Today I started to compare performance
> of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
> increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
> worse performance in comparision with SQL2k. The tests is simply simulate
> load of our production database. It is call of stored procedures which do
> inserts/update/deletes. Why in most of tests SQL2k5 have worse performance? I
> restored database from backup, updated statistics, rebuilded indices.
> Additionally - I see what average CPU usage of SQL2k is near 90%, and SQL2k5
> is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
> And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
> where located OS, log and data. Default trace disabled.|||I also encountered an overall loss of performance when running the same
tests on the same machine - inserting data into tables seems to be the
problem: Doing an OLE DB insert of 100.000 records lasts 42,3 seconds on
2000 but 189,5 seconds on 2005! Even bulk inserting 1 million records is
quite slow (9,8 seconds on 2000 but 25,2 seconds on 2005). Queries perform
the same or somewhat better...
2x AMD Athlon MP 2800+
4 GB of RAM
Windows 2000 Server, SP4
SQL Server 2000, Standard, SP3 (8.00.760) <=> SQL Server 2005, Standard, RTM
(9.0.1399)
Kind regards
Th. Hein
"andsm" <andsm@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>I have SQL 2k5 RTM Developer Edition. Today I started to compare
>performance
> of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
> increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
> worse performance in comparision with SQL2k. The tests is simply simulate
> load of our production database. It is call of stored procedures which do
> inserts/update/deletes. Why in most of tests SQL2k5 have worse
> performance? I
> restored database from backup, updated statistics, rebuilded indices.
> Additionally - I see what average CPU usage of SQL2k is near 90%, and
> SQL2k5
> is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
> And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
> where located OS, log and data. Default trace disabled.

Performance of SQL2k5 vs SQL2k

Saturday, February 25, 2012

Performance object "SQL Server" does not exist

Hi,

I am running SQL Server 2000 SP4 on a Windows Server 2003 64bit Edition. I indend to monitor the SQL Server performance in the Windows Performance Monitor however neither the SQL Server performance object nor the associated counters are visible.

Any idea why and how I possibly install it?

Thanks in advance,

Greg

When ever I've had the counters drop off either a SQL Server restart or an OS restart will resolve the issue.|||

Thanks.

I did so however the counters haven't emerged after the restart of the both.

Any help would be appreciated.

Greg

|||

Come on........! The suggestion to restart the servers shows little respect towards this issue.

Would somebody please help?

Thanks.

|||

Is the SQL server a 64 bit edition?

THe issue is you can only see 64bit related counters and only way you load the 32bit version of PERFMON to be able to read the 32 bit SQL Server counters, that is easily done by running this: mmc /32 perfmon.msc.

|||

mmc /32 perfmon.msc was the solution.

Thanks!

Performance Monitoring â?" Interpretation

Folks,
Objective: Is there enough resources on this server to create another
instance of SQL Server?
SQL Server 2000 Enterprise Edition SP3
Windows 2003 Enterprise Edition SP1 4GB Memory
The server currently has 1 instance.
SQL Server is dynamically managing memory
Total Server Memory: 2.606 GB
Available Mbytes: 380
Pages/Sec: .023
SQLSERV Working Set: 2.648 GB
Processor: 10%
Cache Hit Ratio: 99.8%
SQL Server Free Pages: 1,031 (1.611 MB)
No disk contention.
My interpretation is this:
This instance of SQL Server appears to be utilizing all the memory available
to it. If I scale this instance back via max server memory so that I have
available memory for the second instance, Iâ'm thinking that the good
performance Iâ'm realizing now on this server would be jeopardized.
The book I am referencing (SQL Server 2000 Performance Tuning â' Microsoft
Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
consistently below 5MB (in my case it is) that I am either running low on
physical memory or allocating too much to SQL Server. This has me confused.
How can I determine?
Iâ'd also like to know how SQL Server is able to use more than 2GB of
physical memory. Is there anything special (i.e. Boot.ini flag) that is
required?
Thanks in advance.
Scott H.Adding another instance will mean that you have to allocate memory between
the two instances and they may compete but it is hard to say without knowing
what the other instance needs for resources. Why not simply add the new db
to the existing instance? I would look at the Page Life Expectancy counter
to see how well the memory you have now is used. And you are obviously using
more than 2GB for this instance if you have 2.66GB of total memory. So you
must have already set the /3GB switch in the boot.ini file other wise you
would only be using 2GB or less. And why not add more memory? You have EE
for both SQL and Windows yet only have 4GB of memory. Seems counter
productive.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> Folks,
> Objective: Is there enough resources on this server to create another
> instance of SQL Server?
> SQL Server 2000 Enterprise Edition SP3
> Windows 2003 Enterprise Edition SP1 4GB Memory
> The server currently has 1 instance.
> SQL Server is dynamically managing memory
> Total Server Memory: 2.606 GB
> Available Mbytes: 380
> Pages/Sec: .023
> SQLSERV Working Set: 2.648 GB
> Processor: 10%
> Cache Hit Ratio: 99.8%
> SQL Server Free Pages: 1,031 (1.611 MB)
> No disk contention.
> My interpretation is this:
> This instance of SQL Server appears to be utilizing all the memory
> available
> to it. If I scale this instance back via max server memory so that I have
> available memory for the second instance, Iâ?Tm thinking that the good
> performance Iâ?Tm realizing now on this server would be jeopardized.
> The book I am referencing (SQL Server 2000 Performance Tuning â?"
> Microsoft
> Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> consistently below 5MB (in my case it is) that I am either running low on
> physical memory or allocating too much to SQL Server. This has me
> confused.
> How can I determine?
> Iâ?Td also like to know how SQL Server is able to use more than 2GB of
> physical memory. Is there anything special (i.e. Boot.ini flag) that is
> required?
> Thanks in advance.
> Scott H.
>|||Andrew,
The reason for the additional instance is that there will be 2 new databases
that will be internet facing. Currently this instance is not, and no plans on
opening up port 1433 through the firewall.
My recommendation is to add more memory. There is some reluctance due to
cost. The server is an older generation HP (HP DL380 G2). I'm being told that
the memory is not available.
So, the /3GB switch in the boot.ini file is still applicable on Windows 2003
EE and SQL Server 2000 EE? How can I find/view this parameter?
Based on the performance counters, would your recommendation be to add more
memory?
Thanks for your help.
--
Thanks,
Scott H.
"Andrew J. Kelly" wrote:
> Adding another instance will mean that you have to allocate memory between
> the two instances and they may compete but it is hard to say without knowing
> what the other instance needs for resources. Why not simply add the new db
> to the existing instance? I would look at the Page Life Expectancy counter
> to see how well the memory you have now is used. And you are obviously using
> more than 2GB for this instance if you have 2.66GB of total memory. So you
> must have already set the /3GB switch in the boot.ini file other wise you
> would only be using 2GB or less. And why not add more memory? You have EE
> for both SQL and Windows yet only have 4GB of memory. Seems counter
> productive.
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> > Folks,
> >
> > Objective: Is there enough resources on this server to create another
> > instance of SQL Server?
> >
> > SQL Server 2000 Enterprise Edition SP3
> > Windows 2003 Enterprise Edition SP1 4GB Memory
> >
> > The server currently has 1 instance.
> > SQL Server is dynamically managing memory
> > Total Server Memory: 2.606 GB
> > Available Mbytes: 380
> > Pages/Sec: .023
> > SQLSERV Working Set: 2.648 GB
> > Processor: 10%
> > Cache Hit Ratio: 99.8%
> > SQL Server Free Pages: 1,031 (1.611 MB)
> > No disk contention.
> >
> > My interpretation is this:
> >
> > This instance of SQL Server appears to be utilizing all the memory
> > available
> > to it. If I scale this instance back via max server memory so that I have
> > available memory for the second instance, IÃ¢?Tm thinking that the good
> > performance IÃ¢?Tm realizing now on this server would be jeopardized.
> >
> > The book I am referencing (SQL Server 2000 Performance Tuning Ã¢?"
> > Microsoft
> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> > consistently below 5MB (in my case it is) that I am either running low on
> > physical memory or allocating too much to SQL Server. This has me
> > confused.
> > How can I determine?
> >
> > IÃ¢?Td also like to know how SQL Server is able to use more than 2GB of
> > physical memory. Is there anything special (i.e. Boot.ini flag) that is
> > required?
> >
> > Thanks in advance.
> >
> > Scott H.
> >
>
>|||I should have investigated prior to asking. I found the boot.ini under C:\ -
it was hiddent. And yes, the 3GB/ switch is there.
--
Thanks,
Scott H.
"Scott H." wrote:
> Andrew,
> The reason for the additional instance is that there will be 2 new databases
> that will be internet facing. Currently this instance is not, and no plans on
> opening up port 1433 through the firewall.
> My recommendation is to add more memory. There is some reluctance due to
> cost. The server is an older generation HP (HP DL380 G2). I'm being told that
> the memory is not available.
> So, the /3GB switch in the boot.ini file is still applicable on Windows 2003
> EE and SQL Server 2000 EE? How can I find/view this parameter?
> Based on the performance counters, would your recommendation be to add more
> memory?
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
> > Adding another instance will mean that you have to allocate memory between
> > the two instances and they may compete but it is hard to say without knowing
> > what the other instance needs for resources. Why not simply add the new db
> > to the existing instance? I would look at the Page Life Expectancy counter
> > to see how well the memory you have now is used. And you are obviously using
> > more than 2GB for this instance if you have 2.66GB of total memory. So you
> > must have already set the /3GB switch in the boot.ini file other wise you
> > would only be using 2GB or less. And why not add more memory? You have EE
> > for both SQL and Windows yet only have 4GB of memory. Seems counter
> > productive.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> > news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> > > Folks,
> > >
> > > Objective: Is there enough resources on this server to create another
> > > instance of SQL Server?
> > >
> > > SQL Server 2000 Enterprise Edition SP3
> > > Windows 2003 Enterprise Edition SP1 4GB Memory
> > >
> > > The server currently has 1 instance.
> > > SQL Server is dynamically managing memory
> > > Total Server Memory: 2.606 GB
> > > Available Mbytes: 380
> > > Pages/Sec: .023
> > > SQLSERV Working Set: 2.648 GB
> > > Processor: 10%
> > > Cache Hit Ratio: 99.8%
> > > SQL Server Free Pages: 1,031 (1.611 MB)
> > > No disk contention.
> > >
> > > My interpretation is this:
> > >
> > > This instance of SQL Server appears to be utilizing all the memory
> > > available
> > > to it. If I scale this instance back via max server memory so that I have
> > > available memory for the second instance, IÃ¢?Tm thinking that the good
> > > performance IÃ¢?Tm realizing now on this server would be jeopardized.
> > >
> > > The book I am referencing (SQL Server 2000 Performance Tuning Ã¢?"
> > > Microsoft
> > > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> > > consistently below 5MB (in my case it is) that I am either running low on
> > > physical memory or allocating too much to SQL Server. This has me
> > > confused.
> > > How can I determine?
> > >
> > > IÃ¢?Td also like to know how SQL Server is able to use more than 2GB of
> > > physical memory. Is there anything special (i.e. Boot.ini flag) that is
> > > required?
> > >
> > > Thanks in advance.
> > >
> > > Scott H.
> > >
> >
> >
> >|||Well like I said I would look at the Page Life Exptancy counter first and
see how high or low that is. You can't tell from a few counters like this
what is needed. We would really need a lot more info. The existing instance
is using all the available memory but that does not mean it actually needs
it. SQL Server will always use what is there if the db is large enough and
you ask for that much data over time. Having said that memory is one of the
cheapest and easiest ways to grow a SQL box.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:FFF35509-CC93-4E35-8AE2-C61F0A436B82@.microsoft.com...
> Andrew,
> The reason for the additional instance is that there will be 2 new
> databases
> that will be internet facing. Currently this instance is not, and no plans
> on
> opening up port 1433 through the firewall.
> My recommendation is to add more memory. There is some reluctance due to
> cost. The server is an older generation HP (HP DL380 G2). I'm being told
> that
> the memory is not available.
> So, the /3GB switch in the boot.ini file is still applicable on Windows
> 2003
> EE and SQL Server 2000 EE? How can I find/view this parameter?
> Based on the performance counters, would your recommendation be to add
> more
> memory?
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>> Adding another instance will mean that you have to allocate memory
>> between
>> the two instances and they may compete but it is hard to say without
>> knowing
>> what the other instance needs for resources. Why not simply add the new
>> db
>> to the existing instance? I would look at the Page Life Expectancy
>> counter
>> to see how well the memory you have now is used. And you are obviously
>> using
>> more than 2GB for this instance if you have 2.66GB of total memory. So
>> you
>> must have already set the /3GB switch in the boot.ini file other wise you
>> would only be using 2GB or less. And why not add more memory? You have EE
>> for both SQL and Windows yet only have 4GB of memory. Seems counter
>> productive.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
>> > Folks,
>> >
>> > Objective: Is there enough resources on this server to create another
>> > instance of SQL Server?
>> >
>> > SQL Server 2000 Enterprise Edition SP3
>> > Windows 2003 Enterprise Edition SP1 4GB Memory
>> >
>> > The server currently has 1 instance.
>> > SQL Server is dynamically managing memory
>> > Total Server Memory: 2.606 GB
>> > Available Mbytes: 380
>> > Pages/Sec: .023
>> > SQLSERV Working Set: 2.648 GB
>> > Processor: 10%
>> > Cache Hit Ratio: 99.8%
>> > SQL Server Free Pages: 1,031 (1.611 MB)
>> > No disk contention.
>> >
>> > My interpretation is this:
>> >
>> > This instance of SQL Server appears to be utilizing all the memory
>> > available
>> > to it. If I scale this instance back via max server memory so that I
>> > have
>> > available memory for the second instance, IÃ¢?Tm thinking that the good
>> > performance IÃ¢?Tm realizing now on this server would be jeopardized.
>> >
>> > The book I am referencing (SQL Server 2000 Performance Tuning Ã¢?"
>> > Microsoft
>> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
>> > consistently below 5MB (in my case it is) that I am either running low
>> > on
>> > physical memory or allocating too much to SQL Server. This has me
>> > confused.
>> > How can I determine?
>> >
>> > IÃ¢?Td also like to know how SQL Server is able to use more than 2GB of
>> > physical memory. Is there anything special (i.e. Boot.ini flag) that is
>> > required?
>> >
>> > Thanks in advance.
>> >
>> > Scott H.
>> >
>>|||Thanks for your help Andrew.
Again, I'm taking my lead from SQL Server 2000 Performance Tuning book -
Microsoft Press. The counters I've included are what they suggest. I'd be
happy to read other performance tuning documents if you have any
recommendations.
I have added the suggested counter - page life expectancy. The first
interval returned 12 hours (after conversion)
I assumed SQL Server would release memory if it did not require it.
What I'm going to suggest, until the powers that be decide to spend some
money on memory, is that we run the instance with max server memory
configured to say 1.5GB for a few days. I'll compare the counters I'm
collecting now, with a few days of running with the memory scaled back.
--
Thanks,
Scott H.
"Andrew J. Kelly" wrote:
> Well like I said I would look at the Page Life Exptancy counter first and
> see how high or low that is. You can't tell from a few counters like this
> what is needed. We would really need a lot more info. The existing instance
> is using all the available memory but that does not mean it actually needs
> it. SQL Server will always use what is there if the db is large enough and
> you ask for that much data over time. Having said that memory is one of the
> cheapest and easiest ways to grow a SQL box.
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:FFF35509-CC93-4E35-8AE2-C61F0A436B82@.microsoft.com...
> > Andrew,
> >
> > The reason for the additional instance is that there will be 2 new
> > databases
> > that will be internet facing. Currently this instance is not, and no plans
> > on
> > opening up port 1433 through the firewall.
> >
> > My recommendation is to add more memory. There is some reluctance due to
> > cost. The server is an older generation HP (HP DL380 G2). I'm being told
> > that
> > the memory is not available.
> >
> > So, the /3GB switch in the boot.ini file is still applicable on Windows
> > 2003
> > EE and SQL Server 2000 EE? How can I find/view this parameter?
> >
> > Based on the performance counters, would your recommendation be to add
> > more
> > memory?
> >
> > Thanks for your help.
> > --
> > Thanks,
> >
> > Scott H.
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Adding another instance will mean that you have to allocate memory
> >> between
> >> the two instances and they may compete but it is hard to say without
> >> knowing
> >> what the other instance needs for resources. Why not simply add the new
> >> db
> >> to the existing instance? I would look at the Page Life Expectancy
> >> counter
> >> to see how well the memory you have now is used. And you are obviously
> >> using
> >> more than 2GB for this instance if you have 2.66GB of total memory. So
> >> you
> >> must have already set the /3GB switch in the boot.ini file other wise you
> >> would only be using 2GB or less. And why not add more memory? You have EE
> >> for both SQL and Windows yet only have 4GB of memory. Seems counter
> >> productive.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> >> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> >> > Folks,
> >> >
> >> > Objective: Is there enough resources on this server to create another
> >> > instance of SQL Server?
> >> >
> >> > SQL Server 2000 Enterprise Edition SP3
> >> > Windows 2003 Enterprise Edition SP1 4GB Memory
> >> >
> >> > The server currently has 1 instance.
> >> > SQL Server is dynamically managing memory
> >> > Total Server Memory: 2.606 GB
> >> > Available Mbytes: 380
> >> > Pages/Sec: .023
> >> > SQLSERV Working Set: 2.648 GB
> >> > Processor: 10%
> >> > Cache Hit Ratio: 99.8%
> >> > SQL Server Free Pages: 1,031 (1.611 MB)
> >> > No disk contention.
> >> >
> >> > My interpretation is this:
> >> >
> >> > This instance of SQL Server appears to be utilizing all the memory
> >> > available
> >> > to it. If I scale this instance back via max server memory so that I
> >> > have
> >> > available memory for the second instance, IÃ?Â¢?Tm thinking that the good
> >> > performance IÃ?Â¢?Tm realizing now on this server would be jeopardized.
> >> >
> >> > The book I am referencing (SQL Server 2000 Performance Tuning Ã?Â¢?"
> >> > Microsoft
> >> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> >> > consistently below 5MB (in my case it is) that I am either running low
> >> > on
> >> > physical memory or allocating too much to SQL Server. This has me
> >> > confused.
> >> > How can I determine?
> >> >
> >> > IÃ?Â¢?Td also like to know how SQL Server is able to use more than 2GB of
> >> > physical memory. Is there anything special (i.e. Boot.ini flag) that is
> >> > required?
> >> >
> >> > Thanks in advance.
> >> >
> >> > Scott H.
> >> >
> >>
> >>
> >>
>
>|||SQL Server never gives back memory unless the OS specifically asks for it.
So if there is nothing else requiring the memory SQL Server will hold onto
it. IF your PLE counter reads 12 hours chances are you have more memory than
you can get away with. A PLE reading of around 300 seconds or more is
usually enough for decent performance. Over 1000 is considered really good.
I think your idea of dropping the MAX memory and see how it performs is a
good idea. Keep in mind that the Max memory setting is for the buffer pool
only and that on a 32 bit machine SQL Server may use up to ~384MB of
additional memory for the MemToLeave area.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:19D9D73E-0A5C-441D-BFDF-24DE162FD36E@.microsoft.com...
> Thanks for your help Andrew.
> Again, I'm taking my lead from SQL Server 2000 Performance Tuning book -
> Microsoft Press. The counters I've included are what they suggest. I'd be
> happy to read other performance tuning documents if you have any
> recommendations.
> I have added the suggested counter - page life expectancy. The first
> interval returned 12 hours (after conversion)
> I assumed SQL Server would release memory if it did not require it.
> What I'm going to suggest, until the powers that be decide to spend some
> money on memory, is that we run the instance with max server memory
> configured to say 1.5GB for a few days. I'll compare the counters I'm
> collecting now, with a few days of running with the memory scaled back.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>> Well like I said I would look at the Page Life Exptancy counter first and
>> see how high or low that is. You can't tell from a few counters like
>> this
>> what is needed. We would really need a lot more info. The existing
>> instance
>> is using all the available memory but that does not mean it actually
>> needs
>> it. SQL Server will always use what is there if the db is large enough
>> and
>> you ask for that much data over time. Having said that memory is one of
>> the
>> cheapest and easiest ways to grow a SQL box.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:FFF35509-CC93-4E35-8AE2-C61F0A436B82@.microsoft.com...
>> > Andrew,
>> >
>> > The reason for the additional instance is that there will be 2 new
>> > databases
>> > that will be internet facing. Currently this instance is not, and no
>> > plans
>> > on
>> > opening up port 1433 through the firewall.
>> >
>> > My recommendation is to add more memory. There is some reluctance due
>> > to
>> > cost. The server is an older generation HP (HP DL380 G2). I'm being
>> > told
>> > that
>> > the memory is not available.
>> >
>> > So, the /3GB switch in the boot.ini file is still applicable on Windows
>> > 2003
>> > EE and SQL Server 2000 EE? How can I find/view this parameter?
>> >
>> > Based on the performance counters, would your recommendation be to add
>> > more
>> > memory?
>> >
>> > Thanks for your help.
>> > --
>> > Thanks,
>> >
>> > Scott H.
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Adding another instance will mean that you have to allocate memory
>> >> between
>> >> the two instances and they may compete but it is hard to say without
>> >> knowing
>> >> what the other instance needs for resources. Why not simply add the
>> >> new
>> >> db
>> >> to the existing instance? I would look at the Page Life Expectancy
>> >> counter
>> >> to see how well the memory you have now is used. And you are obviously
>> >> using
>> >> more than 2GB for this instance if you have 2.66GB of total memory. So
>> >> you
>> >> must have already set the /3GB switch in the boot.ini file other wise
>> >> you
>> >> would only be using 2GB or less. And why not add more memory? You have
>> >> EE
>> >> for both SQL and Windows yet only have 4GB of memory. Seems counter
>> >> productive.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> >> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
>> >> > Folks,
>> >> >
>> >> > Objective: Is there enough resources on this server to create
>> >> > another
>> >> > instance of SQL Server?
>> >> >
>> >> > SQL Server 2000 Enterprise Edition SP3
>> >> > Windows 2003 Enterprise Edition SP1 4GB Memory
>> >> >
>> >> > The server currently has 1 instance.
>> >> > SQL Server is dynamically managing memory
>> >> > Total Server Memory: 2.606 GB
>> >> > Available Mbytes: 380
>> >> > Pages/Sec: .023
>> >> > SQLSERV Working Set: 2.648 GB
>> >> > Processor: 10%
>> >> > Cache Hit Ratio: 99.8%
>> >> > SQL Server Free Pages: 1,031 (1.611 MB)
>> >> > No disk contention.
>> >> >
>> >> > My interpretation is this:
>> >> >
>> >> > This instance of SQL Server appears to be utilizing all the memory
>> >> > available
>> >> > to it. If I scale this instance back via max server memory so that I
>> >> > have
>> >> > available memory for the second instance, IÃfÂ¢?Tm thinking that the
>> >> > good
>> >> > performance IÃfÂ¢?Tm realizing now on this server would be
>> >> > jeopardized.
>> >> >
>> >> > The book I am referencing (SQL Server 2000 Performance Tuning ÃfÂ¢?"
>> >> > Microsoft
>> >> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages
>> >> > is
>> >> > consistently below 5MB (in my case it is) that I am either running
>> >> > low
>> >> > on
>> >> > physical memory or allocating too much to SQL Server. This has me
>> >> > confused.
>> >> > How can I determine?
>> >> >
>> >> > IÃfÂ¢?Td also like to know how SQL Server is able to use more than
>> >> > 2GB of
>> >> > physical memory. Is there anything special (i.e. Boot.ini flag) that
>> >> > is
>> >> > required?
>> >> >
>> >> > Thanks in advance.
>> >> >
>> >> > Scott H.
>> >> >
>> >>
>> >>
>> >>
>>

Wednesday, March 28, 2012

Performance really poor if calling data from sql clients

Friday, March 23, 2012

performance problems with SQL 2K SP3

Monday, March 12, 2012

Performance Problem

Performance Problem

Friday, March 9, 2012

Performance of stored procedure is improved after database is backed up and restored

Performance of SQL2k5 vs SQL2k

Performance of SQL2k5 vs SQL2k

Performance of SQL2k5 vs SQL2k

Saturday, February 25, 2012

Performance object "SQL Server" does not exist

Performance Monitoring â?" Interpretation

performance monitoring

Blog Archive

About Me