Create 3 PageFiles (8 GB of RAM)

I have 8 GB of ram on my server, Microsoft recommends
creating Pagefiles for the server at 1.5 * (RAM on server).
I would like to create 3 Pagefiles on my server each at
4095 MB in size on the drives m, n, and o.
Please let me the procedures to complete this task.
Thank You,
StevenSteven,
Refer to this url.
http://www.petri.co.il/pagefile_optimization.htm
--
- Vishal
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:03b401c3ae2a$5f6638a0$a501280a@.phx.gbl...
> I have 8 GB of ram on my server, Microsoft recommends
> creating Pagefiles for the server at 1.5 * (RAM on server).
> I would like to create 3 Pagefiles on my server each at
> 4095 MB in size on the drives m, n, and o.
> Please let me the procedures to complete this task.
> Thank You,
> Steven
>sql

CPUs and RAM limit on workgroup edition

I can find out the limit of CPUs and ram on workgroup edition are 2pc and
3GB respectively. What does this mean if I have 4 CPUs and 4GB ram in one
machine? The 3GB ram limitation refers to the maximum size of memory
allocated to SQL Server?
Thanks!The CPU and memory restrictions refer to the maximum SQL Server will use.
The additional processors/memory will be available to other applications but
ignored by SQL Server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jackie" <-> wrote in message news:OHt2YphsGHA.1196@.TK2MSFTNGP04.phx.gbl...
>I can find out the limit of CPUs and ram on workgroup edition are 2pc and
> 3GB respectively. What does this mean if I have 4 CPUs and 4GB ram in one
> machine? The 3GB ram limitation refers to the maximum size of memory
> allocated to SQL Server?
> Thanks!
>
>

CPUs and RAM limit on workgroup edition

I can find out the limit of CPUs and ram on workgroup edition are 2pc and
3GB respectively. What does this mean if I have 4 CPUs and 4GB ram in one
machine? The 3GB ram limitation refers to the maximum size of memory
allocated to SQL Server?
Thanks!The CPU and memory restrictions refer to the maximum SQL Server will use.
The additional processors/memory will be available to other applications but
ignored by SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jackie" <-> wrote in message news:OHt2YphsGHA.1196@.TK2MSFTNGP04.phx.gbl...
>I can find out the limit of CPUs and ram on workgroup edition are 2pc and
> 3GB respectively. What does this mean if I have 4 CPUs and 4GB ram in one
> machine? The 3GB ram limitation refers to the maximum size of memory
> allocated to SQL Server?
> Thanks!
>
>

CPU Pegging

