memory usage jumps

I have an instance of SQL server installed on a P3 1GHz with 512Mb RAM. This
is a small development machine so there's very little stress on it. SQL
server is pretty much the only app running as I turned off IIS and all web
server stuff.
When I start the server, the RAM usage is around 150Mb. After a little while
(seems random but within an hour) the RAM usage jumps to 600Mb. This is not
a gradual increase to 600Mb but rather a jump. If I restart the mssqlserver
service, then the RAM usage goes back down to 150Mb and the cycle starts
again.
There are only 6 DBs defined, I turned off all the jobs and there is no such
things as DTS etc... the config is very simple
Any idea what could be causing that?
ThanksIt doesn't take much to eat up a few 100 MB of ram. A large regular join or
Cartesian join can easily do this. I would suggest running profiler to see
what is happening when this occurs.
--
Andrew J. Kelly
SQL Server MVP
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:ukTtPGTiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> I have an instance of SQL server installed on a P3 1GHz with 512Mb RAM.
This
> is a small development machine so there's very little stress on it. SQL
> server is pretty much the only app running as I turned off IIS and all web
> server stuff.
> When I start the server, the RAM usage is around 150Mb. After a little
while
> (seems random but within an hour) the RAM usage jumps to 600Mb. This is
not
> a gradual increase to 600Mb but rather a jump. If I restart the
mssqlserver
> service, then the RAM usage goes back down to 150Mb and the cycle starts
> again.
> There are only 6 DBs defined, I turned off all the jobs and there is no
such
> things as DTS etc... the config is very simple
> Any idea what could be causing that?
> Thanks
>|||Try to avoid pulling data from the disk into the buffer pool. To do this,
avoid all DML, like Select's or Updates.
You should then see memory remain pretty constant.
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:ukTtPGTiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> I have an instance of SQL server installed on a P3 1GHz with 512Mb RAM.
This
> is a small development machine so there's very little stress on it. SQL
> server is pretty much the only app running as I turned off IIS and all web
> server stuff.
> When I start the server, the RAM usage is around 150Mb. After a little
while
> (seems random but within an hour) the RAM usage jumps to 600Mb. This is
not
> a gradual increase to 600Mb but rather a jump. If I restart the
mssqlserver
> service, then the RAM usage goes back down to 150Mb and the cycle starts
> again.
> There are only 6 DBs defined, I turned off all the jobs and there is no
such
> things as DTS etc... the config is very simple
> Any idea what could be causing that?
> Thanks
>|||what do you mean by DML?
do you mean replacing SELECT and UPDATE by stored procs?
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:us3SVPWiDHA.2960@.TK2MSFTNGP11.phx.gbl...
> Try to avoid pulling data from the disk into the buffer pool. To do this,
> avoid all DML, like Select's or Updates.
> You should then see memory remain pretty constant.
>
> "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> news:ukTtPGTiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> > I have an instance of SQL server installed on a P3 1GHz with 512Mb RAM.
> This
> > is a small development machine so there's very little stress on it. SQL
> > server is pretty much the only app running as I turned off IIS and all
web
> > server stuff.
> >
> > When I start the server, the RAM usage is around 150Mb. After a little
> while
> > (seems random but within an hour) the RAM usage jumps to 600Mb. This is
> not
> > a gradual increase to 600Mb but rather a jump. If I restart the
> mssqlserver
> > service, then the RAM usage goes back down to 150Mb and the cycle starts
> > again.
> >
> > There are only 6 DBs defined, I turned off all the jobs and there is no
> such
> > things as DTS etc... the config is very simple
> >
> > Any idea what could be causing that?
> >
> > Thanks
> >
> >
>|||> Try to avoid pulling data from the disk into the buffer pool. To do this,
> avoid all DML, like Select's or Updates.
Eh, doesn't that make SQL Server a pretty useless product?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:us3SVPWiDHA.2960@.TK2MSFTNGP11.phx.gbl...
> Try to avoid pulling data from the disk into the buffer pool. To do this,
> avoid all DML, like Select's or Updates.
> You should then see memory remain pretty constant.
>
> "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> news:ukTtPGTiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> > I have an instance of SQL server installed on a P3 1GHz with 512Mb RAM.
> This
> > is a small development machine so there's very little stress on it. SQL
> > server is pretty much the only app running as I turned off IIS and all web
> > server stuff.
> >
> > When I start the server, the RAM usage is around 150Mb. After a little
> while
> > (seems random but within an hour) the RAM usage jumps to 600Mb. This is
> not
> > a gradual increase to 600Mb but rather a jump. If I restart the
> mssqlserver
> > service, then the RAM usage goes back down to 150Mb and the cycle starts
> > again.
> >
> > There are only 6 DBs defined, I turned off all the jobs and there is no
> such
> > things as DTS etc... the config is very simple
> >
> > Any idea what could be causing that?
> >
> > Thanks
> >
> >
>

