Min Max Memory settings.

Question

Min Max Memory settings.

TRACEY-320982

SSChampion

Points: 13653
More actions
June 1, 2009 at 10:00 am

#210317

64 bit SQL 2005 standard SP 2. Changed the min memory to 1024 and max to 6 gig.
Did the lock in memory pages in OS . Shutdown SQL - i do not see any entry in the logs stating this has changed. The memory in task manager for physical is 16,771,724 and available 1,408,864.
Looks to me that the Lock in memory is not working...The available should be at least 6 gig ..?
Any suggestions i just checked it is standard copy not an enterprise copy - does this not work for standard copy of SQL

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

You have to apply CU4 to SP3 for Standard Edition, but even then, I don't think your expectations match what Lock Pages in Memory will do. What exactly are you expecting to have happen here?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

The reference for Lock Pages in Standard Edition is:

http://blogs.msdn.com/psssql/archive/2009/05/19/an-update-for-standard-sku-support-for-locked-pages.aspx

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 3

Thanks for thread...expecting Available MBytes to be increased..during day get down to 98 MBytes.

So fixing min/max and lock in should give SQL more memory to prevent it paging o/s.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Tracey, it looks like this server has 16GB of memory on it. Can you confirm?

If so, what else is installed and running on this server? Because, if you have set the max memory of SQL Server to 6GB, but there is less than 1GB of memory available - something else is using that memory.

Remember, if you are running SSIS packages on this server - those are not run in SQL Server and require memory of their own.

And finally, what is the actual setting you set for max memory? Use sp_configure to display the actual values.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 5

Tracey, it looks like this server has 16GB of memory on it. Can you confirm?

Yes this is correct 16 GIG.

Two instances of SQL - set both min to 1024 an Max to

sp_configure

max server memory (MB)16214748364764426442

min server memory (MB)0214748364710241024

same for both instances

In task manager two copies of sql and both have 6864 values.

Only SQL is supposed to be running on this server. I was under the impression that when the lock in memory is set that there be more memory in the "available memory" bucket......in task manager

commit charge

Total 15282736

limit 41303176

peak 15605508

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

TRACEY (6/1/2009)
I was under the impression that when the lock in memory is set that there be more memory in the "available memory" bucket......in task manager

Not to my best knowledge. All this does is prevent memory set trimming by the OS for the SQL Process space. Keep in mind that Max Server Memory affects the sizing of the Buffer Pool only, it doesn't affect the total memory footprint of SQL Sever because you still have process space that is required for things that use VAS in SQL like CLR, OLE Automation, multi-page allocations, Linked Servers, Extended Stored Procedures, large plans, and connections with a network packet size larger than 8060 bytes. 6GB for the BPool would actually equate to being roughly 6.5-7GB+ of process space depending on the number of schedulers that SQL Server thinks it has, and the number of workers it is configured for. Keep in mind the x64 workers have a 2MB allocation not .5MB like a x86. This doesn't mean to size your workers down, it just makes determining the correct memory setting a trial and error type thing. Monitor your server and slowly adjust down to the optimum size that leaves you with > 150MB Available MB during peak load.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 7

Interesting...so setting the MAX has allowed the data to be stored in buffer cache which is fetched from physical disk to reside in memory (buffer cache) so the next time data is retrieved it is fetched from memory and not physical disk. Bearing in mind this could be swapped out if another big table is required etc. This also helps with keeping the sp/views in procedure cache too...can you confirm or is this set somewhere else. From one of the tools used the procedure cache goes down to zero available during some spikes..

So setting the MAX has had an effect because it saves going to physical disk every time.

Lock memory in ...All this does is prevent memory set trimming by the OS for the SQL Process space.

(If i have understood correctly..when os need memory it will start taking this from the other applications on server that are using the memory)..so if you do not want it to take from SQL Server then thats why you do the lock in memory part)...so it is forced to take from other processes and not SQL...)...Did i get this right.

Monitoring available physical memory in task manager and see if this is going up and down..if drastically increasing give more memory back to OS from SQL i.e change to 5 GIG..?