I have
Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
I am noticing the processors are pegging quite a bit, cpu for the
sqlservr.exe process is running around 50% constantly with lots of pegs up
to 100%. Is there something I should be looking at before I throw more
hardware at it? Is this normal, knew sql server sucked memory but not the
cpu. The memory for the process is around 1.6 gig. It is a production
machine so it is hit pretty hard during the day.
John"John Cantley" <jcstrider@.hotmail.com> wrote in message
news:uc2glj1vDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I have
> Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
> I am noticing the processors are pegging quite a bit, cpu for the
> sqlservr.exe process is running around 50% constantly with lots of pegs up
> to 100%. Is there something I should be looking at before I throw more
> hardware at it? Is this normal, knew sql server sucked memory but not the
> cpu. The memory for the process is around 1.6 gig. It is a production
> machine so it is hit pretty hard during the day.
> John
>|||Well, you might want to look at profiler and see if there are specific
queries that can be better tuned or rewritten
"John Cantley" <jcstrider@.hotmail.com> wrote in message
news:u4sV1n1vDHA.1424@.tk2msftngp13.phx.gbl...
> "John Cantley" <jcstrider@.hotmail.com> wrote in message
> news:uc2glj1vDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > I have
> >
> > Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
> >
> > I am noticing the processors are pegging quite a bit, cpu for the
> > sqlservr.exe process is running around 50% constantly with lots of pegs
up
> > to 100%. Is there something I should be looking at before I throw more
> > hardware at it? Is this normal, knew sql server sucked memory but not
the
> > cpu. The memory for the process is around 1.6 gig. It is a production
> > machine so it is hit pretty hard during the day.
> >
> > John
> >
> >
>|||Oh, and as an aside, if your pegging CPU, one thing to look for is cursors.
"John Cantley" <jcstrider@.hotmail.com> wrote in message
news:u4sV1n1vDHA.1424@.tk2msftngp13.phx.gbl...
> "John Cantley" <jcstrider@.hotmail.com> wrote in message
> news:uc2glj1vDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > I have
> >
> > Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
> >
> > I am noticing the processors are pegging quite a bit, cpu for the
> > sqlservr.exe process is running around 50% constantly with lots of pegs
up
> > to 100%. Is there something I should be looking at before I throw more
> > hardware at it? Is this normal, knew sql server sucked memory but not
the
> > cpu. The memory for the process is around 1.6 gig. It is a production
> > machine so it is hit pretty hard during the day.
> >
> > John
> >
> >
>|||| I have
|
| Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
|
| I am noticing the processors are pegging quite a bit, cpu for the
| sqlservr.exe process is running around 50% constantly with lots of pegs up
| to 100%. Is there something I should be looking at before I throw more
| hardware at it? Is this normal, knew sql server sucked memory but not the
| cpu. The memory for the process is around 1.6 gig. It is a production
| machine so it is hit pretty hard during the day.
--
Hi John,
The most important question you need to ask yourself as a DBA: are your
users complaining about slowness or lack of responsiveness? If not, then
things are fine. The old adage goes, "if it ain't broke, don't fix it."
High CPU utilisation, as long as it's not always running at 100%, is
actually a GOOD THING. That means that the CPU, the most expensive
component of a computer system, is doing it's job. If you see CPU spiking
to 100% on occasion, that means there are no bottlenecks in your computer
system.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Eric,
Ok just wondering then.
I have a smaller server that runs our website content and customer order
data, I coded the application and made heavy use of stored procedures,
triggers and such. The cpu is typically under 10% and mem usage is about 1/2
gig and connections are usually in the 50 range. The site is heavily used
for order tracking and other stuff.
The one that is causing me problems is a bigger faster machine with two
processors, I have no control of the frontend application that is used in
our production process, it uses 0 stored procedures, 0 triggers, nothing
nada zilch. I am thinking that since this is true then I am not getting a
good rate of the sql statements compiled, since so many varying queries are
hitting it. I have also noticed 3 connections opening just upon startup of
the application. Couldn't these factors account for the higher cpu usage. I
preformed a backup on it and noticed that the cpu usage dropped to a lower
rate. I am thinking that maybe the transactions are left dangling by the 3rd
party product and the backup process terminates them.
john
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:pfOA846vDHA.3532@.cpmsftngxa07.phx.gbl...
> | I have
> |
> | Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
> |
> | I am noticing the processors are pegging quite a bit, cpu for the
> | sqlservr.exe process is running around 50% constantly with lots of pegs
up
> | to 100%. Is there something I should be looking at before I throw more
> | hardware at it? Is this normal, knew sql server sucked memory but not
the
> | cpu. The memory for the process is around 1.6 gig. It is a production
> | machine so it is hit pretty hard during the day.
> --
> Hi John,
> The most important question you need to ask yourself as a DBA: are your
> users complaining about slowness or lack of responsiveness? If not, then
> things are fine. The old adage goes, "if it ain't broke, don't fix it."
> High CPU utilisation, as long as it's not always running at 100%, is
> actually a GOOD THING. That means that the CPU, the most expensive
> component of a computer system, is doing it's job. If you see CPU spiking
> to 100% on occasion, that means there are no bottlenecks in your computer
> system.
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>

CPU is 99% used by sqlserver

