Performance Hits - Second Post

Sorry for re-posting - wasn't sure how to -repost the same question. We are
becoming very frustrated with this performance issue with SQL server - any
response would be GREATLY appreciated.
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have others
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!
Hi
How many databases are on the server?
Have you applied SQL Server SP3a to the clients also (EM and QA do get fixes
too)?
On a client machine, run in QA, "SELECT * FROM Master.dbo.sysdatabases" and
see how long it takes to return.
Regards
Mike
"JaneDoe" wrote:

> Sorry for re-posting - wasn't sure how to -repost the same question. We are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was either
> instant or 1-2 seconds. Once the intial connection has been made, there are
> no further performance issues. Do you know if there is a fix, or have others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>
|||Do you have Auto-Close turned on for those clients?
Andrew J. Kelly SQL MVP
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
> Sorry for re-posting - wasn't sure how to -repost the same question. We
> are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
> Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was
> either
> instant or 1-2 seconds. Once the intial connection has been made, there
> are
> no further performance issues. Do you know if there is a fix, or have
> others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>
|||Make sure you have SP3a applied to the client machines. See if these help:
http://support.microsoft.com/default...b;en-us;889696
http://support.microsoft.com/default...b;en-us;282416
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0eCIEhCFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you have Auto-Close turned on for those clients?
> --
> Andrew J. Kelly SQL MVP
>
> "JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
> news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
>

Performance Hits - Second Post

Sorry for re-posting - wasn't sure how to -repost the same question. We are
becoming very frustrated with this performance issue with SQL server - any
response would be GREATLY appreciated.
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have other
s
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!Hi
How many databases are on the server?
Have you applied SQL Server SP3a to the clients also (EM and QA do get fixes
too)?
On a client machine, run in QA, "SELECT * FROM Master.dbo.sysdatabases" and
see how long it takes to return.
Regards
Mike
"JaneDoe" wrote:

> Sorry for re-posting - wasn't sure how to -repost the same question. We a
re
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Serve
r
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was eith
er
> instant or 1-2 seconds. Once the intial connection has been made, there a
re
> no further performance issues. Do you know if there is a fix, or have oth
ers
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Do you have Auto-Close turned on for those clients?
Andrew J. Kelly SQL MVP
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
> Sorry for re-posting - wasn't sure how to -repost the same question. We
> are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
> Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was
> either
> instant or 1-2 seconds. Once the intial connection has been made, there
> are
> no further performance issues. Do you know if there is a fix, or have
> others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Make sure you have SP3a applied to the client machines. See if these help:
http://support.microsoft.com/defaul...kb;en-us;889696
http://support.microsoft.com/defaul...kb;en-us;282416
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0eCIEhCFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you have Auto-Close turned on for those clients?
> --
> Andrew J. Kelly SQL MVP
>
> "JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
> news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
>

Performance Hits - Second Post

Sorry for re-posting - wasn't sure how to -repost the same question. We are
becoming very frustrated with this performance issue with SQL server - any
response would be GREATLY appreciated.
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have others
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!Hi
How many databases are on the server?
Have you applied SQL Server SP3a to the clients also (EM and QA do get fixes
too)?
On a client machine, run in QA, "SELECT * FROM Master.dbo.sysdatabases" and
see how long it takes to return.
Regards
Mike
"JaneDoe" wrote:
> Sorry for re-posting - wasn't sure how to -repost the same question. We are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was either
> instant or 1-2 seconds. Once the intial connection has been made, there are
> no further performance issues. Do you know if there is a fix, or have others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Do you have Auto-Close turned on for those clients?
--
Andrew J. Kelly SQL MVP
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
> Sorry for re-posting - wasn't sure how to -repost the same question. We
> are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
> Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was
> either
> instant or 1-2 seconds. Once the intial connection has been made, there
> are
> no further performance issues. Do you know if there is a fix, or have
> others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Make sure you have SP3a applied to the client machines. See if these help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;889696
http://support.microsoft.com/default.aspx?scid=kb;en-us;282416
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0eCIEhCFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you have Auto-Close turned on for those clients?
> --
> Andrew J. Kelly SQL MVP
>
> "JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
> news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
>> Sorry for re-posting - wasn't sure how to -repost the same question. We
>> are
>> becoming very frustrated with this performance issue with SQL server -
>> any
>> response would be GREATLY appreciated.
>> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
>> Server
>> 2000. Many people are complaining that Enterprise Manager now takes 4-20
>> seconds to expand the databases on those servers, where before it was
>> either
>> instant or 1-2 seconds. Once the intial connection has been made, there
>> are
>> no further performance issues. Do you know if there is a fix, or have
>> others
>> experienced this slow performance after the upgrade?
>> We have compared user desktops' memory, processors, hard drives, etc, and
>> there is no trend there.
>> We would appreciate any advice before moving forward w/ our other
>> servers'
>> upgrades!
>

Performance counters disappeared

I have an application written in C++ that querys all
available performance counters via RegQueryValueEx() API
at 10 second interval on some Windows 2000 computers that
have SQL Server 7.0 installed. After the application have
run for some period of time (several days or longer), the
SQL Server related performance counters disappeared. They
are disabeld by the OS. The knowledge base article
Q248993 mentions that the performance library of Windows
2000 disables any performance DLL that returns data in the
incorrect format, causes an unhandled program fault, or
takes too long to return the performance data (this is
usually the cause of a library being disabled). It is
obvious the incorrect format does not apply to my case
since it would have caused the problem right after it
started not several days later. Anyone knows what causes
the problem?Did you see Q812915?
"Tianxiang Zhang" <tianxiang_zhang@.bmc.com> wrote in message
news:013701c344b5$7f9caed0$a501280a@.phx.gbl...
> I have an application written in C++ that querys all
> available performance counters via RegQueryValueEx() API
> at 10 second interval on some Windows 2000 computers that
> have SQL Server 7.0 installed. After the application have
> run for some period of time (several days or longer), the
> SQL Server related performance counters disappeared. They
> are disabeld by the OS. The knowledge base article
> Q248993 mentions that the performance library of Windows
> 2000 disables any performance DLL that returns data in the
> incorrect format, causes an unhandled program fault, or
> takes too long to return the performance data (this is
> usually the cause of a library being disabled). It is
> obvious the incorrect format does not apply to my case
> since it would have caused the problem right after it
> started not several days later. Anyone knows what causes
> the problem?|||I did. They don't seem to be the same issue. Thanks for
the help.
>--Original Message--
>Did you see Q812915?
>
>"Tianxiang Zhang" <tianxiang_zhang@.bmc.com> wrote in
message
>news:013701c344b5$7f9caed0$a501280a@.phx.gbl...
>> I have an application written in C++ that querys all
>> available performance counters via RegQueryValueEx() API
>> at 10 second interval on some Windows 2000 computers
that
>> have SQL Server 7.0 installed. After the application
have
>> run for some period of time (several days or longer),
the
>> SQL Server related performance counters disappeared.
They
>> are disabeld by the OS. The knowledge base article
>> Q248993 mentions that the performance library of Windows
>> 2000 disables any performance DLL that returns data in
the
>> incorrect format, causes an unhandled program fault, or
>> takes too long to return the performance data (this is
>> usually the cause of a library being disabled). It is
>> obvious the incorrect format does not apply to my case
>> since it would have caused the problem right after it
>> started not several days later. Anyone knows what
causes
>> the problem?
>
>.
>sql

Performance and second table

Hi,

I have a small theoretical issue.
I have one table, which is prettyu large. There is lot of evaluations
running on this table, that's why, each process need to wait for
another to be finished. Sometimes, for some critical functions, it
takes to long time.

I don't think that I can speed up processes, by changing the indexes on
the tables (to increase scan time for example), because this is
something what I was experimenting with already, and it was not enought
good.

My question is, will it improve performance, if I will create second
table, exactly like this one, and I will split some evaluations, that
the one, which defenately need to run on the source table will run on
the first one, and the second evaluations, will run on the other one.

To keep data consistance between this two tables, I was thinking baout
trigger on insert on the mother table, which will transport the data to
another one.

Second part is: to improve selects on the table, should I set indexes
with option of Fill factor as possible close to 100% or as possible
close to 0%. Or maybe should I set the pad index option?

What about clustered indexes. Is it better to use them if I would like
to increase performace for selects?

Thanks in advance

MateuszFor selects you should have your indexes as close to 100% as possible,
if this table is frequently modify make it around 80%-90%

Also did you check the fragmentation level of your table?
A lot of times this improves speed dramatically
run DBCC SHOWCONTIG ('YourTableName') and look at Scan Density, Avg.
Bytes Free per Page and Fragmentation Levels
If your density is low and/or fragmentation high run DBCC INDEXDEFRAG
(dbname,tablename,1)
Lookup DBCC SHOWCONTIG and DBCC INDEXDEFRAG in Books online

