Configuring SQL Server memory settings

Question

Post reply

Configuring SQL Server memory settings

Satish-219904

SSC Eights!

Points: 874
More actions
April 5, 2010 at 8:43 am

#138226

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

Viewing 15 posts - 1 through 15 (of 15 total)

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

Enterprise DBA SSC-Addicted Points: 478 More actions · Answer 1

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.

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 2

Mh-397891

SSCertifiable

Points: 6704

April 5, 2010 at 2:04 pm

#1145985

You can set Min memory to 0.

Satish-219904 SSC Eights! Points: 874 More actions · Answer 3

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.

GregoryF SSCertifiable Points: 6296 More actions · Answer 4

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!

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

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" 😉

Satish-219904 SSC Eights! Points: 874 More actions · Answer 6

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.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

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" 😉

Satish-219904 SSC Eights! Points: 874 More actions · Answer 8

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?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

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" 😉

Enterprise DBA SSC-Addicted Points: 478 More actions · Answer 10

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

Dhananjay-440114 Ten Centuries Points: 1365 More actions · Answer 11

Lock Pages in Memory setting is required when you are using 64 bit edition.

http://support.microsoft.com/kb/918483

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 12

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 13

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" 😉

Satish-219904 SSC Eights! Points: 874 More actions · Answer 14

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.