Hi all ,
I have an issue hope you can help:
We have 1 server SQL server 2000 ( Small business )
running with a Pentiem 3 + 1 Go RAM. And we have only 3
Workstation like clients.
Since severals weeks, the CPU od the server is used for
99% and all client request using ACCESS is very slow.
Do you know what happened?
Thanks a lot for your help.
CanCan,
You need to monitor the CPU usage using perfmon counters concurrently
running sql profiler to see whats happening in the background.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"can" <canpama@.netscape.net> wrote in message
news:47c301c37616$eccb9970$a601280a@.phx.gbl...
> Hi all ,
> I have an issue hope you can help:
> We have 1 server SQL server 2000 ( Small business )
> running with a Pentiem 3 + 1 Go RAM. And we have only 3
> Workstation like clients.
> Since severals weeks, the CPU od the server is used for
> 99% and all client request using ACCESS is very slow.
> Do you know what happened?
> Thanks a lot for your help.
> Can
>|||Hi Dinesh ,
Thanks a lot to reply me . I visited your page , it is
nice and fun.
I have another question , i hope that you can help me.
My company has SQL Server 7 and clients using Access to
extract Data from Sql Server.
So my question is:
I would like to migrate all existing applications to Linus
Red Hat , i wonder if we can exporte all data to Postgress
or to My SQL or to DB2 ( Linux ) and i wonder if there are
the same product like ACCESS running with Linux:
Many thank.
Can
>--Original Message--
>Can,
>You need to monitor the CPU usage using perfmon counters
concurrently
>running sql profiler to see whats happening in the
background.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"can" <canpama@.netscape.net> wrote in message
>news:47c301c37616$eccb9970$a601280a@.phx.gbl...
>> Hi all ,
>> I have an issue hope you can help:
>> We have 1 server SQL server 2000 ( Small business )
>> running with a Pentiem 3 + 1 Go RAM. And we have only 3
>> Workstation like clients.
>> Since severals weeks, the CPU od the server is used for
>> 99% and all client request using ACCESS is very slow.
>> Do you know what happened?
>> Thanks a lot for your help.
>> Can
>>
>
>.
>|||Don't know if you've resolved this already...but I've had this happen on
several servers. It was a virus. I believe it was slammer. The
characteristics of it are similar. Rebooting would recover the CPU for a
while..but it would go back tying up the network, and locking up the server.
If you don't have an SA password...I would bet that it is slammer.
Gary
"can" <canpama@.netscape.net> wrote in message
news:47c301c37616$eccb9970$a601280a@.phx.gbl...
> Hi all ,
> I have an issue hope you can help:
> We have 1 server SQL server 2000 ( Small business )
> running with a Pentiem 3 + 1 Go RAM. And we have only 3
> Workstation like clients.
> Since severals weeks, the CPU od the server is used for
> 99% and all client request using ACCESS is very slow.
> Do you know what happened?
> Thanks a lot for your help.
> Can
>|||Can,
Thanks:)
>>to DB2
Try
INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2
http://www.support.microsoft.com/?id=218590
INF: Configuring Data Sources for the Microsoft OLE DB Provider for AS/400
and VSAM
http://www.support.microsoft.com/?id=219077
>>same product like ACCESS running with Linux:
Iam not aware about any.
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"can" <canpama@.netscape.net> wrote in message
news:3c1d01c3762a$9d2b7200$a301280a@.phx.gbl...
> Hi Dinesh ,
> Thanks a lot to reply me . I visited your page , it is
> nice and fun.
> I have another question , i hope that you can help me.
> My company has SQL Server 7 and clients using Access to
> extract Data from Sql Server.
> So my question is:
> I would like to migrate all existing applications to Linus
> Red Hat , i wonder if we can exporte all data to Postgress
> or to My SQL or to DB2 ( Linux ) and i wonder if there are
> the same product like ACCESS running with Linux:
> Many thank.
> Can
>
>
>
>
> >--Original Message--
> >Can,
> >
> >You need to monitor the CPU usage using perfmon counters
> concurrently
> >running sql profiler to see whats happening in the
> background.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"can" <canpama@.netscape.net> wrote in message
> >news:47c301c37616$eccb9970$a601280a@.phx.gbl...
> >> Hi all ,
> >>
> >> I have an issue hope you can help:
> >>
> >> We have 1 server SQL server 2000 ( Small business )
> >> running with a Pentiem 3 + 1 Go RAM. And we have only 3
> >> Workstation like clients.
> >>
> >> Since severals weeks, the CPU od the server is used for
> >> 99% and all client request using ACCESS is very slow.
> >>
> >> Do you know what happened?
> >> Thanks a lot for your help.
> >> Can
> >>
> >>
> >
> >
> >.
> >