Memory Usage Following ISA Installation

Hi There
I have just installed ISA 2004 to a Win 2003 server. This had also installed
an instance of SQL MSDE which is eating up the memory. Is there an easy
command prompt statement or registry hack I can run to restrict the maximum
memory usage possible as we are regularly getting "Low Virtual Memory" errors
as it does not seem to be releasing the memory quickly enough. I'm not very
familiar with SQL, so any help out be greatly appriciated. There is no SQL or
MSDE option under the programs list in the Start Menu, so I can't open
Enterprise manager like you would on a normal SQL box. However, there is a
SQL folder within the Program Files directory although this looks a bit slim
compared to a normal install. Failing being able to restrict memory usage, is
it feasble to remove the MSDE installation? I also have Backup Exec running
which is dependant upon the MSSQL service.
Many thanks
Bob
INF: SQL Server Memory Usage:
http://support.microsoft.com/default...;en-us;q321363
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Sampson" wrote:

> Hi There
> I have just installed ISA 2004 to a Win 2003 server. This had also installed
> an instance of SQL MSDE which is eating up the memory. Is there an easy
> command prompt statement or registry hack I can run to restrict the maximum
> memory usage possible as we are regularly getting "Low Virtual Memory" errors
> as it does not seem to be releasing the memory quickly enough. I'm not very
> familiar with SQL, so any help out be greatly appriciated. There is no SQL or
> MSDE option under the programs list in the Start Menu, so I can't open
> Enterprise manager like you would on a normal SQL box. However, there is a
> SQL folder within the Program Files directory although this looks a bit slim
> compared to a normal install. Failing being able to restrict memory usage, is
> it feasble to remove the MSDE installation? I also have Backup Exec running
> which is dependant upon the MSSQL service.
> Many thanks
> Bob
|||Thanks for the reply, however, maybe its me, but I didn't think that really
answered my problem, maybe I'm not looking in the right places, but where is
the Min and Max memory settings? Is it something I have to do from a command
prompt?
Bob
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> INF: SQL Server Memory Usage:
> http://support.microsoft.com/default...;en-us;q321363
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Bob Sampson" wrote:
|||Hi
Yes. MSDE does not have a UI, you need to run T-SQL code through OSQL.
To set a minimum of 64Mb and a Maximum of 256Mb, run from the command
prompt:
osql -Q"sp_configure 'min server memory', 64 RECONFIGURE WITH OVERRIDE" -E
osql -Q"sp_configure 'sp_configure 'max server memory', 256 RECONFIGURE WITH
OVERRIDE" -E
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Sampson" <BobSampson@.discussions.microsoft.com> wrote in message
news:44FB02FD-8B86-4061-B8AC-D2D4F9F38165@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, however, maybe its me, but I didn't think that
> really
> answered my problem, maybe I'm not looking in the right places, but where
> is
> the Min and Max memory settings? Is it something I have to do from a
> command
> prompt?
> Bob
> "Mike Epprecht (SQL MVP)" wrote:

Memory Usage

Hello Everybody,
We have a SQL SERVER installed on a Windows 2003 SERVER which has memory of
3.5 GB. We have configured SQL SERVER as a Dynamically Configure SQL SERVER
memory when we installed. Now when we check memory usage of SQL SERVER
sqlservr.exe is using a 1.7 GB of memory ? os just wondering is it normal ?
Pls let us know.Which version of sql server 2000 do you have?. If it is standard, then it ca
n
use 2gbytes max, if it is enterprise, then you have to configure the server
in order to sql server use more than 2gb.
How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/kb/274750/
AMB
"mvp" wrote:

