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