CPU and memory usage

Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.

CPU and memory usage

Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:

> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas

"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.

CPU and memory usage

Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:

> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any w
ay
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.

Couple of questions

1) To use or not to use the 3GB switch. We have 8GB of RAM and just run SQL
Server with awe and PAE Enabled. I have heard that 3GB is good and I have
also heard 3 GB is bad with the above setup..
2) We have a 32 bit system and we run into worker thread issues such all 256
worker threads are used and all incoming connections are rejected. Should I
be increasing those worker threads to say 512 ?
3) What is stored in the address space above the 4GB limit ..i.e when i use
awe to address say 6GB , is it only data/index pages that reside in that
region of memory above the 4GB limit ?
4) Is user connection memory, procedure cache stored in mem to leave area ?
We are using SQL 2000 EE
ThanksAnswers Inline
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%2300axugfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> 1) To use or not to use the 3GB switch. We have 8GB of RAM and just run
> SQL Server with awe and PAE Enabled. I have heard that 3GB is good and I
> have also heard 3 GB is bad with the above setup..
>
3GB is usually OK up to 12 GB total system RAM. Above that it is not
recommended

> 2) We have a 32 bit system and we run into worker thread issues such all
> 256 worker threads are used and all incoming connections are rejected.
> Should I be increasing those worker threads to say 512 ?
How many processers do you have? Are they running above 90% at the time of
the incoming rejections?

> 3) What is stored in the address space above the 4GB limit ..i.e when i
> use awe to address say 6GB , is it only data/index pages that reside in
> that region of memory above the 4GB limit ?
>
AWE memory is not addressable and is used for data cache only.

> 4) Is user connection memory, procedure cache stored in mem to leave area
> ?
No, with one exception. If you are using large packets, then user
connection memory may come out of mem-to-leave if the system has to use OS
network buffers rather than SQL network buffers.

> We are using SQL 2000 EE
> Thanks
>
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Thanks Geoff..
1) So what are we missing out on for not using 3GB if we have less than 12GB
of RAM ?
2) We have seen worker thread problems on our high OLTP systems. Some
possible causes could be h/w related such as disk IO issue causing slowness
in response times all revolving around blocking..So say if the blocking
sproc runs for 10 secs all of a sudden, but there are around 1000 sprocs
that want to execute and read of that blocked table, then in no time, we run
out of threads and connections are not honored.
3) With AWE , does data flow from the non addressable memory to addressable
memory ?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ehrOAIhfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Answers Inline
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%2300axugfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> 3GB is usually OK up to 12 GB total system RAM. Above that it is not
> recommended
>
> How many processers do you have? Are they running above 90% at the time
> of the incoming rejections?
>
> AWE memory is not addressable and is used for data cache only.
>
> No, with one exception. If you are using large packets, then user
> connection memory may come out of mem-to-leave if the system has to use OS
> network buffers rather than SQL network buffers.
>
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>|||More Inline
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23dYzJghfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> Thanks Geoff..
> 1) So what are we missing out on for not using 3GB if we have less than
> 12GB of RAM ?
>
/3GB allows for more SQL addressable memory. Depending on your system, this
may improve performance. As with any config change, test both before and
after to see if it improves things. In most cases with system memory above
4GB and less than or equal to 12GB, you will see an improvement.

