SQL SQLExpress: memory

Showing posts with label memory. Show all posts

Sunday, March 25, 2012

@@connections

I currently get 47 million connections back when I run 'select @.@.connections'. At what point does this become a problem with memory? And, how can I close connections from the backend? I know stopping and restarting will do it but that is not always possible. Any ideas? Thank You

You're not interpreting this number correctly. @.@.connections returns the total number of connections that have been made or attempted since SQL Server was last started. It has no relation whatsoever to the number of open or active connections.

To see or count open connections, execute sp_who or sp_who2.

|||Thanks for clearing that up. The mem usage for our sqlserver is at 3 gig, is that normal?sql

Tuesday, March 20, 2012

/pae /3gb switch

Hello,
I have a pair of clustered sql server with 12GB of memory in each. What do
you recommend in allocating memory space? I was told to add a /3gb and /pae
switch is boot.ini. please let me know if this is best practice.
thanks!
Calvin
doc@.ebay.com
sorry... forgot some details. both boxes are W2K3 enterprise with sql 2000
enterprise.
"Calvin Do" wrote:

> Hello,
> I have a pair of clustered sql server with 12GB of memory in each. What do
> you recommend in allocating memory space? I was told to add a /3gb and /pae
> switch is boot.ini. please let me know if this is best practice.
>
> thanks!
> --
> Calvin
> doc@.ebay.com
|||Calvin,
Enabling /PAE will enable Windows address more than 4gb... good way of making use of available RAM.
/3gb switch will make 3gb of virtual memory address space available to Sql server for Buffer cache.
Turn on AWE for data-caching on your available physical memory.
All these are ways of allocating more memory for sql server use.
http://support.microsoft.com/default...;en-us;q321363
http://msdn.microsoft.com/library/de...ar_sa_6b3k.asp
Also refer to this earlier post below:
Definitely apply the hotfix mentioned for article 834628. Another DBA here
ran into something that he thinks may have been caused by this issue.
Don't use /3GB when you have more than 12 GB of RAM in your system. The OS
needs the extra mamory to handle mapping AWE memory back into the lower 4GB
address space. Finally, set the MAX SERVER MEMORY to about 2-4GB less than
your physical memory.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Larry Kippenhan" <lwkthrowaway1@.comcast.net> wrote in message
news:e$pzBY9rEHA.2128@.TK2MSFTNGP10.phx.gbl...
> I have a couple of questions that I hope y'all will be able to give me
> some info on.
> I am installing SQL Server 2000 on a Win2K3 Enterprise server with 20GB
> of RAM (I know, odd number), anyway, as I was searching for information
> on enabling all of my memory, and the /PAE option, I came across a KB
> article, #834628
> (http://support.microsoft.com/default...b;en-us;834628), titled
> "Data is corrupted when PAE is enabled on a Windows Server 2003-based
> computer". I expect this machine to give the resources a pretty good
> workout, and I was concerned about running into this issue. Has any of
> you run into this issue? Or, how likely is it that I will experience
> this. Microsoft suggests not installing the hotfix unless you are
> severely affected by this bug. To me, ANY corruption counts as severe,
> and I really don't want to have to do all of the restores, and send 400
> people home, etc, etc, while I fix this thing. So, any info here is
> appreciated.
> Second question I had was: with the /PAE option, and awe enabled on the
> SQL Server, it consumed as much of the available memory as it could
> (about 19.7 GB). This in itself doesn't bother me, but what had me
> puzzled, was that it appeared to be using the memory that was below the
> 4GB line. I really expected that it wouldn't since I hadn't used the
> /3GB switch. Any thoughts on this? Is /3GB not needed anymore, or is
> my understanding not in line with reality?
> Thanks again for your insights.
> Larry
|||thanks, appreciate your response.
"TJ" wrote:

> Calvin,
> Enabling /PAE will enable Windows address more than 4gb... good way of making use of available RAM.
> /3gb switch will make 3gb of virtual memory address space available to Sql server for Buffer cache.
> Turn on AWE for data-caching on your available physical memory.
> All these are ways of allocating more memory for sql server use.
> http://support.microsoft.com/default...;en-us;q321363
> http://msdn.microsoft.com/library/de...ar_sa_6b3k.asp
>
> Also refer to this earlier post below:
>
> Definitely apply the hotfix mentioned for article 834628. Another DBA here
> ran into something that he thinks may have been caused by this issue.
> Don't use /3GB when you have more than 12 GB of RAM in your system. The OS
> needs the extra mamory to handle mapping AWE memory back into the lower 4GB
> address space. Finally, set the MAX SERVER MEMORY to about 2-4GB less than
> your physical memory.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Larry Kippenhan" <lwkthrowaway1@.comcast.net> wrote in message
> news:e$pzBY9rEHA.2128@.TK2MSFTNGP10.phx.gbl...
>
sql

/pae /3gb switch

Hello,
I have a pair of clustered sql server with 12GB of memory in each. What do
you recommend in allocating memory space? I was told to add a /3gb and /pae
switch is boot.ini. please let me know if this is best practice.
thanks!
--
Calvin
doc@.ebay.comsorry... forgot some details. both boxes are W2K3 enterprise with sql 2000
enterprise.
"Calvin Do" wrote:

> Hello,
> I have a pair of clustered sql server with 12GB of memory in each. What do
> you recommend in allocating memory space? I was told to add a /3gb and /pa
e
> switch is boot.ini. please let me know if this is best practice.
>
> thanks!
> --
> Calvin
> doc@.ebay.com|||Calvin,
Enabling /PAE will enable Windows address more than 4gb... good way of maki
ng use of available RAM.
/3gb switch will make 3gb of virtual memory address space available to Sql s
erver for Buffer cache.
Turn on AWE for data-caching on your available physical memory.
All these are ways of allocating more memory for sql server use.
http://support.microsoft.com/defaul...b;en-us;q321363
http://msdn.microsoft.com/library/d...br />
6b3k.asp
Also refer to this earlier post below:
Definitely apply the hotfix mentioned for article 834628. Another DBA here
ran into something that he thinks may have been caused by this issue.
Don't use /3GB when you have more than 12 GB of RAM in your system. The OS
needs the extra mamory to handle mapping AWE memory back into the lower 4GB
address space. Finally, set the MAX SERVER MEMORY to about 2-4GB less than
your physical memory.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Larry Kippenhan" <lwkthrowaway1@.comcast.net> wrote in message
news:e$pzBY9rEHA.2128@.TK2MSFTNGP10.phx.gbl...
> I have a couple of questions that I hope y'all will be able to give me
> some info on.
> I am installing SQL Server 2000 on a Win2K3 Enterprise server with 20GB
> of RAM (I know, odd number), anyway, as I was searching for information
> on enabling all of my memory, and the /PAE option, I came across a KB
> article, #834628
> (http://support.microsoft.com/defaul...kb;en-us;834628), titled
> "Data is corrupted when PAE is enabled on a Windows Server 2003-based
> computer". I expect this machine to give the resources a pretty good
> workout, and I was concerned about running into this issue. Has any of
> you run into this issue? Or, how likely is it that I will experience
> this. Microsoft suggests not installing the hotfix unless you are
> severely affected by this bug. To me, ANY corruption counts as severe,
> and I really don't want to have to do all of the restores, and send 400
> people home, etc, etc, while I fix this thing. So, any info here is
> appreciated.
> Second question I had was: with the /PAE option, and awe enabled on the
> SQL Server, it consumed as much of the available memory as it could
> (about 19.7 GB). This in itself doesn't bother me, but what had me
> puzzled, was that it appeared to be using the memory that was below the
> 4GB line. I really expected that it wouldn't since I hadn't used the
> /3GB switch. Any thoughts on this? Is /3GB not needed anymore, or is
> my understanding not in line with reality?
> Thanks again for your insights.
> Larry|||thanks, appreciate your response.
"TJ" wrote:

> Calvin,
> Enabling /PAE will enable Windows address more than 4gb... good way of ma
king use of available RAM.
> /3gb switch will make 3gb of virtual memory address space available to Sql
server for Buffer cache.
> Turn on AWE for data-caching on your available physical memory.
> All these are ways of allocating more memory for sql server use.
> http://support.microsoft.com/defaul...b;en-us;q321363
> http://msdn.microsoft.com/library/d... />
a_6b3k.asp
>
> Also refer to this earlier post below:
>
> Definitely apply the hotfix mentioned for article 834628. Another DBA h
ere
> ran into something that he thinks may have been caused by this issue.
> Don't use /3GB when you have more than 12 GB of RAM in your system. The
OS
> needs the extra mamory to handle mapping AWE memory back into the lower
4GB
> address space. Finally, set the MAX SERVER MEMORY to about 2-4GB less t
han
> your physical memory.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Larry Kippenhan" <lwkthrowaway1@.comcast.net> wrote in message
> news:e$pzBY9rEHA.2128@.TK2MSFTNGP10.phx.gbl...
>

/pae /3gb switch

Hello,
I have a pair of clustered sql server with 12GB of memory in each. What do
you recommend in allocating memory space? I was told to add a /3gb and /pae
switch is boot.ini. please let me know if this is best practice.
thanks!
--
Calvin
doc@.ebay.comsorry... forgot some details. both boxes are W2K3 enterprise with sql 2000
enterprise.
"Calvin Do" wrote:
> Hello,
> I have a pair of clustered sql server with 12GB of memory in each. What do
> you recommend in allocating memory space? I was told to add a /3gb and /pae
> switch is boot.ini. please let me know if this is best practice.
>
> thanks!
> --
> Calvin
> doc@.ebay.com|||This is a multi-part message in MIME format.
--=_NextPart_000_00DA_01C4AFDA.4AD438D0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Calvin,
Enabling /PAE will enable Windows address more than 4gb... good way of =making use of available RAM.
/3gb switch will make 3gb of virtual memory address space available to =Sql server for Buffer cache.
Turn on AWE for data-caching on your available physical memory.
All these are ways of allocating more memory for sql server use.
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;q321363
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/archit=
ec/8_ar_sa_6b3k.asp
Also refer to this earlier post below:
Definitely apply the hotfix mentioned for article 834628. Another DBA =here
ran into something that he thinks may have been caused by this issue.
Don't use /3GB when you have more than 12 GB of RAM in your system. =The OS
needs the extra mamory to handle mapping AWE memory back into the =lower 4GB
address space. Finally, set the MAX SERVER MEMORY to about 2-4GB less =than
your physical memory.
-- Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Larry Kippenhan" <lwkthrowaway1@.comcast.net> wrote in message
news:e$pzBY9rEHA.2128@.TK2MSFTNGP10.phx.gbl...
> I have a couple of questions that I hope y'all will be able to give =me
> some info on.
>
> I am installing SQL Server 2000 on a Win2K3 Enterprise server with =20GB
> of RAM (I know, odd number), anyway, as I was searching for =information
> on enabling all of my memory, and the /PAE option, I came across a =KB
> article, #834628
> (http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;834628), =titled
> "Data is corrupted when PAE is enabled on a Windows Server =2003-based
> computer". I expect this machine to give the resources a pretty =good
> workout, and I was concerned about running into this issue. Has any =of
> you run into this issue? Or, how likely is it that I will =experience
> this. Microsoft suggests not installing the hotfix unless you are
> severely affected by this bug. To me, ANY corruption counts as =severe,
> and I really don't want to have to do all of the restores, and send =400
> people home, etc, etc, while I fix this thing. So, any info here is
> appreciated.
>
> Second question I had was: with the /PAE option, and awe enabled on =the
> SQL Server, it consumed as much of the available memory as it could
> (about 19.7 GB). This in itself doesn't bother me, but what had me
> puzzled, was that it appeared to be using the memory that was below =the
> 4GB line. I really expected that it wouldn't since I hadn't used =the
> /3GB switch. Any thoughts on this? Is /3GB not needed anymore, or =is
> my understanding not in line with reality?
>
> Thanks again for your insights.
>
> Larry
--=_NextPart_000_00DA_01C4AFDA.4AD438D0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Calvin,
Enabling /PAE will enable Windows =address more than 4gb... good way of making use of available RAM.
/3gb switch will make 3gb of virtual =memory address space available to Sql server for Buffer cache.
Turn on AWE for data-caching on your =available physical memory.
All these are ways of allocating more =memory for sql server use.

http://msdn.microsoft.com/library/default.as=p?url=3D/library/en-us/architec/8_ar_sa_6b3k.asp
Also refer to this earlier post below:
Definitely apply the hotfix mentioned for article 834628. Another DBA =hereran into something that he thinks may have been caused by this =issue.Don't use /3GB when you have more than 12 GB of RAM in your system. =The OSneeds the extra mamory to handle mapping AWE memory back into =the lower 4GBaddress space. Finally, set the MAX SERVER MEMORY to =about 2-4GB less thanyour physical memory.-- Geoff N. =HitenMicrosoft SQL Server MVPSenior Database =AdministratorCareerbuilder.comI support the Professional Association for SQL Serverhttp://www.sqlpass.org">www.sqlpass.org"Larry =Kippenhan" I have a couple of questions that I hope y'all will be able to give =me> some info on.>> I am installing SQL Server 2000 on a =Win2K3 Enterprise server with 20GB> of RAM (I know, odd number), =anyway, as I was searching for information> on enabling all of my memory, =and the /PAE option, I came across a KB> article, #834628> (http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;834628), titled> "Data is corrupted when PAE is enabled on a Windows =Server 2003-based> computer". I expect this machine to give the resources a pretty good> workout, and I was concerned about =running into this issue. Has any of> you run into this =issue? Or, how likely is it that I will experience> this. Microsoft =suggests not installing the hotfix unless you are> severely affected by =this bug. To me, ANY corruption counts as severe,> and I =really don't want to have to do all of the restores, and send 400> people =home, etc, etc, while I fix this thing. So, any info here is> appreciated.>> Second question I had was: with the /PAE =option, and awe enabled on the> SQL Server, it consumed as much of the available memory as it could> (about 19.7 GB). This in =itself doesn't bother me, but what had me> puzzled, was that it =appeared to be using the memory that was below the> 4GB line. I really =expected that it wouldn't since I hadn't used the> /3GB switch. =Any thoughts on this? Is /3GB not needed anymore, or is> my understanding not in line with reality?>> Thanks again =for your insights.>> Larry

--=_NextPart_000_00DA_01C4AFDA.4AD438D0--|||thanks, appreciate your response.
"TJ" wrote:
> Calvin,
> Enabling /PAE will enable Windows address more than 4gb... good way of making use of available RAM.
> /3gb switch will make 3gb of virtual memory address space available to Sql server for Buffer cache.
> Turn on AWE for data-caching on your available physical memory.
> All these are ways of allocating more memory for sql server use.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_6b3k.asp
>
> Also refer to this earlier post below:
>
> Definitely apply the hotfix mentioned for article 834628. Another DBA here
> ran into something that he thinks may have been caused by this issue.
> Don't use /3GB when you have more than 12 GB of RAM in your system. The OS
> needs the extra mamory to handle mapping AWE memory back into the lower 4GB
> address space. Finally, set the MAX SERVER MEMORY to about 2-4GB less than
> your physical memory.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Larry Kippenhan" <lwkthrowaway1@.comcast.net> wrote in message
> news:e$pzBY9rEHA.2128@.TK2MSFTNGP10.phx.gbl...
> > I have a couple of questions that I hope y'all will be able to give me
> > some info on.
> >
> > I am installing SQL Server 2000 on a Win2K3 Enterprise server with 20GB
> > of RAM (I know, odd number), anyway, as I was searching for information
> > on enabling all of my memory, and the /PAE option, I came across a KB
> > article, #834628
> > (http://support.microsoft.com/default.aspx?scid=kb;en-us;834628), titled
> > "Data is corrupted when PAE is enabled on a Windows Server 2003-based
> > computer". I expect this machine to give the resources a pretty good
> > workout, and I was concerned about running into this issue. Has any of
> > you run into this issue? Or, how likely is it that I will experience
> > this. Microsoft suggests not installing the hotfix unless you are
> > severely affected by this bug. To me, ANY corruption counts as severe,
> > and I really don't want to have to do all of the restores, and send 400
> > people home, etc, etc, while I fix this thing. So, any info here is
> > appreciated.
> >
> > Second question I had was: with the /PAE option, and awe enabled on the
> > SQL Server, it consumed as much of the available memory as it could
> > (about 19.7 GB). This in itself doesn't bother me, but what had me
> > puzzled, was that it appeared to be using the memory that was below the
> > 4GB line. I really expected that it wouldn't since I hadn't used the
> > /3GB switch. Any thoughts on this? Is /3GB not needed anymore, or is
> > my understanding not in line with reality?
> >
> > Thanks again for your insights.
> >
> > Larry
>

/3GB SQL Server 2000

Hi,
I run SQL Server 2000 on Windows 2000 Server (4 GB memory) with 2
instances.
As I understand it, one instance can use a maximum of 2 GB of memory. If I
would use the /3GB switch in this environment, would that help me or is it
that this switch can only be used with the Advanced or Enterprise Edition
of SQL Server 2000 /w2K?
TIA
WombelYou need to run Windows 2000 Advanced Server or Data Center editions in
order to use the /3GB switch. See
http://support.microsoft.com/defaul...750&Product=sql
for more information.
Assuming you want both instances to use an equal amount of memory, you
wouldn't want to use /3GB anyway with only 4GB physical RAM. It's important
to have enough physical RAM to support the SQL Server memory requirements of
all instances running on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Wrobel" <twrobel@.waslos.de> wrote in message
news:Xns94C3739FDCBEBtwrobelwaslosde@.207
.46.248.16...
> Hi,
> I run SQL Server 2000 on Windows 2000 Server (4 GB memory) with 2
> instances.
> As I understand it, one instance can use a maximum of 2 GB of memory. If I
> would use the /3GB switch in this environment, would that help me or is it
> that this switch can only be used with the Advanced or Enterprise Edition
> of SQL Server 2000 /w2K?
> TIA
> Wombel|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in
news:OrPWjb9GEHA.684@.tk2msftngp13.phx.gbl:

> You need to run Windows 2000 Advanced Server or Data Center editions
> in order to use the /3GB switch. See
> http://support.microsoft.com/defaul...274750&Product=
> sql for more information.
> Assuming you want both instances to use an equal amount of memory, you
> wouldn't want to use /3GB anyway with only 4GB physical RAM. It's
> important to have enough physical RAM to support the SQL Server memory
> requirements of all instances running on the server.
>
Dan,
thanks for your reply. Do you know if there is any difference when I user
Windows 2003 Server Standard?
TIA
Thomas|||> thanks for your reply. Do you know if there is any difference when I user
> Windows 2003 Server Standard?
As stated in MSKB 274750, you can use the /3GB boot.ini switch with Windows
2000 Server (Standard Edition).
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Wrobel" <twrobel@.waslos.de> wrote in message
news:Xns94C39FB4C54Etwrobelwaslosde@.207.46.248.16...
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in
> news:OrPWjb9GEHA.684@.tk2msftngp13.phx.gbl:
>
> Dan,
> thanks for your reply. Do you know if there is any difference when I user
> Windows 2003 Server Standard?
> TIA
> Thomassql

/3GB not working

Hi,

I am on a box with 4 GB of memory that only runs SQL Server. SQL Server is using 1.7 GB as per task manager. There are a lot of databases on this box. I looked at the boot.ini and /3GB was not set. I have done this at least 50 times before: turn on the /3GB switch and SQL Server will use up to 2.7 GB per task manager. (I know it is actually 2GB and 3GB but it seems that in task manager it shows up as 1.7-1.8 and 2.7-2.8)

Anyway back to the story. So I set /3GB, rebooted the server and SQL Server still only consumes up to 1.7GB. I realize that SQL Server might not actually need more memory, but I have a distinct feeling that it wants more but is being constrained; that is, the the /3GB switch is not working. More relevant information:

1) the boot.ini line:
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /3GB

2) max server memory (MB) = 3000

3) min server memory (MB) = 3000

4) awe enabled = 0

5) @.@.version = 8.00.2039 which is SQL Server 2000 SP4

Has anyone seen this before? Any ideas on how to troubleshoot this?Just figured out the problem: This server is using SQL Server 2000 Standard Edition. For the benefit of all future readers of this thread, the maximum amount of memory that SQL Server 2000 Standard Edition and SQL Server 2000 Personal Edition can ever use is 2GB. SQL Server 2000 Developer's Edition and SQL Server 2000 Enterprise Edition will both go higher. The actual amount of memory is dependent on the operating system.

Monday, March 19, 2012

/3gb memory switch

The properties of sql server (EE 2000) show that the
instance is set to dynamically allocate 0 - 3895 MB.
There are 4gb of ram on the server. However, the /3gb
memory switch is not set in the boot.ini file. It looks
like that this is what is happening -- sql server
recognizes all of the memory in the server, but the OS
prevents user processes from utilizing more than 2 GB. Is
this as expected? I'm getting ready to set the /3gb
switch and need a little reassurance. It is also my
understanding, that AWE does not need to be enabled when
dealing with a server with only 4GB of RAM. The only
change I need to make is the /3gb switch in the boot.ini
file -- can someone confirm?You are correct. /3B switch will allow the SQL process to use 3GB of user
memory instead of 2GB user memory.
AWE is for servers with greater than 4GB of physical memory.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:f92301c3f197$e266ced0$a501280a@.phx.gbl...
> The properties of sql server (EE 2000) show that the
> instance is set to dynamically allocate 0 - 3895 MB.
> There are 4gb of ram on the server. However, the /3gb
> memory switch is not set in the boot.ini file. It looks
> like that this is what is happening -- sql server
> recognizes all of the memory in the server, but the OS
> prevents user processes from utilizing more than 2 GB. Is
> this as expected? I'm getting ready to set the /3gb
> switch and need a little reassurance. It is also my
> understanding, that AWE does not need to be enabled when
> dealing with a server with only 4GB of RAM. The only
> change I need to make is the /3gb switch in the boot.ini
> file -- can someone confirm?

/3gb memory switch

The properties of sql server (EE 2000) show that the
instance is set to dynamically allocate 0 - 3895 MB.
There are 4gb of ram on the server. However, the /3gb
memory switch is not set in the boot.ini file. It looks
like that this is what is happening -- sql server
recognizes all of the memory in the server, but the OS
prevents user processes from utilizing more than 2 GB. Is
this as expected? I'm getting ready to set the /3gb
switch and need a little reassurance. It is also my
understanding, that AWE does not need to be enabled when
dealing with a server with only 4GB of RAM. The only
change I need to make is the /3gb switch in the boot.ini
file -- can someone confirm?You are correct. /3B switch will allow the SQL process to use 3GB of user
memory instead of 2GB user memory.
AWE is for servers with greater than 4GB of physical memory.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:f92301c3f197$e266ced0$a501280a@.phx.gbl...
> The properties of sql server (EE 2000) show that the
> instance is set to dynamically allocate 0 - 3895 MB.
> There are 4gb of ram on the server. However, the /3gb
> memory switch is not set in the boot.ini file. It looks
> like that this is what is happening -- sql server
> recognizes all of the memory in the server, but the OS
> prevents user processes from utilizing more than 2 GB. Is
> this as expected? I'm getting ready to set the /3gb
> switch and need a little reassurance. It is also my
> understanding, that AWE does not need to be enabled when
> dealing with a server with only 4GB of RAM. The only
> change I need to make is the /3gb switch in the boot.ini
> file -- can someone confirm?

