Lock Pages in Memory

My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
Lock Pages in Memory setting is ignored.
We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
configuration). The only other application sharing SQL Server resources is
CLR.
With the Lock Pages in Memory setting ignored, does this mean that the 14GB
dedicated to SQL Server is fair game, to be taken as desired by other
processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Still hoping for an answer.
We had a server running 32bit with 2 GB RAM, converted it to 64bit with 16GB
RAM (14GB Max Server Memory) and in less than a week we are getting the
following error in our error log:
"A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
117484, committed (KB): 11856700, memory utilization: 0%."
We have not made any modifications to MemToLeave, so it is at the default
(256MB?). But from what I understand, 64bit gives you a huge extended memory.
So we have not done anything to MemToLeave.
Why do we have memory issues with 16GB RAM, 64bit, but did not have memory
issues on 2GB with 32 bit?
cbrichards wrote:
>My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
>Lock Pages in Memory setting is ignored.
>We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
>configuration). The only other application sharing SQL Server resources is
>CLR.
>With the Lock Pages in Memory setting ignored, does this mean that the 14GB
>dedicated to SQL Server is fair game, to be taken as desired by other
>processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||Hello there!
Have you read the following document?
http://support.microsoft.com/default.aspx/kb/918483
Ekrem Ã?nsoy
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7925eb5963cda@.uwe...
> My understanding is that running 64 bit SQL Server 2005 Standard Edition,
> the
> Lock Pages in Memory setting is ignored.
> We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server
> Memory
> configuration). The only other application sharing SQL Server resources is
> CLR.
> With the Lock Pages in Memory setting ignored, does this mean that the
> 14GB
> dedicated to SQL Server is fair game, to be taken as desired by other
> processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||I highly recommend you read the KB that was posted by Ekrem. I don't
believe Std Edition supports Lock Pages in Memory but you have to understand
there is a huge difference in how memory is utilized between 32 and 64 bit
and even SQL2000 and 2005. These messages are not related to the MemToLeave
area. That memory is pre-allocated and will not be paged out. But unlike in
32 bit the 64 bit SQL Server can utilize all of the memory for things
normally confined to the 2 or 3GB area in a 32 env and can use considerably
more memory than before. For instance the procedure cache would have been
limited to less than 2GB on your old server. Now it can grow at times to as
much as 75% ( exact amount changes with service packs and current conditions
and this is peak, usually won't be more than 50%) of total available memory.
This memory is all dynamic by default and if windows needs more it can call
for memory from SQL Server which will give you the messages you are seeing.
My guess is that you have a lot of adhoc SQL and the proc cache is huge due
to all the non-reusable plans. And or you have operations other than SQL
Server itself running on the server such as SSIS, CLR, Term Services,
Winzip, Notepad etc.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7929227ce194c@.uwe...
> Still hoping for an answer.
> We had a server running 32bit with 2 GB RAM, converted it to 64bit with
> 16GB
> RAM (14GB Max Server Memory) and in less than a week we are getting the
> following error in our error log:
> "A significant part of sql server process memory has been paged out. This
> may
> result in a performance degradation. Duration: 0 seconds. Working set
> (KB):
> 117484, committed (KB): 11856700, memory utilization: 0%."
> We have not made any modifications to MemToLeave, so it is at the default
> (256MB?). But from what I understand, 64bit gives you a huge extended
> memory.
> So we have not done anything to MemToLeave.
> Why do we have memory issues with 16GB RAM, 64bit, but did not have memory
> issues on 2GB with 32 bit?
> cbrichards wrote:
>>My understanding is that running 64 bit SQL Server 2005 Standard Edition,
>>the
>>Lock Pages in Memory setting is ignored.
>>We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server
>>Memory
>>configuration). The only other application sharing SQL Server resources is
>>CLR.
>>With the Lock Pages in Memory setting ignored, does this mean that the
>>14GB
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via http://www.sqlmonster.com
>|||Well, Hello Ekrem!
I have read that document, and that applies to SQL Server 2005 64 bit
Enterprise Edition. In SQL Server 2005 64 bit Standard Edition (which I noted
in my post, is what we run), that Lock Pages in Memory is ignored.
So, I am still, seeking for answers...
Ekrem Ã?nsoy wrote:
>Hello there!
>Have you read the following document?
>http://support.microsoft.com/default.aspx/kb/918483
>> My understanding is that running 64 bit SQL Server 2005 Standard Edition,
>> the
>[quoted text clipped - 9 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||So now the question is, if I cannot lock pages in memory, then is my only
choice to upgrade to Enterprise edition?
cbrichards wrote:
>Well, Hello Ekrem!
>I have read that document, and that applies to SQL Server 2005 64 bit
>Enterprise Edition. In SQL Server 2005 64 bit Standard Edition (which I noted
>in my post, is what we run), that Lock Pages in Memory is ignored.
>So, I am still, seeking for answers...
>>Hello there!
>[quoted text clipped - 6 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||Thanks Andrew for the information.
From what you wrote, I believe our server ran out of memory due to an export
of data from a 250,000,000 row table. This table holds 90 days worth of
archive data and the "adhoc" query was selecting 1 day to export.
I am still perplexed, because this same archive table was (a week ago) on a
32 bit server with 2 GB RAM, and nothing like this ever happened (running out
of memory). The volume of data, number of stored procedures, user connections,
etc., etc. are all the same as when it was on a 32 bit server. One month ago
I performed this same export when it was 32 bit, and while the CPU escalated,
the server did not crater.
I am having difficulty wrapping my arms around the fact that the server did
not crater under 32 bit and 2 GB RAM, but the same operation cratered 64 bit
with 16 GB RAM.
The following came from running DBCC MemoryStatus:
Procedure Cache Value
-- --
TotalProcs 88
TotalPages 2086
InUsePages 70
Any further insights would be appreciated. We are nervous to move forward
with 64 bit Standard Edition with all these unknowns.
Andrew J. Kelly wrote:
>I highly recommend you read the KB that was posted by Ekrem. I don't
>believe Std Edition supports Lock Pages in Memory but you have to understand
>there is a huge difference in how memory is utilized between 32 and 64 bit
>and even SQL2000 and 2005. These messages are not related to the MemToLeave
>area. That memory is pre-allocated and will not be paged out. But unlike in
>32 bit the 64 bit SQL Server can utilize all of the memory for things
>normally confined to the 2 or 3GB area in a 32 env and can use considerably
>more memory than before. For instance the procedure cache would have been
>limited to less than 2GB on your old server. Now it can grow at times to as
>much as 75% ( exact amount changes with service packs and current conditions
>and this is peak, usually won't be more than 50%) of total available memory.
>This memory is all dynamic by default and if windows needs more it can call
>for memory from SQL Server which will give you the messages you are seeing.
>My guess is that you have a lot of adhoc SQL and the proc cache is huge due
>to all the non-reusable plans. And or you have operations other than SQL
>Server itself running on the server such as SSIS, CLR, Term Services,
>Winzip, Notepad etc.
>> Still hoping for an answer.
>[quoted text clipped - 30 lines]
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||OK now we are getting somewhere. You keep feeding bits and pieces to us but
it would be great to have a more detailed description of the issue and
circumstances. Exactly how are you doing this data export? Is it SSIS? If
so that explains a lot. SSIS is a totally separate process from SQL Server
and it will use it's own memory space just like another app on the server.
On a 32 bit machine it can only use up to 2GB max but probably a lot less.
On the 64 bit it can use all it wants. Since the memory is dynamic it will
compete with SQL Server for sure if run on the same machine. SSIS like to do
the work totally in memory if at all possible and can use a lot more than
you might expect. If you provide more details we can give a more directed or
intelligent answer. Also if you do something like this routinely it sounds
like you may want to partition the data to suite these needs.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79322e221281d@.uwe...
> Thanks Andrew for the information.
> From what you wrote, I believe our server ran out of memory due to an
> export
> of data from a 250,000,000 row table. This table holds 90 days worth of
> archive data and the "adhoc" query was selecting 1 day to export.
> I am still perplexed, because this same archive table was (a week ago) on
> a
> 32 bit server with 2 GB RAM, and nothing like this ever happened (running
> out
> of memory). The volume of data, number of stored procedures, user
> connections,
> etc., etc. are all the same as when it was on a 32 bit server. One month
> ago
> I performed this same export when it was 32 bit, and while the CPU
> escalated,
> the server did not crater.
> I am having difficulty wrapping my arms around the fact that the server
> did
> not crater under 32 bit and 2 GB RAM, but the same operation cratered 64
> bit
> with 16 GB RAM.
> The following came from running DBCC MemoryStatus:
> Procedure Cache Value
> -- --
> TotalProcs 88
> TotalPages 2086
> InUsePages 70
> Any further insights would be appreciated. We are nervous to move forward
> with 64 bit Standard Edition with all these unknowns.
> Andrew J. Kelly wrote:
>>I highly recommend you read the KB that was posted by Ekrem. I don't
>>believe Std Edition supports Lock Pages in Memory but you have to
>>understand
>>there is a huge difference in how memory is utilized between 32 and 64 bit
>>and even SQL2000 and 2005. These messages are not related to the
>>MemToLeave
>>area. That memory is pre-allocated and will not be paged out. But unlike
>>in
>>32 bit the 64 bit SQL Server can utilize all of the memory for things
>>normally confined to the 2 or 3GB area in a 32 env and can use
>>considerably
>>more memory than before. For instance the procedure cache would have been
>>limited to less than 2GB on your old server. Now it can grow at times to
>>as
>>much as 75% ( exact amount changes with service packs and current
>>conditions
>>and this is peak, usually won't be more than 50%) of total available
>>memory.
>>This memory is all dynamic by default and if windows needs more it can
>>call
>>for memory from SQL Server which will give you the messages you are
>>seeing.
>>My guess is that you have a lot of adhoc SQL and the proc cache is huge
>>due
>>to all the non-reusable plans. And or you have operations other than SQL
>>Server itself running on the server such as SSIS, CLR, Term Services,
>>Winzip, Notepad etc.
>> Still hoping for an answer.
>>[quoted text clipped - 30 lines]
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
> --
> Message posted via http://www.sqlmonster.com
>|||> Is Max Server Memory even needed in this case?
Personally, I'd always set Max Server Memory and Min Server Memory on any
serious instance. Why let the SQL Server process engage in expensive back and
forth memory trading with OS?
Linchi
"cbrichards via SQLMonster.com" wrote:
> My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
> Lock Pages in Memory setting is ignored.
> We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
> configuration). The only other application sharing SQL Server resources is
> CLR.
> With the Lock Pages in Memory setting ignored, does this mean that the 14GB
> dedicated to SQL Server is fair game, to be taken as desired by other
> processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Interesting...
I am using SSIS to perform the export.
This adds more questions:
1. You stated, "On a 32 bit machine it can only use up to 2GB max but
probably a lot less." Why only 2GB and probably a lot less?
2. You stated, "On the 64 bit it can use all it wants. Since the memory is
dynamic..." Is this true even when using Enterprise edition when you can lock
pages in memory?
Andrew J. Kelly wrote:
>OK now we are getting somewhere. You keep feeding bits and pieces to us but
>it would be great to have a more detailed description of the issue and
>circumstances. Exactly how are you doing this data export? Is it SSIS? If
>so that explains a lot. SSIS is a totally separate process from SQL Server
>and it will use it's own memory space just like another app on the server.
>On a 32 bit machine it can only use up to 2GB max but probably a lot less.
>On the 64 bit it can use all it wants. Since the memory is dynamic it will
>compete with SQL Server for sure if run on the same machine. SSIS like to do
>the work totally in memory if at all possible and can use a lot more than
>you might expect. If you provide more details we can give a more directed or
>intelligent answer. Also if you do something like this routinely it sounds
>like you may want to partition the data to suite these needs.
>> Thanks Andrew for the information.
>[quoted text clipped - 65 lines]
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||> This adds more questions:
> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
> probably a lot less." Why only 2GB and probably a lot less?
Well if the system only had 2GB you need memory for other things as well.
But in 32 bit OS by default any app can only use up to 2GB of directly
addressable memory. If you had more than 2GB and the OS was capable of using
PAE and the app was AWE aware it may be able to use more than 2GB. But I
don't think SSIS was AWE aware so on 32 bit I believe it was only able to
use 2GB or 3GB if /3gb was set max. I could be wrong there but I am pretty
sure that is correct.
> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
> dynamic..." Is this true even when using Enterprise edition when you can
> lock
> pages in memory?
No. The purpose of Lock Pages is to prevent something else from stealing or
borrowing those pages once they are allocated. You can achieve a similar
functionality as Linchi mentioned by setting the MIN and MAX to the same
value. Then as long as you start SQL Server, use that much memory and the
memory is available it will keep it once it has used it. But now you have
another potentially serious issue and that is with the memory that is left.
If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the OS
and anything else you run. If you attempt to run that SSIS package again in
this mode you will surely starve the OS of memory and the machine and
everything on it will not be happy. This is one of the main reasons why it
is recommended you run larger SSIS packages on a separate machine.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79367cc8cda07@.uwe...
> Interesting...
> I am using SSIS to perform the export.
> This adds more questions:
> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
> probably a lot less." Why only 2GB and probably a lot less?
> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
> dynamic..." Is this true even when using Enterprise edition when you can
> lock
> pages in memory?
> Andrew J. Kelly wrote:
>>OK now we are getting somewhere. You keep feeding bits and pieces to us
>>but
>>it would be great to have a more detailed description of the issue and
>>circumstances. Exactly how are you doing this data export? Is it SSIS?
>>If
>>so that explains a lot. SSIS is a totally separate process from SQL Server
>>and it will use it's own memory space just like another app on the server.
>>On a 32 bit machine it can only use up to 2GB max but probably a lot less.
>>On the 64 bit it can use all it wants. Since the memory is dynamic it will
>>compete with SQL Server for sure if run on the same machine. SSIS like to
>>do
>>the work totally in memory if at all possible and can use a lot more than
>>you might expect. If you provide more details we can give a more directed
>>or
>>intelligent answer. Also if you do something like this routinely it
>>sounds
>>like you may want to partition the data to suite these needs.
>> Thanks Andrew for the information.
>>[quoted text clipped - 65 lines]
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||> 1. So, in my case, if SSIS were to be run on a separate machine, would it
> not
> equally starve the Memory on the separate machine?
If those are the only two processes on the machine they would live much
better together than with SQL Server and lock pages.
> 2. When running this SSIS export on the same machine, (loosely speaking)
> does
> SSIS first consume the 14GB, then go after the 2GB of the OS?
I don't know the allocations work at that level but I would say there are
factors that would make it a DEPENDS type of answer.
> 3. At what point does the 8TB of virtual address space get used.
Virtual address space is always used but if you mean when does it start
swapping to disk? Then generally when all the physical memory is depleted.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:793df7e614c62@.uwe...
> This raises a few more questions:
> 1. So, in my case, if SSIS were to be run on a separate machine, would it
> not
> equally starve the Memory on the separate machine?
> 2. When running this SSIS export on the same machine, (loosely speaking)
> does
> SSIS first consume the 14GB, then go after the 2GB of the OS?
> 3. At what point does the 8TB of virtual address space get used.
> Andrew J. Kelly wrote:
>> This adds more questions:
>> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
>> probably a lot less." Why only 2GB and probably a lot less?
>>Well if the system only had 2GB you need memory for other things as well.
>>But in 32 bit OS by default any app can only use up to 2GB of directly
>>addressable memory. If you had more than 2GB and the OS was capable of
>>using
>>PAE and the app was AWE aware it may be able to use more than 2GB. But I
>>don't think SSIS was AWE aware so on 32 bit I believe it was only able to
>>use 2GB or 3GB if /3gb was set max. I could be wrong there but I am
>>pretty
>>sure that is correct.
>> 2. You stated, "On the 64 bit it can use all it wants. Since the memory
>> is
>> dynamic..." Is this true even when using Enterprise edition when you can
>> lock
>> pages in memory?
>>No. The purpose of Lock Pages is to prevent something else from stealing
>>or
>>borrowing those pages once they are allocated. You can achieve a similar
>>functionality as Linchi mentioned by setting the MIN and MAX to the same
>>value. Then as long as you start SQL Server, use that much memory and the
>>memory is available it will keep it once it has used it. But now you have
>>another potentially serious issue and that is with the memory that is
>>left.
>>If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the
>>OS
>>and anything else you run. If you attempt to run that SSIS package again
>>in
>>this mode you will surely starve the OS of memory and the machine and
>>everything on it will not be happy. This is one of the main reasons why
>>it
>>is recommended you run larger SSIS packages on a separate machine.
>> Interesting...
>>[quoted text clipped - 32 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
> --
> Message posted via http://www.sqlmonster.com
>|||This raises a few more questions:
1. So, in my case, if SSIS were to be run on a separate machine, would it not
equally starve the Memory on the separate machine?
2. When running this SSIS export on the same machine, (loosely speaking) does
SSIS first consume the 14GB, then go after the 2GB of the OS?
3. At what point does the 8TB of virtual address space get used.
Andrew J. Kelly wrote:
>> This adds more questions:
>> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
>> probably a lot less." Why only 2GB and probably a lot less?
>Well if the system only had 2GB you need memory for other things as well.
>But in 32 bit OS by default any app can only use up to 2GB of directly
>addressable memory. If you had more than 2GB and the OS was capable of using
>PAE and the app was AWE aware it may be able to use more than 2GB. But I
>don't think SSIS was AWE aware so on 32 bit I believe it was only able to
>use 2GB or 3GB if /3gb was set max. I could be wrong there but I am pretty
>sure that is correct.
>> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
>> dynamic..." Is this true even when using Enterprise edition when you can
>> lock
>> pages in memory?
>No. The purpose of Lock Pages is to prevent something else from stealing or
>borrowing those pages once they are allocated. You can achieve a similar
>functionality as Linchi mentioned by setting the MIN and MAX to the same
>value. Then as long as you start SQL Server, use that much memory and the
>memory is available it will keep it once it has used it. But now you have
>another potentially serious issue and that is with the memory that is left.
>If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the OS
>and anything else you run. If you attempt to run that SSIS package again in
>this mode you will surely starve the OS of memory and the machine and
>everything on it will not be happy. This is one of the main reasons why it
>is recommended you run larger SSIS packages on a separate machine.
>> Interesting...
>[quoted text clipped - 32 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
--
Message posted via http://www.sqlmonster.com|||Thanks for all the info, Andrew.
Hopefully, (for your sake) this is my last set of questions. If I set my
Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS, then,
if I execute an expensive adhoc query directly on the server from Management
Studio, and that query consumes the 14GB, is that query able to consume any
of the OS memory?
On the flip side, if my Max Server Memory is set to 14GB and Min Server
Memory is left at its default (0), then with this configuration, the same
adhoc query is executed above, does that mean the query could consume both
the 14GB as well as the 2GB OS?
Andrew J. Kelly wrote:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would it
>> not
>> equally starve the Memory on the separate machine?
>If those are the only two processes on the machine they would live much
>better together than with SQL Server and lock pages.
>> 2. When running this SSIS export on the same machine, (loosely speaking)
>> does
>> SSIS first consume the 14GB, then go after the 2GB of the OS?
>I don't know the allocations work at that level but I would say there are
>factors that would make it a DEPENDS type of answer.
>> 3. At what point does the 8TB of virtual address space get used.
>Virtual address space is always used but if you mean when does it start
>swapping to disk? Then generally when all the physical memory is depleted.
>> This raises a few more questions:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would it
>[quoted text clipped - 49 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||> Hopefully, (for your sake) this is my last set of questions. If I set my
> Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS,
> then,
> if I execute an expensive adhoc query directly on the server from
> Management
> Studio, and that query consumes the 14GB, is that query able to consume
> any
> of the OS memory?
Well the portion of memory that used to be called MemTo Leave still comes
into play as a section of memory pre-reserved at startup and is not part of
the buffer pool. In 2000 that defaulted to about 384MB (128M for the worker
threads). In 2005 the allocations are a little more complicated but lets
just say 384MB comes right off the top. The MAX Memory is for the buffer
pool only. So you can theoriticallyuse 14GB for bufferpool and 384MB for
Contiguous memory. That will leave about 1.7GB for the OS. Having said that
none of the 1.7GB will be used for a query in SQL Server under the
conditions you outlined.
> On the flip side, if my Max Server Memory is set to 14GB and Min Server
> Memory is left at its default (0), then with this configuration, the same
> adhoc query is executed above, does that mean the query could consume both
> the 14GB as well as the 2GB OS?
No, this still follows the exact same rules. The MIN just states that after
SQL Server grabs that memory it will not give it back to the OS.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:793ea42168c08@.uwe...
> Thanks for all the info, Andrew.
> Hopefully, (for your sake) this is my last set of questions. If I set my
> Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS,
> then,
> if I execute an expensive adhoc query directly on the server from
> Management
> Studio, and that query consumes the 14GB, is that query able to consume
> any
> of the OS memory?
> On the flip side, if my Max Server Memory is set to 14GB and Min Server
> Memory is left at its default (0), then with this configuration, the same
> adhoc query is executed above, does that mean the query could consume both
> the 14GB as well as the 2GB OS?
> Andrew J. Kelly wrote:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would
>> it
>> not
>> equally starve the Memory on the separate machine?
>>If those are the only two processes on the machine they would live much
>>better together than with SQL Server and lock pages.
>> 2. When running this SSIS export on the same machine, (loosely speaking)
>> does
>> SSIS first consume the 14GB, then go after the 2GB of the OS?
>>I don't know the allocations work at that level but I would say there are
>>factors that would make it a DEPENDS type of answer.
>> 3. At what point does the 8TB of virtual address space get used.
>>Virtual address space is always used but if you mean when does it start
>>swapping to disk? Then generally when all the physical memory is
>>depleted.
>> This raises a few more questions:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would
>> it
>>[quoted text clipped - 49 lines]
>>>>processes whenever needed? Is Max Server Memory even needed in
>>>>this
>>>>case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks for the advice Linchi.
I am trying to understand the difference between the ability to lock pages in
memory on 64 bit Enterprise Edition and setting the Min/Max Server Memory
setting the same on a 64 bit Standard Edition.
We are running 64 bit Standard Edition, with 16 GB RAM, and have the Min
Server Memory set to zero and Max Server Memory set to 14336.
We are still experiencing a shortage of memory and I believe it is either a
separate application, such as Reporting Services or SSIS that is depleting
our memory.
So, it this were an Enterprise addition box (instead of Standard Edition),
and I had the ability to Lock Pages in Memory, how would this change my
situation?
Or, with my current Standard Edition situation, how would setting the Min and
Max Server Memory to the same amount change my situation?
Linchi Shea wrote:
>> Is Max Server Memory even needed in this case?
>Personally, I'd always set Max Server Memory and Min Server Memory on any
>serious instance. Why let the SQL Server process engage in expensive back and
>forth memory trading with OS?
>Linchi
>> My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
>> Lock Pages in Memory setting is ignored.
>[quoted text clipped - 6 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1sql