Have you tried horizontal partitioning? This might benefit you

http://sqlservercode.blogspot.com/|||On 27 Sep 2005 09:35:55 -0700, Matik wrote:

>Hi,
>I have a small theoretical issue.
>I have one table, which is prettyu large. There is lot of evaluations
>running on this table, that's why, each process need to wait for
>another to be finished. Sometimes, for some critical functions, it
>takes to long time.

Hi Mateusz,

If the processes are only reading the data without modifying it, then
there is no need to wait. They can run concurrently.

>My question is, will it improve performance, if I will create second
>table, exactly like this one, and I will split some evaluations, that
>the one, which defenately need to run on the source table will run on
>the first one, and the second evaluations, will run on the other one.

I doubt it. SQL Server doesn't know that the data in both tables is
equal. So if one query reads row #12345 from table #1, and the other
query reads row #12345 from table #2, SQL Server will fetch the
corresponding data from both tables from disk to cache. In short, you
are effectively halving the amount of cache SQL Server can use for these
queries. I expect performance to decrease.

>To keep data consistance between this two tables, I was thinking baout
>trigger on insert on the mother table, which will transport the data to
>another one.

And this will hurt performance even more. The speed of inserts will slow
down because the trigger has to be executed. As a result, locks on the
main table will live longer, keeping other queries blocked for longer
amounts of time. And the second table will be blocked as well.

Since the data is apparently updated while you are querying it, you
might find benefit in a variation on your idea: make a copy of the
table, but don't use triggers to copy over all modifications. Instead,
set up a job that will periodically synchronise the data. Now make sure
that all queries that don't need up-to-the-second precision are used on
the copy table (that is only update periodically).

>Second part is: to improve selects on the table, should I set indexes
>with option of Fill factor as possible close to 100% or as possible
>close to 0%. Or maybe should I set the pad index option?
>What about clustered indexes. Is it better to use them if I would like
>to increase performace for selects?

There is no magic bullet here. Each problem needs it's own solution,
that's why there are so many options.

Read more about performance at www.sqlserver-performance.com, or post
here with full details of your tables, indexes, queries and execution
plans for more advise.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 27 Sep 2005 23:30:53 +0200, Hugo Kornelis wrote:

>Read more about performance at www.sqlserver-performance.com

I goofed when typing that URL from memory. The correct URL is
http://www.sql-server-performance.c...performance.asp.

Unfortunately, the site is revamped since my last visit. The content is
still there, but buried in lots of irritating advertising.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo and SQL :)

I see, that there is no better way, like just experiment with this
indexes, and maybe modify some statements. During past two days, I've
did that, and now is much better.

Anyway, the structure of the tables is bad, so there is also no
possibility to use better indexing.

I've one more question, if we are already by the topic of indexes. My
question is about the phisical memory the indexes are using.
When I've made all tables empty, truncated db, shrinkt aso. but the
file size is still a little to big. I know, that the indexes are taking
also memory to be stored (specialy clustered), but, after I've removed
all data, the indexes should be also cleared, right? Or maybe I need to
rebuild them?

Gratings

Mateusz

Performance / Paging

