July 8, 2010 at 3:16 am
One of our SQL 2005 64 bit servers (running on an ESXi virtual, windows 2003 64 bit) was having issues with the system cache taking the memory away from the sql buffer pool. It was due to a backup overrunning. To combat this we have turned on lock pages in memory and stopped/started the sql services. Have the message in startup "Using locked pages for buffer pool". Thing is - the memory is getting stolen once more!
Surely this should have been enough and I don't also need to turn the system cache off of being large system cache?
Any ideas?
To give an example -
Target server memory at best is 5gb. But once the system cache eats in, it steadily drops and drops. HOWEVER it only goes as low as the current Total Server Memory (doesn't force them both to drop lower than the current total). I think beforehand, the Total Server Memory would then lower too. Is it because the sql server doesn't need it all so the target server memory adjusts, but once the pages are required it will force the system cache out and reclaim the 5gb? If so I can see some serious performance issues on the server when this occurs!
July 8, 2010 at 4:03 am
What is SQL Server edition? Is this a STANDARD edition, because LOCK PAGES IN MEMORY is not supported in this edition hence you are not able to LOCK the pages.
There is a hotfix available for this.
http://support.microsoft.com/kb/970070/
Windows 2003 is aggressive the way is handle cache. Any kind of buffered I/O, behavior can cause the working set in SQL Server to be trimmed. You van view the MS KB on this http://support.microsoft.com/kb/920739/.
How many instances are running on this server if One then is it possible to cap the SQL Server memory with Max and Min server memory.
"More Green More Oxygen !! Plant a tree today"
July 8, 2010 at 4:16 am
No it is Enterprise (I thought I put that somewhere - sorry!)
Will the minimum server memory setting lock it in and prevent it from shrinking?
July 8, 2010 at 4:47 am
Shark Energy (7/8/2010)
Will the minimum server memory setting lock it in and prevent it from shrinking?
Yes , if you are capping it yes, SQL Serevr will not release the memory and also please check the KB which takes about windows 2003 server hotfix.
Let me know if this had helped you 🙂
"More Green More Oxygen !! Plant a tree today"
July 8, 2010 at 4:54 am
I had seen the article before along with various others (that were more relevant for this circumstance).
The whole point though with the Lock Pages in memory for 64 bit enterprise is surely that the buffer pool is protected and does not release memory when under pressure. My results so far since setting it (and with it running as shown in the SQL log) is that the Target Memory does get eating up, but perhaps the Total Memory is protected and if the Total memory at that time is using up the full allocation of 5gb, perhaps the server will not give up its pool and therefore this isn't an issue and lock pages is working. (I'll keep monitoring but if someone has knowledge of lock pages and expected behaviour that'd be great).
Thanks for help so far
July 8, 2010 at 8:13 am
are you getting errors in the SQL log about memory being cached out? we had that until we put in lock pages in memory
July 8, 2010 at 8:19 am
We was getting that error - yep! I haven't checked for the error again, but monitoring the behaviour in perfmon. Will keep an eye on it.
July 8, 2010 at 11:36 pm
If, Total Server Memory (KB) counter is less than the Target Server Memory (KB) counter, then this means that SQL Server has enough memory to run efficiently.
On the other hand, if the Total Server Memory (KB) counter is more or equal than the Target Server Memory (KB) counter, this indicates that SQL Server may be under memory pressure.
"More Green More Oxygen !! Plant a tree today"
July 8, 2010 at 11:44 pm
Check this link out:
To lock pages in memory you have to apply the service packs and hotfixes and set a traceflag on SQL engine startup
Will.
July 9, 2010 at 2:03 am
Minaz Amin (7/8/2010)
If, Total Server Memory (KB) counter is less than the Target Server Memory (KB) counter, then this means that SQL Server has enough memory to run efficiently.On the other hand, if the Total Server Memory (KB) counter is more or equal than the Target Server Memory (KB) counter, this indicates that SQL Server may be under memory pressure.
Yes thats right. What I am trying to work out is, when Locked Pages is ON, does the Target memory still drift up and down as required (therefore if called upon by other resources, and if the memory is not being used on SQL, it WILL free up memory). Or should it now set locked at its maximum?
Its probably working and I've just been caught out by the expected action not being what I expected!!!
WIlliam, thanks for the link, but I'm guessing that is a fix for standard edition.
Cheers,
Shark
July 9, 2010 at 2:11 am
Before -
System cache required for windows process. Eats into SQL server Target memory (total memory follows with it) until SQL server has no memory left and the processes and queries running grind to a halt. SQL could not claw back any memory.
After (Locked pages in memory set and min server memory) -
System cache required for windows process. Eats into SQL target memory up to the Min Server memory setting as the total isn't using that much anyway in this case. I kick off a big process on SQL and it now starts to attempt to claw back the memory - but very slowly. If I clear the system cache it starts to use up all the memory available.
So I have 1 check to make - WIthout the min server memory will the System Cache be able to eat up and reduce the sql again (My own answer is no - locked pages is protecting the USED memory/pages and will only free up available memory - and the question above is normal behaviour).
Then I have 1 decision - Do I turn off large system cache for performance. At the moment with it on - if SQL needs to get memory for a big process, it takes longer because it is having to retrieve it from the process using the system cache. If system cache was set at 8mb (or the low default) then it would gather memory far quicker improving performance (although the system process would suffer). Thats one for me to decide.
July 9, 2010 at 5:18 am
You may find this article handy
http://www.sqlservercentral.com/blogs/sqldbauk/archive/2010/06/25/lock-pages-in-memory.aspx
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply