"Mike Wazowski" <bookham_measures_notha***@yahoo.com> wrote in message
news:uVEBvYkUJHA.6092@TK2MSFTNGP04.phx.gbl...
> Hello Experts
>
> We have an active/passive failover cluster running on some decent IBM X
> Series servers, quad x64 Xeons with 16 Gb of RAM.
>
> We upgraded each server to 64Gb.  As the server started eating into the
> new RAM, performance gradually got worse.  When we got to about 50Gb
> consumed we had to stop and start the service due to the number of
> complaints from our users.  The cycle repeated.
>
> There are three databases, two under 1Gb and not used much, and one at
> about 47Gb, used intensively.  Performance had been "ok", with ups and
> downs as and when large reports where running etc.
>
> I panicked when I noticed the AWE checkbox was not ticked in the memory
> properties, then breathed out when I learned this is not required on X64
> due to it's ability to be able to address all the RAM normally.  I did
> learn however about the "lock pages in memory" privilege that _wasn't_
> set-up for the SQL server login accounts, so set that up and restarted SQL
> Server.  (As these servers are in a FO cluster, this is set in Domain
> Policy Editor, not gpedit.msc as M$ state. grpedit does not allow you to
> edit this setting on my servers.)
>
> It made no difference.  I had thought that perhaps there was a paging
> issue and that this would fix it.  I allocated a min of 1Gb and a max of
> 56Gb, leaving 8Gb for anything else.  I checked pages/sec in performance
> monitor and there was nothing doing.
>
> The problem occurred again, after a while, once the server had started
> eating in to some memory, the performance tails right off and the server
> slows right down.
>
> I have now removed the lock pages in memory privilege, and fixed the
> maximum memory at 16Gb, (the amount that was previously installed), and
> touch wood, it seems to be holding up.
>
> Can anyone shed any light on what might be causing this?  Surely we should
> be able to whack the RAM in and let SQL Server eat it's heart out.  You
> can imagine that the RAM was a considerable investment and it's turned out
> to be a complete waste of money, if not worse ... it's damaging our
> business!
>
> Windows Server 2003 R2 Enterprise x64 Edition
> SQL Server 9.00.1399.06 RTM Standard Edition (64-bit)
>
> According to this we don't need Enterprise edition of SQL Server
> (http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx).
>
> The only odd thing I have noticed is that when we were running with 56Gb
> maximum, the counter for current memory was 36 or so and the target memory
> was 59 (GB).  Why would it target more than it was using if more was
> available?
>
> I really hope you can help as I am totally in the dark.  Everything I read
> says how SQL loves memory, you can't have too much memory, how wonderful
> SQL memory management is. For me, it's been a disaster.
>
> Thanks and regards
>
> Mike
>
Hi Mike

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:0BC82A5C-9BE1-491E-BEB8-B8DA6E040D11@microsoft.com...
>
> "Mike Wazowski" <bookham_measures_notha***@yahoo.com> wrote in message
> news:uVEBvYkUJHA.6092@TK2MSFTNGP04.phx.gbl...
>> Hello Experts
>>
>> We have an active/passive failover cluster running on some decent IBM X
>> Series servers, quad x64 Xeons with 16 Gb of RAM.
>>
>> We upgraded each server to 64Gb.  As the server started eating into the
>> new RAM, performance gradually got worse.  When we got to about 50Gb
>> consumed we had to stop and start the service due to the number of
>> complaints from our users.  The cycle repeated.
>>
>> There are three databases, two under 1Gb and not used much, and one at
>> about 47Gb, used intensively.  Performance had been "ok", with ups and
>> downs as and when large reports where running etc.
>>
>> I panicked when I noticed the AWE checkbox was not ticked in the memory
>> properties, then breathed out when I learned this is not required on X64
>> due to it's ability to be able to address all the RAM normally.  I did
>> learn however about the "lock pages in memory" privilege that _wasn't_
>> set-up for the SQL server login accounts, so set that up and restarted
>> SQL Server.  (As these servers are in a FO cluster, this is set in Domain
>> Policy Editor, not gpedit.msc as M$ state. grpedit does not allow you to
>> edit this setting on my servers.)
>>
>> It made no difference.  I had thought that perhaps there was a paging
>> issue and that this would fix it.  I allocated a min of 1Gb and a max of
>> 56Gb, leaving 8Gb for anything else.  I checked pages/sec in performance
>> monitor and there was nothing doing.
>>
>> The problem occurred again, after a while, once the server had started
>> eating in to some memory, the performance tails right off and the server
>> slows right down.
>>
>> I have now removed the lock pages in memory privilege, and fixed the
>> maximum memory at 16Gb, (the amount that was previously installed), and
>> touch wood, it seems to be holding up.
>>
>> Can anyone shed any light on what might be causing this?  Surely we
>> should be able to whack the RAM in and let SQL Server eat it's heart out.
>> You can imagine that the RAM was a considerable investment and it's
>> turned out to be a complete waste of money, if not worse ... it's
>> damaging our business!
>>
>> Windows Server 2003 R2 Enterprise x64 Edition
>> SQL Server 9.00.1399.06 RTM Standard Edition (64-bit)
>>
>> According to this we don't need Enterprise edition of SQL Server
>> (http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx).
>>
>> The only odd thing I have noticed is that when we were running with 56Gb
>> maximum, the counter for current memory was 36 or so and the target
>> memory was 59 (GB).  Why would it target more than it was using if more
>> was available?
>>
>> I really hope you can help as I am totally in the dark.  Everything I
>> read says how SQL loves memory, you can't have too much memory, how
>> wonderful SQL memory management is. For me, it's been a disaster.
>>
>> Thanks and regards
>>
>> Mike
>>
> Hi Mike
>
> According to http://support.microsoft.com/kb/918483 only SQL Server
> Enterprise Edition can use the lock pages in memory, so you could still be
> seeing the working set being paged out.  You may want to look at the
> troubleshooting section and also the possible benefits of upgrading to SP2
> to get the error messages.
>
> John