Hi,
While monitoring one of my clients servers, I notice memory paging of
between 50 and 60 pages per second. However, the server has 4gb of memory
and it is currently only using 1.6gb. Why would the server have any
paging at all?
Thanks
BillHi Bill,
Form your descriptions, I understood that your SQL Server was limited in
1.6 GB memory while you have 4 GB memory on the box. Have I understood you?
Correct me if I was wrong.
Based on my socpe, first of all, when using 4 GB memory, I would like to
confirm that you had followed the knowledge base article below to
confirgure your SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750
If it was not done, SQL Server will not be able to access more than 2 GB
memory.
Secondly, when paging still goes so high, you'd better try to trace what is
going on with SQL Server by Profiler. Please understood that Performance
issue tend to very complex and we are not able to go further in this topic,
anyway, the following knowledge base articles will help much
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hey Michael,
Thanks for the response. SQL Server 2000 is configured to dynamically
configure it's memory and the box has 4GB. Using both PerfMon and Quest's
Spotlight on SQL Server, I noticed that there was a lot of paging. But,
the server was only using 1.6gb.
After reading those articles, I am concerned and confused.
Am running SQL Server 2000 Standard on Windows 2000 Server
1st, I can't find anything anywhere on configuring Windows 2000 to use AWE.
And, the article says the /3GB and /PAE is not available on Win2000 Server.
This also comes from the article.
[[Normally, both the SQL Server 2000 Enterprise Edition and SQL Serv
er 2000
Developer Edition can use up to 2 GB of physical memory. With the use of the
AWE enable option, SQL Server can use up to 4 GB of physical memory. ]]
Does this mean in my config, SS can only use 2GB'
(Can you run SS Standard on 2000 Advance Server?)
Thanks for helping out.
Bill
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:be88i9C2EHA.2732@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Form your descriptions, I understood that your SQL Server was limited in
> 1.6 GB memory while you have 4 GB memory on the box. Have I understood
> you?
> Correct me if I was wrong.
> Based on my socpe, first of all, when using 4 GB memory, I would like to
> confirm that you had followed the knowledge base article below to
> confirgure your SQL Server
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/kb/274750
> If it was not done, SQL Server will not be able to access more than 2 GB
> memory.
> Secondly, when paging still goes so high, you'd better try to trace what
> is
> going on with SQL Server by Profiler. Please understood that Performance
> issue tend to very complex and we are not able to go further in this
> topic,
> anyway, the following knowledge base articles will help much
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/kb/224587
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||AWE uses memory differently. So, on a server with 4 GB of physical memory,
there are a couple of ways to configure its usage.
If you are running SQL Server 2000 Enterprise Edition, you can enable AWE
support and address up to 2 GB in the active memory and up to 2 GB in the
AWE memory pools, regardless of the Windows edition.
With Windows Advanced Server, you could specify the /3GB boot.ini file
parameter and address up to 3 GB in the active memory and up to 1 GB in the
AWE memory pools, again with SS Enterprise Edition.
With Windows Advanced Server, you could also specify the /PAE boot.ini file
parameter and address more than 4 GB if the server had more installed on it;
however, depending on the status of the /3GB parameter, the additional
memory would be relugated to the AWE pool.
If you are running SQL Server Standard Edition, you will be limited to the
USER MODE memory area only because it does not support AWE; however, if you
are running Advanced Server, you can still set the /3GB switch, which
changes the size of the USER MODE area. This would be available to SQL
Server Standard Edition. The question is: will SS SE address it? I do not
believe there is any sort of governor that would preclude it just the
inability to support AWE.
Sincerely,
Anthony Thomas
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KmtM69O2EHA.2732@.cpmsftngxa10.phx.gbl...
Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Performance / Paging

Hi,
While monitoring one of my clients servers, I notice memory paging of
between 50 and 60 pages per second. However, the server has 4gb of memory
and it is currently only using 1.6gb. Why would the server have any
paging at all?
Thanks
BillHi Bill,
Form your descriptions, I understood that your SQL Server was limited in
1.6 GB memory while you have 4 GB memory on the box. Have I understood you?
Correct me if I was wrong.
Based on my socpe, first of all, when using 4 GB memory, I would like to
confirm that you had followed the knowledge base article below to
confirgure your SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750
If it was not done, SQL Server will not be able to access more than 2 GB
memory.
Secondly, when paging still goes so high, you'd better try to trace what is
going on with SQL Server by Profiler. Please understood that Performance
issue tend to very complex and we are not able to go further in this topic,
anyway, the following knowledge base articles will help much
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hey Michael,
Thanks for the response. SQL Server 2000 is configured to dynamically
configure it's memory and the box has 4GB. Using both PerfMon and Quest's
Spotlight on SQL Server, I noticed that there was a lot of paging. But,
the server was only using 1.6gb.
After reading those articles, I am concerned and confused.
Am running SQL Server 2000 Standard on Windows 2000 Server
1st, I can't find anything anywhere on configuring Windows 2000 to use AWE.
And, the article says the /3GB and /PAE is not available on Win2000 Server.
This also comes from the article.
[[Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of the
AWE enable option, SQL Server can use up to 4 GB of physical memory. ]]
Does this mean in my config, SS can only use 2GB'
(Can you run SS Standard on 2000 Advance Server?)
Thanks for helping out.
Bill
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:be88i9C2EHA.2732@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Form your descriptions, I understood that your SQL Server was limited in
> 1.6 GB memory while you have 4 GB memory on the box. Have I understood
> you?
> Correct me if I was wrong.
> Based on my socpe, first of all, when using 4 GB memory, I would like to
> confirm that you had followed the knowledge base article below to
> confirgure your SQL Server
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/kb/274750
> If it was not done, SQL Server will not be able to access more than 2 GB
> memory.
> Secondly, when paging still goes so high, you'd better try to trace what
> is
> going on with SQL Server by Profiler. Please understood that Performance
> issue tend to very complex and we are not able to go further in this
> topic,
> anyway, the following knowledge base articles will help much
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/kb/224587
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||AWE uses memory differently. So, on a server with 4 GB of physical memory,
there are a couple of ways to configure its usage.
If you are running SQL Server 2000 Enterprise Edition, you can enable AWE
support and address up to 2 GB in the active memory and up to 2 GB in the
AWE memory pools, regardless of the Windows edition.
With Windows Advanced Server, you could specify the /3GB boot.ini file
parameter and address up to 3 GB in the active memory and up to 1 GB in the
AWE memory pools, again with SS Enterprise Edition.
With Windows Advanced Server, you could also specify the /PAE boot.ini file
parameter and address more than 4 GB if the server had more installed on it;
however, depending on the status of the /3GB parameter, the additional
memory would be relugated to the AWE pool.
If you are running SQL Server Standard Edition, you will be limited to the
USER MODE memory area only because it does not support AWE; however, if you
are running Advanced Server, you can still set the /3GB switch, which
changes the size of the USER MODE area. This would be available to SQL
Server Standard Edition. The question is: will SS SE address it? I do not
believe there is any sort of governor that would preclude it just the
inability to support AWE.
Sincerely,
Anthony Thomas
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KmtM69O2EHA.2732@.cpmsftngxa10.phx.gbl...
Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Performance / Paging