> 2) We have seen worker thread problems on our high OLTP systems. Some
> possible causes could be h/w related such as disk IO issue causing
> slowness in response times all revolving around blocking..So say if the
> blocking sproc runs for 10 secs all of a sudden, but there are around 1000
> sprocs that want to execute and read of that blocked table, then in no
> time, we run out of threads and connections are not honored.
>
Monitor your current disk queue length counters for your data and log disks.
If you are having IO stalls, you will need to improve the IO subsystem
before any worker thread count improvements will help. This is why I
hesitate to recommend a specific change over the newsgroups. You need to
find the root cause of your performance issues. Sometimes one issue, such
as an I/O weakness, can show up as memory or CPU pressure to casual
observation.

> 3) With AWE , does data flow from the non addressable memory to
> addressable memory ?
>
That is why you have to limit the use of the /3GB switch. AWE is much like
the old EXTENDED and EXPANDED RAM from the '268 days where a window of low
(or in this case addressable) memory is used to map into the non-addressable
memory. This is done via the OS memory area, so if you squeeze it too much
on very large memory systems, you introduce some severe performance issues.
The OS needs the extra memory to handle to address windows.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:ehrOAIhfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>

Couple of questions

1) To use or not to use the 3GB switch. We have 8GB of RAM and just run SQL
Server with awe and PAE Enabled. I have heard that 3GB is good and I have
also heard 3 GB is bad with the above setup..
2) We have a 32 bit system and we run into worker thread issues such all 256
worker threads are used and all incoming connections are rejected. Should I
be increasing those worker threads to say 512 ?
3) What is stored in the address space above the 4GB limit ..i.e when i use
awe to address say 6GB , is it only data/index pages that reside in that
region of memory above the 4GB limit ?
4) Is user connection memory, procedure cache stored in mem to leave area ?
We are using SQL 2000 EE
ThanksAnswers Inline
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%2300axugfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> 1) To use or not to use the 3GB switch. We have 8GB of RAM and just run
> SQL Server with awe and PAE Enabled. I have heard that 3GB is good and I
> have also heard 3 GB is bad with the above setup..
>
3GB is usually OK up to 12 GB total system RAM. Above that it is not
recommended
> 2) We have a 32 bit system and we run into worker thread issues such all
> 256 worker threads are used and all incoming connections are rejected.
> Should I be increasing those worker threads to say 512 ?
How many processers do you have? Are they running above 90% at the time of
the incoming rejections?
> 3) What is stored in the address space above the 4GB limit ..i.e when i
> use awe to address say 6GB , is it only data/index pages that reside in
> that region of memory above the 4GB limit ?
>
AWE memory is not addressable and is used for data cache only.
> 4) Is user connection memory, procedure cache stored in mem to leave area
> ?
No, with one exception. If you are using large packets, then user
connection memory may come out of mem-to-leave if the system has to use OS
network buffers rather than SQL network buffers.
> We are using SQL 2000 EE
> Thanks
>
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Thanks Geoff..
1) So what are we missing out on for not using 3GB if we have less than 12GB
of RAM ?
2) We have seen worker thread problems on our high OLTP systems. Some
possible causes could be h/w related such as disk IO issue causing slowness
in response times all revolving around blocking..So say if the blocking
sproc runs for 10 secs all of a sudden, but there are around 1000 sprocs
that want to execute and read of that blocked table, then in no time, we run
out of threads and connections are not honored.
3) With AWE , does data flow from the non addressable memory to addressable
memory ?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ehrOAIhfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Answers Inline
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%2300axugfGHA.2068@.TK2MSFTNGP02.phx.gbl...
>> 1) To use or not to use the 3GB switch. We have 8GB of RAM and just run
>> SQL Server with awe and PAE Enabled. I have heard that 3GB is good and I
>> have also heard 3 GB is bad with the above setup..
> 3GB is usually OK up to 12 GB total system RAM. Above that it is not
> recommended
>> 2) We have a 32 bit system and we run into worker thread issues such all
>> 256 worker threads are used and all incoming connections are rejected.
>> Should I be increasing those worker threads to say 512 ?
> How many processers do you have? Are they running above 90% at the time
> of the incoming rejections?
>> 3) What is stored in the address space above the 4GB limit ..i.e when i
>> use awe to address say 6GB , is it only data/index pages that reside in
>> that region of memory above the 4GB limit ?
> AWE memory is not addressable and is used for data cache only.
>> 4) Is user connection memory, procedure cache stored in mem to leave area
>> ?
> No, with one exception. If you are using large packets, then user
> connection memory may come out of mem-to-leave if the system has to use OS
> network buffers rather than SQL network buffers.
>
>> We are using SQL 2000 EE
>> Thanks
>>
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>|||More Inline
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23dYzJghfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> Thanks Geoff..
> 1) So what are we missing out on for not using 3GB if we have less than
> 12GB of RAM ?
>
/3GB allows for more SQL addressable memory. Depending on your system, this
may improve performance. As with any config change, test both before and
after to see if it improves things. In most cases with system memory above
4GB and less than or equal to 12GB, you will see an improvement.
> 2) We have seen worker thread problems on our high OLTP systems. Some
> possible causes could be h/w related such as disk IO issue causing
> slowness in response times all revolving around blocking..So say if the
> blocking sproc runs for 10 secs all of a sudden, but there are around 1000
> sprocs that want to execute and read of that blocked table, then in no
> time, we run out of threads and connections are not honored.
>
Monitor your current disk queue length counters for your data and log disks.
If you are having IO stalls, you will need to improve the IO subsystem
before any worker thread count improvements will help. This is why I
hesitate to recommend a specific change over the newsgroups. You need to
find the root cause of your performance issues. Sometimes one issue, such
as an I/O weakness, can show up as memory or CPU pressure to casual
observation.
> 3) With AWE , does data flow from the non addressable memory to
> addressable memory ?
>
That is why you have to limit the use of the /3GB switch. AWE is much like
the old EXTENDED and EXPANDED RAM from the '268 days where a window of low
(or in this case addressable) memory is used to map into the non-addressable
memory. This is done via the OS memory area, so if you squeeze it too much
on very large memory systems, you introduce some severe performance issues.
The OS needs the extra memory to handle to address windows.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:ehrOAIhfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Answers Inline
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:%2300axugfGHA.2068@.TK2MSFTNGP02.phx.gbl...
>> 1) To use or not to use the 3GB switch. We have 8GB of RAM and just run
>> SQL Server with awe and PAE Enabled. I have heard that 3GB is good and I
>> have also heard 3 GB is bad with the above setup..
>>
>> 3GB is usually OK up to 12 GB total system RAM. Above that it is not
>> recommended
>> 2) We have a 32 bit system and we run into worker thread issues such all
>> 256 worker threads are used and all incoming connections are rejected.
>> Should I be increasing those worker threads to say 512 ?
>> How many processers do you have? Are they running above 90% at the time
>> of the incoming rejections?
>>
>> 3) What is stored in the address space above the 4GB limit ..i.e when i
>> use awe to address say 6GB , is it only data/index pages that reside in
>> that region of memory above the 4GB limit ?
>> AWE memory is not addressable and is used for data cache only.
>> 4) Is user connection memory, procedure cache stored in mem to leave
>> area ?
>> No, with one exception. If you are using large packets, then user
>> connection memory may come out of mem-to-leave if the system has to use
>> OS network buffers rather than SQL network buffers.
>>
>> We are using SQL 2000 EE
>> Thanks
>>
>>
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>