I know if you see high disk queue length , it says you may have an IO
bottleneck..
So the question is when i see values like 10 or 20 or 40 for my disk queue
length, what do the values actually indicate ? What is the unit ? How slow
does a query with say a value of 40 correlate to vs a query with queue
length of 10 ?
Thanks
Hi Hassan
A long queue length may be an indication of an issue but not always, see
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
as SQL Server may push the queue length above an average of 2 per disc in
certain circumstances. If these are peaks then it is less likely to be an
issue than a sustained value. Perfmon has several disc queue counters and you
can differentiate average, current, read, write and total lengths. Each
counter is the number of requests in the queue.
You may want to load this information into a SQL Profile and look at what
the system is doing while the queue are at these values.
John
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>
|||> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
To expand on John's response, I often monitor both disk queue length and
checkpoint pages/sec and ignore the queue length during checkpoint spikes.
Also, even with a disk bottleneck, you will not necessarily see queuing.
You need to know how much activity your disk subsystem can handle (e.g.
transfers/sec.) so that you can know when it is saturated. One approach is
to first establish a baseline under ideal conditions using a tool like
SQLIOSim (http://support.microsoft.com/kb/231619). You'll know you have a
bottleneck when your activity approaches the baseline performance metrics.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
|||Hassan, I see you have several posts dealing with performance related
issues. Take a class and/or hire a professional to mentor you/review your
systems. Both will be money well spent - especially the latter if you get
someone good.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
|||On Sun, 11 Nov 2007 07:57:46 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To expand on John's response, I often monitor both disk queue length and
>checkpoint pages/sec and ignore the queue length during checkpoint spikes.
>Also, even with a disk bottleneck, you will not necessarily see queuing.
Good advice.
The thing about the disk queue is that once that's an issue, it trumps
pretty much any other performance issue. So it's hard to answer the
question about how slow. Compared to no disk queue at all, it's
probably a big, huge difference, 10x, 100x, who knows, but with big
data you are simply going to have to do that IO sometime, but that
should still be possible with a small queue.
J.
|||Disk queue length alone is not a reliable indicator of a bottleneck in the
disk subsystem. To detect disk I/O bottleneck, it's better to look at I/O
latency (e.g. Avg disk sec/read and Avg disk sec/write) plus I/O block sizes
(e.g. Bytes/read and bytes/write), look at these metrics in combination with
disk queue length.
Also, see
http://sqlblog.com/blogs/linchi_shea/archive/2007/11/12/disk-queue-length-some-data-points-may-help.aspx for more info.
Linchi
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>
Showing posts with label slow. Show all posts
Showing posts with label slow. Show all posts
Monday, March 19, 2012
High Disk queue length.. how slow
I know if you see high disk queue length , it says you may have an IO
bottleneck..
So the question is when i see values like 10 or 20 or 40 for my disk queue
length, what do the values actually indicate ? What is the unit ? How slow
does a query with say a value of 40 correlate to vs a query with queue
length of 10 ?
ThanksHi Hassan
A long queue length may be an indication of an issue but not always, see
http://blogs.msdn.com/psssql/archiv...ed
.aspx
as SQL Server may push the queue length above an average of 2 per disc in
certain circumstances. If these are peaks then it is less likely to be an
issue than a sustained value. Perfmon has several disc queue counters and yo
u
can differentiate average, current, read, write and total lengths. Each
counter is the number of requests in the queue.
You may want to load this information into a SQL Profile and look at what
the system is doing while the queue are at these values.
John
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>|||> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
To expand on John's response, I often monitor both disk queue length and
checkpoint pages/sec and ignore the queue length during checkpoint spikes.
Also, even with a disk bottleneck, you will not necessarily see queuing.
You need to know how much activity your disk subsystem can handle (e.g.
transfers/sec.) so that you can know when it is saturated. One approach is
to first establish a baseline under ideal conditions using a tool like
SQLIOSim (http://support.microsoft.com/kb/231619). You'll know you have a
bottleneck when your activity approaches the baseline performance metrics.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||Hassan, I see you have several posts dealing with performance related
issues. Take a class and/or hire a professional to mentor you/review your
systems. Both will be money well spent - especially the latter if you get
someone good.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||On Sun, 11 Nov 2007 07:57:46 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To expand on John's response, I often monitor both disk queue length and
>checkpoint pages/sec and ignore the queue length during checkpoint spikes.
>Also, even with a disk bottleneck, you will not necessarily see queuing.
Good advice.
The thing about the disk queue is that once that's an issue, it trumps
pretty much any other performance issue. So it's hard to answer the
question about how slow. Compared to no disk queue at all, it's
probably a big, huge difference, 10x, 100x, who knows, but with big
data you are simply going to have to do that IO sometime, but that
should still be possible with a small queue.
J.|||Disk queue length alone is not a reliable indicator of a bottleneck in the
disk subsystem. To detect disk I/O bottleneck, it's better to look at I/O
latency (e.g. Avg disk sec/read and Avg disk sec/write) plus I/O block sizes
(e.g. Bytes/read and bytes/write), look at these metrics in combination with
disk queue length.
Also, see
http://sqlblog.com/blogs/linchi_she...s-may-help.aspx for more info.
Linchi
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>
bottleneck..
So the question is when i see values like 10 or 20 or 40 for my disk queue
length, what do the values actually indicate ? What is the unit ? How slow
does a query with say a value of 40 correlate to vs a query with queue
length of 10 ?
ThanksHi Hassan
A long queue length may be an indication of an issue but not always, see
http://blogs.msdn.com/psssql/archiv...ed
.aspx
as SQL Server may push the queue length above an average of 2 per disc in
certain circumstances. If these are peaks then it is less likely to be an
issue than a sustained value. Perfmon has several disc queue counters and yo
u
can differentiate average, current, read, write and total lengths. Each
counter is the number of requests in the queue.
You may want to load this information into a SQL Profile and look at what
the system is doing while the queue are at these values.
John
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>|||> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
To expand on John's response, I often monitor both disk queue length and
checkpoint pages/sec and ignore the queue length during checkpoint spikes.
Also, even with a disk bottleneck, you will not necessarily see queuing.
You need to know how much activity your disk subsystem can handle (e.g.
transfers/sec.) so that you can know when it is saturated. One approach is
to first establish a baseline under ideal conditions using a tool like
SQLIOSim (http://support.microsoft.com/kb/231619). You'll know you have a
bottleneck when your activity approaches the baseline performance metrics.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||Hassan, I see you have several posts dealing with performance related
issues. Take a class and/or hire a professional to mentor you/review your
systems. Both will be money well spent - especially the latter if you get
someone good.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||On Sun, 11 Nov 2007 07:57:46 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To expand on John's response, I often monitor both disk queue length and
>checkpoint pages/sec and ignore the queue length during checkpoint spikes.
>Also, even with a disk bottleneck, you will not necessarily see queuing.
Good advice.
The thing about the disk queue is that once that's an issue, it trumps
pretty much any other performance issue. So it's hard to answer the
question about how slow. Compared to no disk queue at all, it's
probably a big, huge difference, 10x, 100x, who knows, but with big
data you are simply going to have to do that IO sometime, but that
should still be possible with a small queue.
J.|||Disk queue length alone is not a reliable indicator of a bottleneck in the
disk subsystem. To detect disk I/O bottleneck, it's better to look at I/O
latency (e.g. Avg disk sec/read and Avg disk sec/write) plus I/O block sizes
(e.g. Bytes/read and bytes/write), look at these metrics in combination with
disk queue length.
Also, see
http://sqlblog.com/blogs/linchi_she...s-may-help.aspx for more info.
Linchi
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>
High Disk queue length.. how slow
I know if you see high disk queue length , it says you may have an IO
bottleneck..
So the question is when i see values like 10 or 20 or 40 for my disk queue
length, what do the values actually indicate ? What is the unit ? How slow
does a query with say a value of 40 correlate to vs a query with queue
length of 10 ?
ThanksHi Hassan
A long queue length may be an indication of an issue but not always, see
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
as SQL Server may push the queue length above an average of 2 per disc in
certain circumstances. If these are peaks then it is less likely to be an
issue than a sustained value. Perfmon has several disc queue counters and you
can differentiate average, current, read, write and total lengths. Each
counter is the number of requests in the queue.
You may want to load this information into a SQL Profile and look at what
the system is doing while the queue are at these values.
John
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>|||> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
To expand on John's response, I often monitor both disk queue length and
checkpoint pages/sec and ignore the queue length during checkpoint spikes.
Also, even with a disk bottleneck, you will not necessarily see queuing.
You need to know how much activity your disk subsystem can handle (e.g.
transfers/sec.) so that you can know when it is saturated. One approach is
to first establish a baseline under ideal conditions using a tool like
SQLIOSim (http://support.microsoft.com/kb/231619). You'll know you have a
bottleneck when your activity approaches the baseline performance metrics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||Hassan, I see you have several posts dealing with performance related
issues. Take a class and/or hire a professional to mentor you/review your
systems. Both will be money well spent - especially the latter if you get
someone good.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||On Sun, 11 Nov 2007 07:57:46 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>> So the question is when i see values like 10 or 20 or 40 for my disk queue
>> length, what do the values actually indicate ? What is the unit ? How slow
>> does a query with say a value of 40 correlate to vs a query with queue
>> length of 10 ?
>To expand on John's response, I often monitor both disk queue length and
>checkpoint pages/sec and ignore the queue length during checkpoint spikes.
>Also, even with a disk bottleneck, you will not necessarily see queuing.
Good advice.
The thing about the disk queue is that once that's an issue, it trumps
pretty much any other performance issue. So it's hard to answer the
question about how slow. Compared to no disk queue at all, it's
probably a big, huge difference, 10x, 100x, who knows, but with big
data you are simply going to have to do that IO sometime, but that
should still be possible with a small queue.
J.|||Disk queue length alone is not a reliable indicator of a bottleneck in the
disk subsystem. To detect disk I/O bottleneck, it's better to look at I/O
latency (e.g. Avg disk sec/read and Avg disk sec/write) plus I/O block sizes
(e.g. Bytes/read and bytes/write), look at these metrics in combination with
disk queue length.
Also, see
http://sqlblog.com/blogs/linchi_shea/archive/2007/11/12/disk-queue-length-some-data-points-may-help.aspx for more info.
Linchi
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>
bottleneck..
So the question is when i see values like 10 or 20 or 40 for my disk queue
length, what do the values actually indicate ? What is the unit ? How slow
does a query with say a value of 40 correlate to vs a query with queue
length of 10 ?
ThanksHi Hassan
A long queue length may be an indication of an issue but not always, see
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
as SQL Server may push the queue length above an average of 2 per disc in
certain circumstances. If these are peaks then it is less likely to be an
issue than a sustained value. Perfmon has several disc queue counters and you
can differentiate average, current, read, write and total lengths. Each
counter is the number of requests in the queue.
You may want to load this information into a SQL Profile and look at what
the system is doing while the queue are at these values.
John
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>|||> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
To expand on John's response, I often monitor both disk queue length and
checkpoint pages/sec and ignore the queue length during checkpoint spikes.
Also, even with a disk bottleneck, you will not necessarily see queuing.
You need to know how much activity your disk subsystem can handle (e.g.
transfers/sec.) so that you can know when it is saturated. One approach is
to first establish a baseline under ideal conditions using a tool like
SQLIOSim (http://support.microsoft.com/kb/231619). You'll know you have a
bottleneck when your activity approaches the baseline performance metrics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||Hassan, I see you have several posts dealing with performance related
issues. Take a class and/or hire a professional to mentor you/review your
systems. Both will be money well spent - especially the latter if you get
someone good.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||On Sun, 11 Nov 2007 07:57:46 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>> So the question is when i see values like 10 or 20 or 40 for my disk queue
>> length, what do the values actually indicate ? What is the unit ? How slow
>> does a query with say a value of 40 correlate to vs a query with queue
>> length of 10 ?
>To expand on John's response, I often monitor both disk queue length and
>checkpoint pages/sec and ignore the queue length during checkpoint spikes.
>Also, even with a disk bottleneck, you will not necessarily see queuing.
Good advice.
The thing about the disk queue is that once that's an issue, it trumps
pretty much any other performance issue. So it's hard to answer the
question about how slow. Compared to no disk queue at all, it's
probably a big, huge difference, 10x, 100x, who knows, but with big
data you are simply going to have to do that IO sometime, but that
should still be possible with a small queue.
J.|||Disk queue length alone is not a reliable indicator of a bottleneck in the
disk subsystem. To detect disk I/O bottleneck, it's better to look at I/O
latency (e.g. Avg disk sec/read and Avg disk sec/write) plus I/O block sizes
(e.g. Bytes/read and bytes/write), look at these metrics in combination with
disk queue length.
Also, see
http://sqlblog.com/blogs/linchi_shea/archive/2007/11/12/disk-queue-length-some-data-points-may-help.aspx for more info.
Linchi
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>
Monday, March 12, 2012
high CPU time and sql 2000 getting slow performance
Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVThe first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle a
n
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and highe
r,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after som
e
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statement
s
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly
.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having sai
d
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, i
t
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could g
et
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS tak
es
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle
an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
>|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:
>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher
,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVThe first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle a
n
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and highe
r,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after som
e
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statement
s
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly
.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having sai
d
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, i
t
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could g
et
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS tak
es
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle
an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
>|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:
>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher
,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV
high CPU time and sql 2000 getting slow performance
Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVIt sounds to me that the server is overloaded.
Normally if it is a live environment, you have a dedicated server just
for MSSQL.
Normally it is not advisable to change the default memory setting. For
optimum performance, SQL Server should be allowed to take as much as
RAM as it wants for its own use without having to compete for RAM with
other applications.
You can turn on some performance monitors to see how busy your system
is and justify to the owner of the machine it is time to upgrade the
hardware.
Here are some monitors you can put on (for memory only):
Memory Object: Pages/Sec
Memory Object: Available Bytes
SQLServer: Memory Manager: Total Server Memory
SQLServer: Memory Manager: Target Server Memory
Alternatively, you can turn on SQL Profiler if you are more interested
what MSSQL doing all day longs. It has CPU, I/O and memory eventlog
you can trace.
Mel|||The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
> > Hi,
> >
> > I admin a server, dual 3,6 xeon with 4GB ram.
> >
> > Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> >
> > The server have around 800 dynamic sites.
> >
> > SQL 2000 have 1Gb memory for it.
> >
> >
> > the problem is:
> > the CPU time after a SQL restart of service keeps getting higher and higher,
> > i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> > wich is the reserved memory for SQL.
> >
> > Is it normal the CPU time keeping getting higher? or should refresh? like
> > dllhost.exe in the webserver.
> >
> > Is it normal the memory stays at 1Gb or also should refresh?
> >
> > The problem is with some hours/days sites start to open slow and when i
> > restart the SQL service, sites start to open in few seconds, but after some
> > day/days start to be slow again.
> >
> > Its a very busy server with some portals, a few, others sites dynamic also
> > but with few hits. But almost 100% of sites uses, except the portals, read
> > for one of the portals database.
> >
> > Im not the programmer of the sites, and also i dont make the SQL statements
> > need for each site, or design the databases.
> >
> > Tkx in advance,
> > PV|||You will need to use SQL Profiler to capture the CPU and memory usage.
As the monitor will also cause some system resources, so you normally
turn it on for a period of time that you think it is a typical business
workload.
After you have the trace, you can either just open in Profiler or even
better import into as a trace table (built-in feature of Profiler -
Save as trace table). From the trace you can then see what processes
have highest CPU/memory usage, using select sql.
As you quoted another server running just fine, it could well be some
poorly written sql scripts/stored procedures are running on the server.
With the trace, you can then identify the root of the problem (if
any). E.g. a stored procedure that keep re-compile for no good reason,
session no closed down properly.
Mel|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:
>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVIt sounds to me that the server is overloaded.
Normally if it is a live environment, you have a dedicated server just
for MSSQL.
Normally it is not advisable to change the default memory setting. For
optimum performance, SQL Server should be allowed to take as much as
RAM as it wants for its own use without having to compete for RAM with
other applications.
You can turn on some performance monitors to see how busy your system
is and justify to the owner of the machine it is time to upgrade the
hardware.
Here are some monitors you can put on (for memory only):
Memory Object: Pages/Sec
Memory Object: Available Bytes
SQLServer: Memory Manager: Total Server Memory
SQLServer: Memory Manager: Target Server Memory
Alternatively, you can turn on SQL Profiler if you are more interested
what MSSQL doing all day longs. It has CPU, I/O and memory eventlog
you can trace.
Mel|||The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
> > Hi,
> >
> > I admin a server, dual 3,6 xeon with 4GB ram.
> >
> > Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> >
> > The server have around 800 dynamic sites.
> >
> > SQL 2000 have 1Gb memory for it.
> >
> >
> > the problem is:
> > the CPU time after a SQL restart of service keeps getting higher and higher,
> > i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> > wich is the reserved memory for SQL.
> >
> > Is it normal the CPU time keeping getting higher? or should refresh? like
> > dllhost.exe in the webserver.
> >
> > Is it normal the memory stays at 1Gb or also should refresh?
> >
> > The problem is with some hours/days sites start to open slow and when i
> > restart the SQL service, sites start to open in few seconds, but after some
> > day/days start to be slow again.
> >
> > Its a very busy server with some portals, a few, others sites dynamic also
> > but with few hits. But almost 100% of sites uses, except the portals, read
> > for one of the portals database.
> >
> > Im not the programmer of the sites, and also i dont make the SQL statements
> > need for each site, or design the databases.
> >
> > Tkx in advance,
> > PV|||You will need to use SQL Profiler to capture the CPU and memory usage.
As the monitor will also cause some system resources, so you normally
turn it on for a period of time that you think it is a typical business
workload.
After you have the trace, you can either just open in Profiler or even
better import into as a trace table (built-in feature of Profiler -
Save as trace table). From the trace you can then see what processes
have highest CPU/memory usage, using select sql.
As you quoted another server running just fine, it could well be some
poorly written sql scripts/stored procedures are running on the server.
With the trace, you can then identify the root of the problem (if
any). E.g. a stored procedure that keep re-compile for no good reason,
session no closed down properly.
Mel|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:
>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV
high CPU time and sql 2000 getting slow performance
Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PV
The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV
|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PV
The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV
|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
Subscribe to:
Posts (Atom)