Hi,
While monitoring one of my clients servers, I notice memory paging of
between 50 and 60 pages per second. However, the server has 4gb of memory
and it is currently only using 1.6gb. Why would the server have any
paging at all?
Thanks
Bill
Hi Bill,
Form your descriptions, I understood that your SQL Server was limited in
1.6 GB memory while you have 4 GB memory on the box. Have I understood you?
Correct me if I was wrong.
Based on my socpe, first of all, when using 4 GB memory, I would like to
confirm that you had followed the knowledge base article below to
confirgure your SQL Server
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750
If it was not done, SQL Server will not be able to access more than 2 GB
memory.
Secondly, when paging still goes so high, you'd better try to trace what is
going on with SQL Server by Profiler. Please understood that Performance
issue tend to very complex and we are not able to go further in this topic,
anyway, the following knowledge base articles will help much
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hey Michael,
Thanks for the response. SQL Server 2000 is configured to dynamically
configure it's memory and the box has 4GB. Using both PerfMon and Quest's
Spotlight on SQL Server, I noticed that there was a lot of paging. But,
the server was only using 1.6gb.
After reading those articles, I am concerned and confused.
Am running SQL Server 2000 Standard on Windows 2000 Server
1st, I can't find anything anywhere on configuring Windows 2000 to use AWE.
And, the article says the /3GB and /PAE is not available on Win2000 Server.
This also comes from the article.
[[Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of the
AWE enable option, SQL Server can use up to 4 GB of physical memory. ]]
Does this mean in my config, SS can only use 2GB?
(Can you run SS Standard on 2000 Advance Server?)
Thanks for helping out.
Bill
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:be88i9C2EHA.2732@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Form your descriptions, I understood that your SQL Server was limited in
> 1.6 GB memory while you have 4 GB memory on the box. Have I understood
> you?
> Correct me if I was wrong.
> Based on my socpe, first of all, when using 4 GB memory, I would like to
> confirm that you had followed the knowledge base article below to
> confirgure your SQL Server
> How to configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/kb/274750
> If it was not done, SQL Server will not be able to access more than 2 GB
> memory.
> Secondly, when paging still goes so high, you'd better try to trace what
> is
> going on with SQL Server by Profiler. Please understood that Performance
> issue tend to very complex and we are not able to go further in this
> topic,
> anyway, the following knowledge base articles will help much
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/kb/224587
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||AWE uses memory differently. So, on a server with 4 GB of physical memory,
there are a couple of ways to configure its usage.
If you are running SQL Server 2000 Enterprise Edition, you can enable AWE
support and address up to 2 GB in the active memory and up to 2 GB in the
AWE memory pools, regardless of the Windows edition.
With Windows Advanced Server, you could specify the /3GB boot.ini file
parameter and address up to 3 GB in the active memory and up to 1 GB in the
AWE memory pools, again with SS Enterprise Edition.
With Windows Advanced Server, you could also specify the /PAE boot.ini file
parameter and address more than 4 GB if the server had more installed on it;
however, depending on the status of the /3GB parameter, the additional
memory would be relugated to the AWE pool.
If you are running SQL Server Standard Edition, you will be limited to the
USER MODE memory area only because it does not support AWE; however, if you
are running Advanced Server, you can still set the /3GB switch, which
changes the size of the USER MODE area. This would be available to SQL
Server Standard Edition. The question is: will SS SE address it? I do not
believe there is any sort of governor that would preclude it just the
inability to support AWE.
Sincerely,
Anthony Thomas