/3GB and Maximum server memory

Also,
you really should set the limit for this to about 2.5-3GB to keep from
starving the OS and other necessary items of RAM.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:u5h1fb6mIHA.3636@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a 32-bit SQL Server 2005 Enterprise Edition with 4GB of memory. I
> activated the /3GB switch in the boot.ini, but I still can't setthe
> Maximum server memory any higher than 2147483647. What should I do else? I
> presume I should be able to set it until 3 gigabytes now?
>
> Any help will be really appreciated,
>
> Thansk a lot in advance,
>
> Pieter
> This is my boot.ini:
> [boot loader]
> timeout=30
> default=multi(0)disk(0)rdisk(0)partition(1)\WINDOW S
> [operating systems]
> multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windo ws Server 2003,
> Standard" /noexecute=optout /fastdetect /3GB
>
|||Hi,
Indeed: In the mean time (when looking at the Task Manager) SQL Server is
using allmost 3 gigabytes, which is perfect!
Regarding the 2147483647: when you right click on the Server in the SQL
Server Management Studio, choose properties, and than Memory, you see the
Maximum server memory. But I guess it has nothing to do with it :-)
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:8DEA94EC-ACD9-4C2F-8C93-FE8B24FF7F94@.microsoft.com...[vbcol=seagreen]
> With /3GB switch alone, you should be able to see SQLServer:Buffer
> Manager\Total Pages or SQLServer:Memory Manager\Total Server Memory
> (KB)--both measuring the buffer pool--reach more than 2GB. But you
> probably
> won't see the buffer pool reach 3GB. Maybe, you didn't drive the SQL
> instance
> hard enough for it to use more than what you reported.
> See
> http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx
> for an exmaple.
> BTW, how did you get the 2147483647 number?
> Linchi
> "Pieter" wrote:

/3GB and Maximum server memory

Hi,
I have a 32-bit SQL Server 2005 Enterprise Edition with 4GB of memory. I
activated the /3GB switch in the boot.ini, but I still can't setthe Maximum
server memory any higher than 2147483647. What should I do else? I presume I
should be able to set it until 3 gigabytes now?
Any help will be really appreciated,
Thansk a lot in advance,
Pieter
This is my boot.ini:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard"
/noexecute=optout /fastdetect /3GBWith /3GB switch alone, you should be able to see SQLServer:Buffer
Manager\Total Pages or SQLServer:Memory Manager\Total Server Memory
(KB)--both measuring the buffer pool--reach more than 2GB. But you probably
won't see the buffer pool reach 3GB. Maybe, you didn't drive the SQL instance
hard enough for it to use more than what you reported.
See
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx for an exmaple.
BTW, how did you get the 2147483647 number?
Linchi
"Pieter" wrote:
> Hi,
> I have a 32-bit SQL Server 2005 Enterprise Edition with 4GB of memory. I
> activated the /3GB switch in the boot.ini, but I still can't setthe Maximum
> server memory any higher than 2147483647. What should I do else? I presume I
> should be able to set it until 3 gigabytes now?
>
> Any help will be really appreciated,
>
> Thansk a lot in advance,
>
> Pieter
> This is my boot.ini:
> [boot loader]
> timeout=30
> default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
> [operating systems]
> multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard"
> /noexecute=optout /fastdetect /3GB
>
>|||IIRC, that setting is in MEGABYTES, so it is far more than 4GB. :) Also,
you really should set the limit for this to about 2.5-3GB to keep from
starving the OS and other necessary items of RAM.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:u5h1fb6mIHA.3636@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a 32-bit SQL Server 2005 Enterprise Edition with 4GB of memory. I
> activated the /3GB switch in the boot.ini, but I still can't setthe
> Maximum server memory any higher than 2147483647. What should I do else? I
> presume I should be able to set it until 3 gigabytes now?
>
> Any help will be really appreciated,
>
> Thansk a lot in advance,
>
> Pieter
> This is my boot.ini:
> [boot loader]
> timeout=30
> default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
> [operating systems]
> multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> Standard" /noexecute=optout /fastdetect /3GB
>|||Hi,
Indeed: In the mean time (when looking at the Task Manager) SQL Server is
using allmost 3 gigabytes, which is perfect!
Regarding the 2147483647: when you right click on the Server in the SQL
Server Management Studio, choose properties, and than Memory, you see the
Maximum server memory. But I guess it has nothing to do with it :-)
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:8DEA94EC-ACD9-4C2F-8C93-FE8B24FF7F94@.microsoft.com...
> With /3GB switch alone, you should be able to see SQLServer:Buffer
> Manager\Total Pages or SQLServer:Memory Manager\Total Server Memory
> (KB)--both measuring the buffer pool--reach more than 2GB. But you
> probably
> won't see the buffer pool reach 3GB. Maybe, you didn't drive the SQL
> instance
> hard enough for it to use more than what you reported.
> See
> http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx
> for an exmaple.
> BTW, how did you get the 2147483647 number?
> Linchi
> "Pieter" wrote:
>> Hi,
>> I have a 32-bit SQL Server 2005 Enterprise Edition with 4GB of memory. I
>> activated the /3GB switch in the boot.ini, but I still can't setthe
>> Maximum
>> server memory any higher than 2147483647. What should I do else? I
>> presume I
>> should be able to set it until 3 gigabytes now?
>>
>> Any help will be really appreciated,
>>
>> Thansk a lot in advance,
>>
>> Pieter
>> This is my boot.ini:
>> [boot loader]
>> timeout=30
>> default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
>> [operating systems]
>> multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
>> Standard"
>> /noexecute=optout /fastdetect /3GB
>>