"Mike Wazowski" <bookham_measures_notha***@yahoo.com> wrote in message
news:uJpifH7UJHA.4744@TK2MSFTNGP05.phx.gbl...
> Hi John
>
> Thank you, it looks like that's the article I need, although as per usual,
> there's no absolute fix - more a case of trying this that and the other
> and hoping for the best.
>
> I'm curious as to why M$ would restrict the use of "lock pages in memory"
> to enterprise edition customers when it's possible that it would make a
> non-working standard edition installation, work properly!
>
> It's strange that this problem does not occur when the max memory is set
> at 16Gb.  Surely the "working set" is being paged all the time.
>
> I am also wondering what the factors are that are causing us to have this
> issue - surely there are millions of other customers with similar
> configurations to us that are not experiencing this problem.
>
> Regards
>
> Mike
>
>
>
> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> news:0BC82A5C-9BE1-491E-BEB8-B8DA6E040D11@microsoft.com...
>>
>> "Mike Wazowski" <bookham_measures_notha***@yahoo.com> wrote in message
>> news:uVEBvYkUJHA.6092@TK2MSFTNGP04.phx.gbl...
>>> Hello Experts
>>>
>>> We have an active/passive failover cluster running on some decent IBM X
>>> Series servers, quad x64 Xeons with 16 Gb of RAM.
>>>
>>> We upgraded each server to 64Gb.  As the server started eating into the
>>> new RAM, performance gradually got worse.  When we got to about 50Gb
>>> consumed we had to stop and start the service due to the number of
>>> complaints from our users.  The cycle repeated.
>>>
>>> There are three databases, two under 1Gb and not used much, and one at
>>> about 47Gb, used intensively.  Performance had been "ok", with ups and
>>> downs as and when large reports where running etc.
>>>
>>> I panicked when I noticed the AWE checkbox was not ticked in the memory
>>> properties, then breathed out when I learned this is not required on X64
>>> due to it's ability to be able to address all the RAM normally.  I did
>>> learn however about the "lock pages in memory" privilege that _wasn't_
>>> set-up for the SQL server login accounts, so set that up and restarted
>>> SQL Server.  (As these servers are in a FO cluster, this is set in
>>> Domain Policy Editor, not gpedit.msc as M$ state. grpedit does not allow
>>> you to edit this setting on my servers.)
>>>
>>> It made no difference.  I had thought that perhaps there was a paging
>>> issue and that this would fix it.  I allocated a min of 1Gb and a max of
>>> 56Gb, leaving 8Gb for anything else.  I checked pages/sec in performance
>>> monitor and there was nothing doing.
>>>
>>> The problem occurred again, after a while, once the server had started
>>> eating in to some memory, the performance tails right off and the server
>>> slows right down.
>>>
>>> I have now removed the lock pages in memory privilege, and fixed the
>>> maximum memory at 16Gb, (the amount that was previously installed), and
>>> touch wood, it seems to be holding up.
>>>
>>> Can anyone shed any light on what might be causing this?  Surely we
>>> should be able to whack the RAM in and let SQL Server eat it's heart
>>> out. You can imagine that the RAM was a considerable investment and it's
>>> turned out to be a complete waste of money, if not worse ... it's
>>> damaging our business!
>>>
>>> Windows Server 2003 R2 Enterprise x64 Edition
>>> SQL Server 9.00.1399.06 RTM Standard Edition (64-bit)
>>>
>>> According to this we don't need Enterprise edition of SQL Server
>>> (http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx).
>>>
>>> The only odd thing I have noticed is that when we were running with 56Gb
>>> maximum, the counter for current memory was 36 or so and the target
>>> memory was 59 (GB).  Why would it target more than it was using if more
>>> was available?
>>>
>>> I really hope you can help as I am totally in the dark.  Everything I
>>> read says how SQL loves memory, you can't have too much memory, how
>>> wonderful SQL memory management is. For me, it's been a disaster.
>>>
>>> Thanks and regards
>>>
>>> Mike
>>>
>> Hi Mike
>>
>> According to http://support.microsoft.com/kb/918483 only SQL Server
>> Enterprise Edition can use the lock pages in memory, so you could still
>> be seeing the working set being paged out.  You may want to look at the
>> troubleshooting section and also the possible benefits of upgrading to
>> SP2 to get the error messages.
>>
>> John
>
Hi Mike

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:AA96AA33-782C-41EA-BF33-5A2CE348CF43@microsoft.com...
>
> "Mike Wazowski" <bookham_measures_notha***@yahoo.com> wrote in message
> news:uJpifH7UJHA.4744@TK2MSFTNGP05.phx.gbl...
>> Hi John
>>
>> Thank you, it looks like that's the article I need, although as per
>> usual, there's no absolute fix - more a case of trying this that and the
>> other and hoping for the best.
>>
>> I'm curious as to why M$ would restrict the use of "lock pages in memory"
>> to enterprise edition customers when it's possible that it would make a
>> non-working standard edition installation, work properly!
>>
>> It's strange that this problem does not occur when the max memory is set
>> at 16Gb.  Surely the "working set" is being paged all the time.
>>
>> I am also wondering what the factors are that are causing us to have this
>> issue - surely there are millions of other customers with similar
>> configurations to us that are not experiencing this problem.
>>
>> Regards
>>
>> Mike
>>
>>
>>
>> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
>> news:0BC82A5C-9BE1-491E-BEB8-B8DA6E040D11@microsoft.com...
>>>
>>> "Mike Wazowski" <bookham_measures_notha***@yahoo.com> wrote in message
>>> news:uVEBvYkUJHA.6092@TK2MSFTNGP04.phx.gbl...
>>>> Hello Experts
>>>>
>>>> We have an active/passive failover cluster running on some decent IBM X
>>>> Series servers, quad x64 Xeons with 16 Gb of RAM.
>>>>
>>>> We upgraded each server to 64Gb.  As the server started eating into the
>>>> new RAM, performance gradually got worse.  When we got to about 50Gb
>>>> consumed we had to stop and start the service due to the number of
>>>> complaints from our users.  The cycle repeated.
>>>>
>>>> There are three databases, two under 1Gb and not used much, and one at
>>>> about 47Gb, used intensively.  Performance had been "ok", with ups and
>>>> downs as and when large reports where running etc.
>>>>
>>>> I panicked when I noticed the AWE checkbox was not ticked in the memory
>>>> properties, then breathed out when I learned this is not required on
>>>> X64 due to it's ability to be able to address all the RAM normally.  I
>>>> did learn however about the "lock pages in memory" privilege that
>>>> _wasn't_ set-up for the SQL server login accounts, so set that up and
>>>> restarted SQL Server.  (As these servers are in a FO cluster, this is
>>>> set in Domain Policy Editor, not gpedit.msc as M$ state. grpedit does
>>>> not allow you to edit this setting on my servers.)
>>>>
>>>> It made no difference.  I had thought that perhaps there was a paging
>>>> issue and that this would fix it.  I allocated a min of 1Gb and a max
>>>> of 56Gb, leaving 8Gb for anything else.  I checked pages/sec in
>>>> performance monitor and there was nothing doing.
>>>>
>>>> The problem occurred again, after a while, once the server had started
>>>> eating in to some memory, the performance tails right off and the
>>>> server slows right down.
>>>>
>>>> I have now removed the lock pages in memory privilege, and fixed the
>>>> maximum memory at 16Gb, (the amount that was previously installed), and
>>>> touch wood, it seems to be holding up.
>>>>
>>>> Can anyone shed any light on what might be causing this?  Surely we
>>>> should be able to whack the RAM in and let SQL Server eat it's heart
>>>> out. You can imagine that the RAM was a considerable investment and
>>>> it's turned out to be a complete waste of money, if not worse ... it's
>>>> damaging our business!
>>>>
>>>> Windows Server 2003 R2 Enterprise x64 Edition
>>>> SQL Server 9.00.1399.06 RTM Standard Edition (64-bit)
>>>>
>>>> According to this we don't need Enterprise edition of SQL Server
>>>> (http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx).
>>>>
>>>> The only odd thing I have noticed is that when we were running with
>>>> 56Gb maximum, the counter for current memory was 36 or so and the
>>>> target memory was 59 (GB).  Why would it target more than it was using
>>>> if more was available?
>>>>
>>>> I really hope you can help as I am totally in the dark.  Everything I
>>>> read says how SQL loves memory, you can't have too much memory, how
>>>> wonderful SQL memory management is. For me, it's been a disaster.
>>>>
>>>> Thanks and regards
>>>>
>>>> Mike
>>>>
>>> Hi Mike
>>>
>>> According to http://support.microsoft.com/kb/918483 only SQL Server
>>> Enterprise Edition can use the lock pages in memory, so you could still
>>> be seeing the working set being paged out.  You may want to look at the
>>> troubleshooting section and also the possible benefits of upgrading to
>>> SP2 to get the error messages.
>>>
>>> John
>>
> Hi Mike
>
> I am not sure there will be that many customers on Standard Edition with
> that amount of memory!
>
> I don't know the algorithm used to determine that the buffer pool should
> be paged. But it may have a factor in the size of memory meaning it is
> less likely to be paged out with lower memory.
>
> The first thing I would do is upgraded to SP2 and make sure you get the
> given errors.
>
> John