Sql Memory Leaks: dynamically

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...

Hi,
We have a server with 4gb of memory, the server is configured to dynamically
allocate memory to the max yet consistently only uses 2gb.
We are using enterprise edition on 2k advanced server. Am I missing
something? Is there something else limiting the server to 2gb?
Any pointers appreciated
Ta
SimonHave a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
"skilla31" <simon@.ris.org.uk> wrote in message
news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have a server with 4gb of memory, the server is configured to
dynamically
> allocate memory to the max yet consistently only uses 2gb.
> We are using enterprise edition on 2k advanced server. Am I missing
> something? Is there something else limiting the server to 2gb?
> Any pointers appreciated
> Ta
> Simon
>|||See if the following link helps:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
"skilla31" wrote:
> Hi,
> We have a server with 4gb of memory, the server is configured to dynamically
> allocate memory to the max yet consistently only uses 2gb.
> We are using enterprise edition on 2k advanced server. Am I missing
> something? Is there something else limiting the server to 2gb?
> Any pointers appreciated
> Ta
> Simon
>
>|||Wow,
Thanks Uri. Does this really mean that by default sql server will only use
2GB? Unless you use AWE
The devil really is in the detail
Simon
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Have a look at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>
>
>
> "skilla31" <simon@.ris.org.uk> wrote in message
> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a server with 4gb of memory, the server is configured to
> dynamically
>> allocate memory to the max yet consistently only uses 2gb.
>> We are using enterprise edition on 2k advanced server. Am I missing
>> something? Is there something else limiting the server to 2gb?
>> Any pointers appreciated
>> Ta
>> Simon
>>
>|||Set the /3GB switch in the Boot.ini file and it will be able to use 3GB. It
is usually best to leave the other 1GB for the OS and any other apps thay
may run on the server. You can use AWE to use some of the memory in between
3 and 4GB but it is usually not worth the effort for a few hundred MB's.
--
Andrew J. Kelly SQL MVP
"skilla31" <simon@.ris.org.uk> wrote in message
news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
> Wow,
> Thanks Uri. Does this really mean that by default sql server will only use
> 2GB? Unless you use AWE
> The devil really is in the detail
> Simon
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>>
>>
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a server with 4gb of memory, the server is configured to
>> dynamically
>> allocate memory to the max yet consistently only uses 2gb.
>> We are using enterprise edition on 2k advanced server. Am I missing
>> something? Is there something else limiting the server to 2gb?
>> Any pointers appreciated
>> Ta
>> Simon
>>
>>
>|||This is true for ALL Windows server apps (excluding 64 bit servers.)
the OS for standard servers has 4 GB memory of which a max of 2 GB can be
allocated to ANY program..., so this is not a SQL issue, but an OS issue...
We can use the /3GB boot. ini switch and/or the AWE extensions to increase
memory...
64 bit editiions of the OS will open the door to greater memory ...
--
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
"skilla31" <simon@.ris.org.uk> wrote in message
news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have a server with 4gb of memory, the server is configured to
> dynamically allocate memory to the max yet consistently only uses 2gb.
> We are using enterprise edition on 2k advanced server. Am I missing
> something? Is there something else limiting the server to 2gb?
> Any pointers appreciated
> Ta
> Simon
>|||Thanks Andrew,
We are using advanced server. I'm thinking of upgrading the server to 6GB
and allowing SQL Server to use up to 5GB.
So I'm thinking, I set the switch to /5GB and set max memory to 5GB, is this
feasible
Simon
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O%23S5bhrOFHA.3000@.TK2MSFTNGP10.phx.gbl...
> Set the /3GB switch in the Boot.ini file and it will be able to use 3GB.
> It is usually best to leave the other 1GB for the OS and any other apps
> thay may run on the server. You can use AWE to use some of the memory in
> between 3 and 4GB but it is usually not worth the effort for a few hundred
> MB's.
> --
> Andrew J. Kelly SQL MVP
>
> "skilla31" <simon@.ris.org.uk> wrote in message
> news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
>> Wow,
>> Thanks Uri. Does this really mean that by default sql server will only
>> use 2GB? Unless you use AWE
>> The devil really is in the detail
>> Simon
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>>
>>
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a server with 4gb of memory, the server is configured to
>> dynamically
>> allocate memory to the max yet consistently only uses 2gb.
>> We are using enterprise edition on 2k advanced server. Am I missing
>> something? Is there something else limiting the server to 2gb?
>> Any pointers appreciated
>> Ta
>> Simon
>>
>>
>>
>|||No that is not how it works<g>. You set the /3GB to allow sql server to
access 3GB (vs. 2GB) of directly addressable memory. You use AWE on top of
that to gain access to memory above 4GB and that would leave 1GB for the OS.
So you need to do this:
Set /3GB in Boot.ini (allows apps to use 3GB vs. 2GB)
Set /PAE in Boot.ini (allows the OS to use more than 4GB)
Set AWE enabled wtih sp_configure (Allows Sql Server to access more than
4GB.)
Set MAX Memory to 5GB (Limits the memory usage of Sql server to 5GB since
it is not dynamic with AWE so you leave 1 GB for the OS)
http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
Memory
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 MVP
"skilla31" <simon@.ris.org.uk> wrote in message
news:eZZVCesOFHA.3144@.tk2msftngp13.phx.gbl...
> Thanks Andrew,
> We are using advanced server. I'm thinking of upgrading the server to 6GB
> and allowing SQL Server to use up to 5GB.
> So I'm thinking, I set the switch to /5GB and set max memory to 5GB, is
> this feasible
> Simon
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O%23S5bhrOFHA.3000@.TK2MSFTNGP10.phx.gbl...
>> Set the /3GB switch in the Boot.ini file and it will be able to use 3GB.
>> It is usually best to leave the other 1GB for the OS and any other apps
>> thay may run on the server. You can use AWE to use some of the memory in
>> between 3 and 4GB but it is usually not worth the effort for a few
>> hundred MB's.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
>> Wow,
>> Thanks Uri. Does this really mean that by default sql server will only
>> use 2GB? Unless you use AWE
>> The devil really is in the detail
>> Simon
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>>
>>
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a server with 4gb of memory, the server is configured to
>> dynamically
>> allocate memory to the max yet consistently only uses 2gb.
>> We are using enterprise edition on 2k advanced server. Am I missing
>> something? Is there something else limiting the server to 2gb?
>> Any pointers appreciated
>> Ta
>> Simon
>>
>>
>>
>>
>|||Thanks again Andrew,
Sorry for being so dim, I think I've got this. So based on my desired
configuration (6 GB with 5GB available to sql server and 1GB left for the
OS)
I would
1. set the following switches in the boot.ini /3GB /PAE
2. Then run
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 5120
RECONFIGURE
GO
Interestingly on my test system I think max memory is displayed in KB, when
I try to set max memory in kb above 2GB I get a conversion error (decimal to
int). When I then set max memory in mb and run sp_configure it displays it
mb. Which makes me nervous.
Although I'm almost certain memory is the bottleneck can you point me in the
right direction for articles on memory sizing for db servers
Ta
Simon
PS: didn't get the "<g>" in previous message
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eh2kO1sOFHA.3704@.TK2MSFTNGP12.phx.gbl...
> No that is not how it works<g>. You set the /3GB to allow sql server to
> access 3GB (vs. 2GB) of directly addressable memory. You use AWE on top
> of that to gain access to memory above 4GB and that would leave 1GB for
> the OS. So you need to do this:
> Set /3GB in Boot.ini (allows apps to use 3GB vs. 2GB)
> Set /PAE in Boot.ini (allows the OS to use more than 4GB)
> Set AWE enabled wtih sp_configure (Allows Sql Server to access more than
> 4GB.)
> Set MAX Memory to 5GB (Limits the memory usage of Sql server to 5GB since
> it is not dynamic with AWE so you leave 1 GB for the OS)
>
> http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
> Memory
> 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 MVP
>
> "skilla31" <simon@.ris.org.uk> wrote in message
> news:eZZVCesOFHA.3144@.tk2msftngp13.phx.gbl...
>> Thanks Andrew,
>> We are using advanced server. I'm thinking of upgrading the server to 6GB
>> and allowing SQL Server to use up to 5GB.
>> So I'm thinking, I set the switch to /5GB and set max memory to 5GB, is
>> this feasible
>> Simon
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O%23S5bhrOFHA.3000@.TK2MSFTNGP10.phx.gbl...
>> Set the /3GB switch in the Boot.ini file and it will be able to use 3GB.
>> It is usually best to leave the other 1GB for the OS and any other apps
>> thay may run on the server. You can use AWE to use some of the memory
>> in between 3 and 4GB but it is usually not worth the effort for a few
>> hundred MB's.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
>> Wow,
>> Thanks Uri. Does this really mean that by default sql server will only
>> use 2GB? Unless you use AWE
>> The devil really is in the detail
>> Simon
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>>
>>
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a server with 4gb of memory, the server is configured to
>> dynamically
>> allocate memory to the max yet consistently only uses 2gb.
>> We are using enterprise edition on 2k advanced server. Am I missing
>> something? Is there something else limiting the server to 2gb?
>> Any pointers appreciated
>> Ta
>> Simon
>>
>>
>>
>>
>>
>|||Here are some links related to setting memory but there isn't much you can
do other than determine how much SQL Server will use. Basically give it all
you have after leaving some for the OS. If you run any other apps on the
machine you must account for them as well. If memory is your bottleneck you
most likely have poorly tuned queries or improperly indexed tables. Running
some traces should help to determine that pretty quickly.
http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
Memory
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 MVP
"skilla31" <simon@.ris.org.uk> wrote in message
news:u68zCw2OFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Thanks again Andrew,
> Sorry for being so dim, I think I've got this. So based on my desired
> configuration (6 GB with 5GB available to sql server and 1GB left for the
> OS)
> I would
> 1. set the following switches in the boot.ini /3GB /PAE
> 2. Then run
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 5120
> RECONFIGURE
> GO
> Interestingly on my test system I think max memory is displayed in KB,
> when I try to set max memory in kb above 2GB I get a conversion error
> (decimal to int). When I then set max memory in mb and run sp_configure it
> displays it mb. Which makes me nervous.
> Although I'm almost certain memory is the bottleneck can you point me in
> the right direction for articles on memory sizing for db servers
> Ta
> Simon
> PS: didn't get the "<g>" in previous message
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eh2kO1sOFHA.3704@.TK2MSFTNGP12.phx.gbl...
>> No that is not how it works<g>. You set the /3GB to allow sql server to
>> access 3GB (vs. 2GB) of directly addressable memory. You use AWE on top
>> of that to gain access to memory above 4GB and that would leave 1GB for
>> the OS. So you need to do this:
>> Set /3GB in Boot.ini (allows apps to use 3GB vs. 2GB)
>> Set /PAE in Boot.ini (allows the OS to use more than 4GB)
>> Set AWE enabled wtih sp_configure (Allows Sql Server to access more than
>> 4GB.)
>> Set MAX Memory to 5GB (Limits the memory usage of Sql server to 5GB
>> since it is not dynamic with AWE so you leave 1 GB for the OS)
>>
>> http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
>> Memory
>> 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 MVP
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:eZZVCesOFHA.3144@.tk2msftngp13.phx.gbl...
>> Thanks Andrew,
>> We are using advanced server. I'm thinking of upgrading the server to
>> 6GB and allowing SQL Server to use up to 5GB.
>> So I'm thinking, I set the switch to /5GB and set max memory to 5GB, is
>> this feasible
>> Simon
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O%23S5bhrOFHA.3000@.TK2MSFTNGP10.phx.gbl...
>> Set the /3GB switch in the Boot.ini file and it will be able to use
>> 3GB. It is usually best to leave the other 1GB for the OS and any other
>> apps thay may run on the server. You can use AWE to use some of the
>> memory in between 3 and 4GB but it is usually not worth the effort for
>> a few hundred MB's.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
>> Wow,
>> Thanks Uri. Does this really mean that by default sql server will only
>> use 2GB? Unless you use AWE
>> The devil really is in the detail
>> Simon
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>>
>>
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>>> Hi,
>>>
>>> We have a server with 4gb of memory, the server is configured to
>> dynamically
>>> allocate memory to the max yet consistently only uses 2gb.
>>>
>>> We are using enterprise edition on 2k advanced server. Am I missing
>>> something? Is there something else limiting the server to 2gb?
>>>
>>> Any pointers appreciated
>>>
>>> Ta
>>>
>>> Simon
>>>
>>>
>>
>>
>>
>>
>>
>

Monday, March 12, 2012

Memory Usage

Memory Usage

Memory Usage

Friday, March 9, 2012

Memory Usage

Memory usage

Sql Memory Leaks

Blog Archive

About Me