/3G question

We have some boxes with Sql 2000 sp4 on windows 2003 enterprise edition. 8G
total physical memory, boot.ini options with /PAE but without /3G. SQL AWE
enabled, and max server memeory configured to be 7G. From Task manager,
memory utilization is 7.2G. From perf mon, sql target mem is 7G and total
server memory both show 7G.
My question is, without /3G, how could SQL use more than 6G of memory? Am I
missing something here? and most importantly, is there any performance
implications configuring SQL to use 7G without /3G boot option?
Thanks very much.
Brian
AWE enables SQL Server (or any properly enabled app) to see up to 64GB of
memory with the proper OS and SQL Editions. The /3GB has nothing to do with
AWE (other than you should turn it off at 12GB or higher to ensure enough
PTEs). It only allows the app to see 3GB instead of 2GB of directly
addressable memory.
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
"Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
news:2DB937E4-6853-4EFB-B1F3-C23012F70734@.microsoft.com...
> We have some boxes with Sql 2000 sp4 on windows 2003 enterprise edition.
> 8G
> total physical memory, boot.ini options with /PAE but without /3G. SQL AWE
> enabled, and max server memeory configured to be 7G. From Task manager,
> memory utilization is 7.2G. From perf mon, sql target mem is 7G and total
> server memory both show 7G.
> My question is, without /3G, how could SQL use more than 6G of memory? Am
> I
> missing something here? and most importantly, is there any performance
> implications configuring SQL to use 7G without /3G boot option?
> Thanks very much.
> --
> Brian
|||Hi Brian
I'm not sure where you get the 6GB figure from. Without AWE, SQL Server is
limited to either 2GB or 3GB. With AWE, SQL Server can use up to 64GB,
depending on the version and edition. The /3GB switch only controls how
much memory is directly addressable. All memory above 2GB or 3GB must be
accessed through AWE memory mapping.
Direct addressing is slightly faster than going through AWE, so with the 3GB
flag you will get to more memory slightly faster. In addition, only pages
from your data files can use the memory addressed through AWE. All other
memory uses (query plan, user connections, lock memory, etc) has to use
directly addressed memory, so having 3GB enabled will give you up to 50%
more space for those other memory uses. This can be a good thing.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
news:2DB937E4-6853-4EFB-B1F3-C23012F70734@.microsoft.com...
> We have some boxes with Sql 2000 sp4 on windows 2003 enterprise edition.
> 8G
> total physical memory, boot.ini options with /PAE but without /3G. SQL AWE
> enabled, and max server memeory configured to be 7G. From Task manager,
> memory utilization is 7.2G. From perf mon, sql target mem is 7G and total
> server memory both show 7G.
> My question is, without /3G, how could SQL use more than 6G of memory? Am
> I
> missing something here? and most importantly, is there any performance
> implications configuring SQL to use 7G without /3G boot option?
> Thanks very much.
> --
> Brian

