I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.
DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Showing posts with label 4gb. Show all posts
Showing posts with label 4gb. Show all posts
Friday, March 23, 2012
High paging but plenty of memory available
I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" => 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" => 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
High paging but plenty of memory available
I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on th
e
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on th
e
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
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:
Friday, March 9, 2012
High Availability
Hi all,
Our production DB is around 4GB and peaks at around 8000
orders a day. We currently have a active/passive cluster
failover solution. During the holiday season, the quorum
disk for the cluster failed. It unfortunately took a long
time (7-10 hours) to recover. Now, someone is suggesting
that we set up an additional and separate active/passive
cluster, and set up log shipping between the existing
cluster (primary) and the new cluster (secondary).
My concern is that it may take too long to make the
secondary server ready for production. We have
replication running on the server, it could take time to
reconfigure the articles.
What are your concerns over this solution? How did you
set up your server?
http://www.microsoft.com/technet/treeview/default.asp?
url=/TechNet/prodtechnol/sql/reskit/sql2000/part4/c1561.asp
Thanks in advance,
AnnaHi, if you want a high availability solution, you need
spare disk in your storages, even with spare, your Quorum
disk must be in a RAID-1 array.
In your scenario, if your primary server failed, you just
have to change the IP of you secondary server to IP of
your primary server.
And you have to be a nice backup schema.
Cesar Kubo
>--Original Message--
>Hi all,
>Our production DB is around 4GB and peaks at around 8000
>orders a day. We currently have a active/passive cluster
>failover solution. During the holiday season, the quorum
>disk for the cluster failed. It unfortunately took a long
>time (7-10 hours) to recover. Now, someone is suggesting
>that we set up an additional and separate active/passive
>cluster, and set up log shipping between the existing
>cluster (primary) and the new cluster (secondary).
>My concern is that it may take too long to make the
>secondary server ready for production. We have
>replication running on the server, it could take time to
>reconfigure the articles.
>What are your concerns over this solution? How did you
>set up your server?
>
>http://www.microsoft.com/technet/treeview/default.asp?
>url=/TechNet/prodtechnol/sql/reskit/sql2000/part4/c1561.as
p
>Thanks in advance,
>Anna
>.
>
Our production DB is around 4GB and peaks at around 8000
orders a day. We currently have a active/passive cluster
failover solution. During the holiday season, the quorum
disk for the cluster failed. It unfortunately took a long
time (7-10 hours) to recover. Now, someone is suggesting
that we set up an additional and separate active/passive
cluster, and set up log shipping between the existing
cluster (primary) and the new cluster (secondary).
My concern is that it may take too long to make the
secondary server ready for production. We have
replication running on the server, it could take time to
reconfigure the articles.
What are your concerns over this solution? How did you
set up your server?
http://www.microsoft.com/technet/treeview/default.asp?
url=/TechNet/prodtechnol/sql/reskit/sql2000/part4/c1561.asp
Thanks in advance,
AnnaHi, if you want a high availability solution, you need
spare disk in your storages, even with spare, your Quorum
disk must be in a RAID-1 array.
In your scenario, if your primary server failed, you just
have to change the IP of you secondary server to IP of
your primary server.
And you have to be a nice backup schema.
Cesar Kubo
>--Original Message--
>Hi all,
>Our production DB is around 4GB and peaks at around 8000
>orders a day. We currently have a active/passive cluster
>failover solution. During the holiday season, the quorum
>disk for the cluster failed. It unfortunately took a long
>time (7-10 hours) to recover. Now, someone is suggesting
>that we set up an additional and separate active/passive
>cluster, and set up log shipping between the existing
>cluster (primary) and the new cluster (secondary).
>My concern is that it may take too long to make the
>secondary server ready for production. We have
>replication running on the server, it could take time to
>reconfigure the articles.
>What are your concerns over this solution? How did you
>set up your server?
>
>http://www.microsoft.com/technet/treeview/default.asp?
>url=/TechNet/prodtechnol/sql/reskit/sql2000/part4/c1561.as
p
>Thanks in advance,
>Anna
>.
>
Subscribe to:
Posts (Atom)