"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KmtM69O2EHA.2732@.cpmsftngxa10.phx.gbl...
Hi Bill,
Thanks for your prompt updates!
SQL Server 2000 Enterprise Edition is able to support 4 GB physical memory
in Windows 2000 Server. See Table "Maximum Amount of Physical Memory
Supported by the Editions of SQL Server 2000" under topic "Maximum Capacity
Specifications" in BOL or MSDN Online:
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_8dbn.asp
Notice the words "With the use of the AWE enable option, SQL Server can use
up to 4 GB of physical memory. " in KB: Q274750, which means using 4GB
memory for SQL Server 2000 Enterprise in Windows 2000 Server, you are
required to open AWE enable option. Example:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
Hope it clarifies and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Performance - transactions/second

Does anyone know how many transactions can SQL Server process per second?
Any suggestions on SQL Server performance testing tools?
Thanks!
Teresa wrote:
> Does anyone know how many transactions can SQL Server process per
> second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!
A lot on a 8-way server and much less on a single CPU with 512MB RAM. Of
course, this assumes all code is running efficiently and the drive
subsystems are designed properly, etc.
If you have specific requirements, I would encourage you to speak with
your hardware vendor and have them recommend a solution that fits your
needs. Compaq used to have a SQL Server Sizer that could give you
hardware specs based on performance criteria, but I don't know if HP
still has the tool on the web site.
You can also visit TPC.org and see the performance results there, but I
would encourage you to speak with someone who can custom design a
solution for you.
David Gugick
Imceda Software
www.imceda.com
|||Hi
It depends on application, implimentation and hardware, but http://www.tpc.org
786,646 transactions per minute.
Regards
Mike
"Teresa" wrote:

> Does anyone know how many transactions can SQL Server process per second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!

Performance - transactions/second

Does anyone know how many transactions can SQL Server process per second?
Any suggestions on SQL Server performance testing tools?
Thanks!Teresa wrote:
> Does anyone know how many transactions can SQL Server process per
> second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!
A lot on a 8-way server and much less on a single CPU with 512MB RAM. Of
course, this assumes all code is running efficiently and the drive
subsystems are designed properly, etc.
If you have specific requirements, I would encourage you to speak with
your hardware vendor and have them recommend a solution that fits your
needs. Compaq used to have a SQL Server Sizer that could give you
hardware specs based on performance criteria, but I don't know if HP
still has the tool on the web site.
You can also visit TPC.org and see the performance results there, but I
would encourage you to speak with someone who can custom design a
solution for you.
David Gugick
Imceda Software
www.imceda.com|||Hi
It depends on application, implimentation and hardware, but http://www.tpc.org
786,646 transactions per minute.
Regards
Mike
"Teresa" wrote:
> Does anyone know how many transactions can SQL Server process per second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!

Performance - transactions/second

Does anyone know how many transactions can SQL Server process per second?
Any suggestions on SQL Server performance testing tools?
Thanks!Teresa wrote:
> Does anyone know how many transactions can SQL Server process per
> second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!
A lot on a 8-way server and much less on a single CPU with 512MB RAM. Of
course, this assumes all code is running efficiently and the drive
subsystems are designed properly, etc.
If you have specific requirements, I would encourage you to speak with
your hardware vendor and have them recommend a solution that fits your
needs. Compaq used to have a SQL Server Sizer that could give you
hardware specs based on performance criteria, but I don't know if HP
still has the tool on the web site.
You can also visit TPC.org and see the performance results there, but I
would encourage you to speak with someone who can custom design a
solution for you.
David Gugick
Imceda Software
www.imceda.com|||Hi
It depends on application, implimentation and hardware, but [url]http://www.tpc.org[/ur
l]
786,646 transactions per minute.
Regards
Mike
"Teresa" wrote:

> Does anyone know how many transactions can SQL Server process per second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!