/3G question

We have some boxes with Sql 2000 sp4 on windows 2003 enterprise edition. 8G
total physical memory, boot.ini options with /PAE but without /3G. SQL AWE
enabled, and max server memeory configured to be 7G. From Task manager,
memory utilization is 7.2G. From perf mon, sql target mem is 7G and total
server memory both show 7G.
My question is, without /3G, how could SQL use more than 6G of memory? Am I
missing something here? and most importantly, is there any performance
implications configuring SQL to use 7G without /3G boot option?
Thanks very much.
--
BrianAWE enables SQL Server (or any properly enabled app) to see up to 64GB of
memory with the proper OS and SQL Editions. The /3GB has nothing to do with
AWE (other than you should turn it off at 12GB or higher to ensure enough
PTEs). It only allows the app to see 3GB instead of 2GB of directly
addressable memory.
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
"Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
news:2DB937E4-6853-4EFB-B1F3-C23012F70734@.microsoft.com...
> We have some boxes with Sql 2000 sp4 on windows 2003 enterprise edition.
> 8G
> total physical memory, boot.ini options with /PAE but without /3G. SQL AWE
> enabled, and max server memeory configured to be 7G. From Task manager,
> memory utilization is 7.2G. From perf mon, sql target mem is 7G and total
> server memory both show 7G.
> My question is, without /3G, how could SQL use more than 6G of memory? Am
> I
> missing something here? and most importantly, is there any performance
> implications configuring SQL to use 7G without /3G boot option?
> Thanks very much.
> --
> Brian|||Hi Brian
I'm not sure where you get the 6GB figure from. Without AWE, SQL Server is
limited to either 2GB or 3GB. With AWE, SQL Server can use up to 64GB,
depending on the version and edition. The /3GB switch only controls how
much memory is directly addressable. All memory above 2GB or 3GB must be
accessed through AWE memory mapping.
Direct addressing is slightly faster than going through AWE, so with the 3GB
flag you will get to more memory slightly faster. In addition, only pages
from your data files can use the memory addressed through AWE. All other
memory uses (query plan, user connections, lock memory, etc) has to use
directly addressed memory, so having 3GB enabled will give you up to 50%
more space for those other memory uses. This can be a good thing.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
news:2DB937E4-6853-4EFB-B1F3-C23012F70734@.microsoft.com...
> We have some boxes with Sql 2000 sp4 on windows 2003 enterprise edition.
> 8G
> total physical memory, boot.ini options with /PAE but without /3G. SQL AWE
> enabled, and max server memeory configured to be 7G. From Task manager,
> memory utilization is 7.2G. From perf mon, sql target mem is 7G and total
> server memory both show 7G.
> My question is, without /3G, how could SQL use more than 6G of memory? Am
> I
> missing something here? and most importantly, is there any performance
> implications configuring SQL to use 7G without /3G boot option?
> Thanks very much.
> --
> Brian

/3G question

We have some boxes with Sql 2000 sp4 on windows 2003 enterprise edition. 8G
total physical memory, boot.ini options with /PAE but without /3G. SQL AWE
enabled, and max server memeory configured to be 7G. From Task manager,
memory utilization is 7.2G. From perf mon, sql target mem is 7G and total
server memory both show 7G.
My question is, without /3G, how could SQL use more than 6G of memory? Am I
missing something here? and most importantly, is there any performance
implications configuring SQL to use 7G without /3G boot option?
Thanks very much.
--
BrianAWE enables SQL Server (or any properly enabled app) to see up to 64GB of
memory with the proper OS and SQL Editions. The /3GB has nothing to do with
AWE (other than you should turn it off at 12GB or higher to ensure enough
PTEs). It only allows the app to see 3GB instead of 2GB of directly
addressable memory.
http://sqljunkies.com/Tutorial/0D4F...F2FE2D58F6.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
"Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
news:2DB937E4-6853-4EFB-B1F3-C23012F70734@.microsoft.com...
> We have some boxes with Sql 2000 sp4 on windows 2003 enterprise edition.
> 8G
> total physical memory, boot.ini options with /PAE but without /3G. SQL AWE
> enabled, and max server memeory configured to be 7G. From Task manager,
> memory utilization is 7.2G. From perf mon, sql target mem is 7G and total
> server memory both show 7G.
> My question is, without /3G, how could SQL use more than 6G of memory? Am
> I
> missing something here? and most importantly, is there any performance
> implications configuring SQL to use 7G without /3G boot option?
> Thanks very much.
> --
> Brian|||Hi Brian
I'm not sure where you get the 6GB figure from. Without AWE, SQL Server is
limited to either 2GB or 3GB. With AWE, SQL Server can use up to 64GB,
depending on the version and edition. The /3GB switch only controls how
much memory is directly addressable. All memory above 2GB or 3GB must be
accessed through AWE memory mapping.
Direct addressing is slightly faster than going through AWE, so with the 3GB
flag you will get to more memory slightly faster. In addition, only pages
from your data files can use the memory addressed through AWE. All other
memory uses (query plan, user connections, lock memory, etc) has to use
directly addressed memory, so having 3GB enabled will give you up to 50%
more space for those other memory uses. This can be a good thing.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
news:2DB937E4-6853-4EFB-B1F3-C23012F70734@.microsoft.com...
> We have some boxes with Sql 2000 sp4 on windows 2003 enterprise edition.
> 8G
> total physical memory, boot.ini options with /PAE but without /3G. SQL AWE
> enabled, and max server memeory configured to be 7G. From Task manager,
> memory utilization is 7.2G. From perf mon, sql target mem is 7G and total
> server memory both show 7G.
> My question is, without /3G, how could SQL use more than 6G of memory? Am
> I
> missing something here? and most importantly, is there any performance
> implications configuring SQL to use 7G without /3G boot option?
> Thanks very much.
> --
> Brian

Thursday, March 8, 2012

.NET Framework execution was aborted by escalation policy because of out of memory

Hi,

I have a SQL CLR used to read a XML file, it works fine when the file is smaller, but will give me this error if the file if bigger i.e. more than 10M.

Msg 6532, Level 16, State 49, Line 2

.NET Framework execution was aborted by escalation policy because of out of memory.

System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

System.InvalidOperationException:

at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)

at System.Data.SqlServer.Internal.ClrLevelContext.GetDatabase(SmiEventSink sink, Int32* pcbLen, IntPtr* ppwsName)

at Microsoft.SqlServer.Server.InProcConnection.GetCurrentDatabase(SmiEventSink eventSink)

at System.Data.SqlClient.SqlInternalConnectionSmi.Activate()

at System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString options, Object providerInfo, DbConnection owningConnection)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at BuildDirect.SQLCLR.CLRErrorHandler.RaiseNewCLRError(Int32 errorid, String errormsg)

at BuildDirect.SQLCLR.CLRErrorHandler.ReportCLRError(String location, String errormsg)

at BuildDirect.SQLCLR.CLRErrorHandler.ReportCLRError(String location, Exception ex)

at BuildDirect.SQLCLR.HTTPRequest.uCLRGetHTTPResponse(String url)

How to prevent this error and solve this problem?

Thanks

This blog post should answer all your questions: http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx. If it doesn't, please feel free to ask them back.