> Hello Everybody,
> We have a SQL SERVER installed on a Windows 2003 SERVER which has memory o
f
> 3.5 GB. We have configured SQL SERVER as a Dynamically Configure SQL SERVE
R
> memory when we installed. Now when we check memory usage of SQL SERVER
> sqlservr.exe is using a 1.7 GB of memory ? os just wondering is it normal
?
> Pls let us know.|||mvp wrote:
> Hello Everybody,
> We have a SQL SERVER installed on a Windows 2003 SERVER which has
> memory of
> 3.5 GB. We have configured SQL SERVER as a Dynamically Configure SQL
> SERVER memory when we installed. Now when we check memory usage of
> SQL SERVER sqlservr.exe is using a 1.7 GB of memory ? os just
> wondering is it normal ?
> Pls let us know.
Are you wondering why it's using 1.7GB or why it's not using more? SQL
Server does not release memory back to the OS once it claims it, except
under certain server conditions. If you want SQL Server to not use more
than a specified amount of memory, you can set an upper memory limit.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> We have a SQL SERVER installed on a Windows 2003 SERVER which has memory
> of
> 3.5 GB. We have configured SQL SERVER as a Dynamically Configure SQL
> SERVER
> memory when we installed. Now when we check memory usage of SQL SERVER
> sqlservr.exe is using a 1.7 GB of memory ? os just wondering is it normal
> ?
Sure, you have it set to take the memory it needs, and so far it has only
needed 1.7 GB. If you want to start it higher, you can do so...

Memory Usage

Hello Everybody,
We have a SQL SERVER installed on a Windows 2003 SERVER which has memory of
3.5 GB. We have configured SQL SERVER as a Dynamically Configure SQL SERVER
memory when we installed. Now when we check memory usage of SQL SERVER
sqlservr.exe is using a 1.7 GB of memory ? os just wondering is it normal ?
Pls let us know.
Its Normal if You have Memory more than 4 gb u can enable awe and set
max server memory to 3.5 to be allocated to sql and rest for the Os but
with less than 4 its better u leave it dynamically, check out the
Paging File configuration u can work around it and see if its
configured corrrectly.
Thanks,
Saradhi
|||Yes, it is normal. Sql will acquire memory as needed, but not release it,
even if no one is currently connected. This prevents memory thrashing and
allows caches to stay loaded.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:426DDB91-F9A8-4E2F-9545-6978AA570E9C@.microsoft.com...
> Hello Everybody,
> We have a SQL SERVER installed on a Windows 2003 SERVER which has memory
> of
> 3.5 GB. We have configured SQL SERVER as a Dynamically Configure SQL
> SERVER
> memory when we installed. Now when we check memory usage of SQL SERVER
> sqlservr.exe is using a 1.7 GB of memory ? os just wondering is it normal
> ?
> Pls let us know.

Memory Usage

How much memory can SQL Server 2000 Enterprise utilize when installed on an
x64 system with Windows Server 2003 Enterprise 64 bit?
Thanks in advance
Dan
512Gb on a 64bits system, but I don't know if this limit is for SQL 64bits +
Windows 64 Bits or SQL 32bits on Win 64Bits
the limit on a 32Bits system is 64Gb
read this:
http://download.microsoft.com/downlo...45/ChoosEd.doc
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:16491266-A2ED-4850-8592-096EA5828A4B@.microsoft.com...
> How much memory can SQL Server 2000 Enterprise utilize when installed on
> an
> x64 system with Windows Server 2003 Enterprise 64 bit?
> Thanks in advance
> Dan

Memory Usage

Hello Everybody,
We have a SQL SERVER installed on a Windows 2003 SERVER which has memory of
3.5 GB. We have configured SQL SERVER as a Dynamically Configure SQL SERVER
memory when we installed. Now when we check memory usage of SQL SERVER
sqlservr.exe is using a 1.7 GB of memory ? os just wondering is it normal ?
Pls let us know.Its Normal if You have Memory more than 4 gb u can enable awe and set
max server memory to 3.5 to be allocated to sql and rest for the Os but
with less than 4 its better u leave it dynamically, check out the
Paging File configuration u can work around it and see if its
configured corrrectly.
Thanks,
Saradhi|||Yes, it is normal. Sql will acquire memory as needed, but not release it,
even if no one is currently connected. This prevents memory thrashing and
allows caches to stay loaded.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:426DDB91-F9A8-4E2F-9545-6978AA570E9C@.microsoft.com...
> Hello Everybody,
> We have a SQL SERVER installed on a Windows 2003 SERVER which has memory
> of
> 3.5 GB. We have configured SQL SERVER as a Dynamically Configure SQL
> SERVER
> memory when we installed. Now when we check memory usage of SQL SERVER
> sqlservr.exe is using a 1.7 GB of memory ? os just wondering is it normal
> ?
> Pls let us know.

Memory Usage

How much memory can SQL Server 2000 Enterprise utilize when installed on an
x64 system with Windows Server 2003 Enterprise 64 bit?
Thanks in advance
Dan512Gb on a 64bits system, but I don't know if this limit is for SQL 64bits +
Windows 64 Bits or SQL 32bits on Win 64Bits
the limit on a 32Bits system is 64Gb
read this:
ChoosEd.doc" target="_blank">http://download.microsoft.com/downl.../>
ChoosEd.doc
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:16491266-A2ED-4850-8592-096EA5828A4B@.microsoft.com...
> How much memory can SQL Server 2000 Enterprise utilize when installed on
> an
> x64 system with Windows Server 2003 Enterprise 64 bit?
> Thanks in advance
> Dan

Memory Usage

How much memory can SQL Server 2000 Enterprise utilize when installed on an
x64 system with Windows Server 2003 Enterprise 64 bit?
Thanks in advance
Dan512Gb on a 64bits system, but I don't know if this limit is for SQL 64bits +
Windows 64 Bits or SQL 32bits on Win 64Bits
the limit on a 32Bits system is 64Gb
read this:
http://download.microsoft.com/download/8/1/7/817bb6e6-9d97-4a5d-be8d-508256ae4045/ChoosEd.doc
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:16491266-A2ED-4850-8592-096EA5828A4B@.microsoft.com...
> How much memory can SQL Server 2000 Enterprise utilize when installed on
> an
> x64 system with Windows Server 2003 Enterprise 64 bit?
> Thanks in advance
> Dan

Memory Usage

Hello Everybody,
We have a SQL SERVER installed on a Windows 2003 SERVER which has memory of
3.5 GB. We have configured SQL SERVER as a Dynamically Configure SQL SERVER
memory when we installed. Now when we check memory usage of SQL SERVER
sqlservr.exe is using a 1.7 GB of memory ? os just wondering is it normal ?
Pls let us know.Its Normal if You have Memory more than 4 gb u can enable awe and set
max server memory to 3.5 to be allocated to sql and rest for the Os but
with less than 4 its better u leave it dynamically, check out the
Paging File configuration u can work around it and see if its
configured corrrectly.
Thanks,
Saradhi|||Yes, it is normal. Sql will acquire memory as needed, but not release it,
even if no one is currently connected. This prevents memory thrashing and
allows caches to stay loaded.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:426DDB91-F9A8-4E2F-9545-6978AA570E9C@.microsoft.com...
> Hello Everybody,
> We have a SQL SERVER installed on a Windows 2003 SERVER which has memory
> of
> 3.5 GB. We have configured SQL SERVER as a Dynamically Configure SQL
> SERVER
> memory when we installed. Now when we check memory usage of SQL SERVER
> sqlservr.exe is using a 1.7 GB of memory ? os just wondering is it normal
> ?
> Pls let us know.

memory setting in SQL 2005 in Windows 2003

We have a SQL 2005 installed in Windows 2003 64 bit system, which has 16 CPU and 32GB RAM, but the performance is poor. SQL server is AWE enable and the sql start account with "Lock Pages In Memory". I checked the task manager and it looks that the SQL server used only about 300 MB memory. Here is what I found:

http://blogs.msdn.com/psssql/archive/2006/11/28/sql-server-becomes-sluggish-or-appears-to-stall-on-64-bit-installations.aspx?CommentPosted=true#commentmessage

What I need to do?

Thanks

Sounds like you have installed the 32 bit version of SQL Server, not the 64bit version of SQL Server.

Can you verify which version of SQL Server was installed?

|||

If I recall, AWE has no effect in 64 bit OS, and extended memory usage is not properly captured with Perf Monitor.

Please refer to this article:

Configuration -Memory, 32 bit SQL 2005 on 64 Bit Windows 2003
http://msdn2.microsoft.com/en-us/library/ms187499.aspx

And for reference on Memory, this is good starting point:

http://msdn2.microsoft.com/en-us/library/ms187499.aspx

|||

Hi Sandlu,

AWE will have no effect on 64 bit servers.

Yes you still need "LOCK PAGES IN MEMORY".

To check exactly how much memory is being used by SQL Server use Perfmon to check for SQL ServerMemoryManager:

Total Server Memory (KB)

Also check if you have capped the memory by using Max Server Memory under sp_configure.

With regards to slow performance, it could be any problem like disks, memory or no/out of date stats or Index defragmentation.

Jag

|||Thank you, everyone, will try.

memory prob w/SQL 2000

We just purchased a new server - Dell 6550, quad 2G
processors, 6G RAM running Windows 2000 Advanced Server.
I installed SQL 2000 Standard Edition and moved my
databases to this new system. My problem is that now the
sql service will continually use memory until it gets to
about 1.7G and then it locks up the service. No one can
login to the application that uses the db's and you can't
access the db's via Enterprise Manager or sql Query.
Stopping and restarting the service does not help. I have
to actually reboot the server. I tried using the memory
setting in the sql server properties but it doesn't help.
Any ideas or solutions?
Thanks for your help!
CathyYou need to set the /3GB and /PAE switches in the boot.ini file and enable
the AWE option in SQL Server to use the full 6GB. Since AWE will use all
the memory in a fixed manner you should then set the max memory setting in
Sql Server to something less than 6GB. See here for more details:
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
Andrew J. Kelly
SQL Server MVP
"Cathy Tumlinson" <cathyt@.tcmhmrs.org> wrote in message
news:00b301c34497$8bb9b5a0$a101280a@.phx.gbl...
> We just purchased a new server - Dell 6550, quad 2G
> processors, 6G RAM running Windows 2000 Advanced Server.
> I installed SQL 2000 Standard Edition and moved my
> databases to this new system. My problem is that now the
> sql service will continually use memory until it gets to
> about 1.7G and then it locks up the service. No one can
> login to the application that uses the db's and you can't
> access the db's via Enterprise Manager or sql Query.
> Stopping and restarting the service does not help. I have
> to actually reboot the server. I tried using the memory
> setting in the sql server properties but it doesn't help.
> Any ideas or solutions?
> Thanks for your help!
> Cathy|||I didn't catch the std edition in the original post. Std can only support
2GB of ram and you will only get 1.7GB in reality. Having 6GB on that
machine is pretty much a waste if you only have 1 instance of sql server
running. I am not convinced this is a sql server issue. Stopping and
restarting the sql server service should certainly free up any memory it had
previously taken and everything should run as normal. Not that using all
the ram should kill it in the first place. If you have to reboot then I
would suspect a server issue more than a sql one. Does the rest of the OS
behave normally when this happens? What are the cpu's and disks like when
this occurs? To get to the bottom as fast as possible you might want to
call ms pss so they can walk through this with you and they can then get a
much better feel for how everything is acting and set up.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Cathy Tumlinson" <cathyt@.tcmhmrs.org> wrote in message
news:0cc101c344a8$f0ab0480$a501280a@.phx.gbl...
> I'm running MS SQL 2000 Standard Edition. I don't have an
> AWE option.
> Cathy
> >--Original Message--
> >Do you have AWE memory setup on you server? Sometimes I
> found that AWE
> >settings cause this
> >
> >DRC
> >Alphabet collector (MCP,MCSD,MCSA,NECD ECT.)
> >
> >*** Sent via Developersdex http://www.developersdex.com
> ***
> >Don't just participate in USENET...get rewarded for it!
> >.
> >|||you might try the startup parameter -g100 (or higher)
this tells SQL Server to reserve a certain amount of
address space for memory allocations excluding the buffer
pool.
What might be happening is that the buffer pool starting
allocation memory (because so much is available) to the
point where an insufficient amount of address space is
left for other functions
the setting is in the SQL Server properties
page, "General" tab, "Startup Parameters" button
depending on the allocation needs of your particular app,
you may need a higher setting, or you could get by with a
lower setting,
>--Original Message--
>We just purchased a new server - Dell 6550, quad 2G
>processors, 6G RAM running Windows 2000 Advanced
Server.
>I installed SQL 2000 Standard Edition and moved my
>databases to this new system. My problem is that now
the
>sql service will continually use memory until it gets to
>about 1.7G and then it locks up the service. No one can
>login to the application that uses the db's and you
can't
>access the db's via Enterprise Manager or sql Query.
>Stopping and restarting the service does not help. I
have
>to actually reboot the server. I tried using the memory
>setting in the sql server properties but it doesn't help.
>Any ideas or solutions?
>Thanks for your help!
>Cathy
>.
>