April 5, 2010 at 8:43 am
I got SQL Server 2005 on a 64-bit with 16GB RAM. SQL Server is taking around 15.2 GB out of it. I would like to set the Min/Max server memory limits.
What max server memory should i set it to?
Thanks In Advance
April 5, 2010 at 2:02 pm
I'd set max memory to 14GB leaving 2GB for the OS.
Monitor PLE (page life expectancy) before and after changes to determine if additional memory may be beneficial.
April 5, 2010 at 2:04 pm
You can set Min memory to 0.
April 5, 2010 at 2:06 pm
How can i moitor PLE (page life expectancy)? I'm thinking of giving 12GB to SQL Server and leave rest 4 GB to OS and other applications.
Is there any draw backs of doing this?
Thanks.
April 5, 2010 at 2:10 pm
If it's a dedicated SQL Server, the OS does not need more than 1-2GB
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 5, 2010 at 2:11 pm
Satish-219904 (4/5/2010)
How can i moitor PLE (page life expectancy)?
which monitoring tool do you use. Perfmon has a counter available for this or view it via the following query
select cntr_value from sys.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy'
Satish-219904 (4/5/2010)
I'm thinking of giving 12GB to SQL Server and leave rest 4 GB to OS and other applications.
That is where i would start and increase if necessary.
Have you applied the "lock pages in memory" local security policy to the sql server service account?
If you have this policy set you definitely need to set the min and max memory settings
4GB min - 12 GB max would be ideal place to start
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 5, 2010 at 2:15 pm
I have not applied "Lock pages in memory" option yet but planning to do this along with this change. Hopefully it should increase the performance of my SQL Server.
April 5, 2010 at 2:21 pm
Satish-219904 (4/5/2010)
I have not applied "Lock pages in memory" option yet but planning to do this along with this change. Hopefully it should increase the performance of my SQL Server.
ensure you set min and max ram sensibly. See my revised post above for how to view the page life expectancy counter
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 5, 2010 at 2:34 pm
Thanks for the query. It is very helpful. Here are the values of the following 2 counters:
Page life expectance: 22804
Buffer cache hit ratio: 6580
What do these values mean?
April 5, 2010 at 3:45 pm
Hi
the values all have different meanings. The buffer cache hit ratio should be compared against buffer cache hit ratio base. Take buffer cache hit ratio and divide by the base then multiply the result by 100, this is your percentage. For example
1448 / 1450 = .99862 x 100 = 99.86%
Page life expectancy is a per second counter and needs to be sampled and compare the values
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 5, 2010 at 4:12 pm
I use perfmon to monitor PLE.
Lock pages in memory is advised only in cases where it proves to improve performance, experiment! In theory the setting should not be required.
Dan
April 6, 2010 at 12:30 am
Lock Pages in Memory setting is required when you are using 64 bit edition.
April 6, 2010 at 1:40 am
We a dedicated SQL server 2005 EE x64 with SP3.
RAM: 16 GB
Max memory: 12 GB
Min memory: default i.e 0
Lock pages in memory enableb
As best practices says that have Memory\Available MBytes atleast 20-25 % RAM. But it NOT possible if set Max memory to 12 GB & left 4 GB to OS.
In my case Memory\Available MBytes is 2049 which is 15% of RAM.
So you may need to set the Max memory to 11 GB & left 5 GB to OS so that Memory\Available MBytes should be 20-25 % of RAM .ie 16 GB
Thanks
April 6, 2010 at 7:04 am
drawlings (4/5/2010)
I use perfmon to monitor PLE.Lock pages in memory is advised only in cases where it proves to improve performance, experiment! In theory the setting should not be required.
Dan
it is especially advised if you start to see errors in the logs as detailed by the MS tech link previously provided by Dhananjay-440114
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 6, 2010 at 9:16 am
From your responses so far,
I will add make the max. memory change and add the sql service account to "lock pages in memory" option. Hopefully these two changes will fix the paging issues that i have been seeing.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply