Memory - Total/Target always using same value?

Question

Memory - Total/Target always using same value?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
March 23, 2012 at 5:21 am

#255181

Hi,
Total RAM - 4 GB, Not enable /3GB switch in SQL buffer side.
SQL server using always 70 % memory and Total & Target always using same values.. please suggestion me how to reduce the memory percentage?
Production server Memory Setting as below
Min -0
Max -3200
Min memory per query - 1024 KB
set working set size -0
Monitor the memory counter as below
Target Server Memory (KB) - 1599.813
Total Server Memory (KB) - 1599.813
Buffer cache hit ratio - always 95 to 100 %
Page life expectancy - always 700 sec & more
Memory\Pages/sec - always 0
Cache Hit Ratio - always 70 to 86
Thanks
ananda

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

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

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 1

What problems are you having that you want to change your memory settings?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Your readings are completely normal. SQL Server will claim as much memory as you allow it provided it thinks its needs it to efficiently serve data to clients. 1.6GB is the most SQL Server can grab on a 32-bit system without the /3GB switch enabled or without AWE enabled. Your hit ratio and PLE could be better, which you may be able to improve by adding the /3GB switch or eanbling AWE. How many Megabytes free do you average on your server? That should tell you whether you have memory to spare to allocate for SQL Server to use.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

opc.three (3/23/2012)
Your readings are completely normal. SQL Server will claim as much memory as you allow it provided it thinks its needs it to efficiently serve data to clients. 1.6GB is the most SQL Server can grab on a 32-bit system without the /3GB switch enabled or without AWE enabled. Your hit ratio and PLE could be better, which you may be able to improve by adding the /3GB switch or eanbling AWE. How many Megabytes free do you average on your server? That should tell you whether you have memory to spare to allocate for SQL Server to use.

AWE won't make any difference unless he adds memory over 4GB, but the /3GB switch could allow for additional memory to be used by SQL Server.

I see the max memory setting is set to 3200 - and the only way to get anywhere close to that would be to enable the /3GB switch.

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Jeffrey Williams 3188 (3/23/2012)
opc.three (3/23/2012)
Your readings are completely normal. SQL Server will claim as much memory as you allow it provided it thinks its needs it to efficiently serve data to clients. 1.6GB is the most SQL Server can grab on a 32-bit system without the /3GB switch enabled or without AWE enabled. Your hit ratio and PLE could be better, which you may be able to improve by adding the /3GB switch or eanbling AWE. How many Megabytes free do you average on your server? That should tell you whether you have memory to spare to allocate for SQL Server to use.
AWE won't make any difference unless he adds memory over 4GB, but the /3GB switch could allow for additional memory to be used by SQL Server.
I see the max memory setting is set to 3200 - and the only way to get anywhere close to that would be to enable the /3GB switch.

Please have a look at this article: AWE and 3GB: An Empirical Picture

It shows that with 3GB off, and AWE on we can access memory outside of the user-mode VAS, i.e. more than the default of 2GB on 32-bit system. This is counter-intuitive of course, but is shown in the article. The article details a 16GB system, however this is true on a 4GB system as well.

Here too is a recent thread that details the effects done on a server just last week with 4GB of RAM by another esteemed frequenter of these forums: http://www.sqlservercentral.com/Forums/FindPost1267111.aspx

Note the "buffer pool target" for this entry:

physical mem, VAS, buffer pool committed, buffer pool target

AWE on, 3GB off

4.00,2.00,0.02,3.14

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

george sibbald SSC Guru Points: 104210 More actions · Answer 5

sorry opc.three I am still not convinced on this use of AWE with 4Gb or less of physical memory and 32 bit, I have not seen any evidence yet of more than 2GB of memory being actually used in this scenario, bpool_commit_target is just that, a target rather than an actual amount used.

I think we should keep it simple and advise on the method known to work, i.e. set the /3GB switch in the boot.ini.

---------------------------------------------------------------------

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

george sibbald (3/23/2012)
sorry opc.three I am still not convinced on this use of AWE with 4Gb or less of physical memory and 32 bit, I have not seen any evidence yet of more than 2GB of memory being actually used in this scenario, bpool_commit_target is just that, a target rather than an actual amount used.
I think we should keep it simple and advise on the method known to work, i.e. set the /3GB switch in the boot.ini.

The simplicity of one configuration versus another is debatable but I respect your position. I actually went with "AWE on, 3GB on" to protect against hard memory trims Windows was imposing on my SQL Server instance, and that has worked well for the stability and performance of my server. I could not vouch for either "AWE on, 3GB off" or "AWE off, 3GB on" as I have no experience with either in a production environment, however I would not object to trying either configuration. In theory they both have merit.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

For the server now using "AWE on, 3GB on" I never tested "AWE on, 3GB off" but I just found a good dev-server that is a candidate for this treatment. I just enabled AWE and do not have the 3GB switch in place.

Prior to the changes it was AWE off, 3GB off:

physical mem, VAS, buffer pool committed, buffer pool target, max server memory

3.75,2.00,1.56, 1.56, 2.44

Immediately after the change to AWE on, 3GB off:

physical mem, VAS, buffer pool committed, buffer pool target, max server memory

3.75,2.00,0.07, 2.44, 2.44

The buffer pool will warm up eventually, I'll keep an eye on it and post back to see if the buffer pool breaks through the 2GB barrier.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

So I just ran a bunch of queries to warm up the buffer pool and got the committed memory up to 2.44GB with "AWE on, 3GB off"

physical mem, VAS, buffer pool committed, buffer pool target, max server memory

3.75, 2.00, 2.44, 2.44, 2.44

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

george sibbald SSC Guru Points: 104210 More actions · Answer 9

thanks opc, Thats better, now we have proof.

Just to complete the picture:

what does task manager say SQL is using ?

whats the value for perfmon - sql server memory manager - total server memory?

is LPIM enabled?

whats message is printed out concerning AWE at the start of the errorlog?

what happens if max server memory is left to default?

---------------------------------------------------------------------

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

george sibbald (3/23/2012)
thanks opc, Thats better, now we have proof.
Just to complete the picture:

Indeed, for completeness...

what does task manager say SQL is using ?

Only ~80MB. At first glance this may seem like a stumper but it makes sense when you consider that memory committed using the AWE APIs will not show up in Task Manager.

whats the value for perfmon - sql server memory manager - total server memory?

Perfmon shows 2560000 bytes. Which is equivalent to 2500MB, i.e. 2.44 GB, the buffer pool max memory setting. Makes sense.

is LPIM enabled?

Yes, the LPIM ("lock pages in memory") privilege was granted to the Windows account running the SQL Server service (a.k.a. "the SQL Server service account"). For completeness, we know that LPIM must be granted to the service account before SQL Server can be configured to use AWE.

whats message is printed out concerning AWE at the start of the errorlog?

This message appears in the error log shortly after startup, indicating that SQL Server has privileges to, and has opted to utilize the AWE APIs to allocate memory:

Address Windowing Extensions is enabled. This is an informational message only; no user action is required.

what happens if max server memory is left to default?

Since SQL Server has access to the AWE APIs it could theoretically allocate an amount of memory up to the entire 4GB of physical memory, leaving 0 bytes of RAM free for the OS to allocate to other processes, including itself. In setting the "max server memory" option in SQL Server we restrict the amount of memory SQL Server can allocate for use by its buffer pool, which if set conservatively should always leave enough room for the OS to allocate additional memory for other processes, even at peak usage times. In my test case I set the "max memory" option to 2500MB to guard against an OS-memory-starvation scenario caused by SQL Server.

Test environment:

- Windows Server 2003 R2 x86 w/SP2

- SQL Server 2005 Standard Edition x86 w/SP4

- 3.75 GB physical RAM

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

george sibbald SSC Guru Points: 104210 More actions · Answer 11

thanks for posting back

---------------------------------------------------------------------

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 12

1. Could you tell me, In Memory Object counter - Total & Target memory shows same values and same memory. Does these values are good or bad?

2. Total 4GB Physical RAM, How much we max memory setting in SQL Server ?

Thanks

ananda

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 13

ananda.murugesan (3/25/2012)
1. Could you tell me, In Memory Object counter - Total & Target memory shows same values and same memory. Does these values are good or bad?
2. Total 4GB Physical RAM, How much we max memory setting in SQL Server ?
Thanks
ananda

Ananda,

I will repeat my question. What problems are you having?

Any advice we can give you on settings depends entirely on the answer to this question as there is no "right" answer for all situations. If you are not experiencing any memory issues, then I suggest you leave the settings as they are.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

george sibbald SSC Guru Points: 104210 More actions · Answer 14

total and target memory the same = a good thing.

is this a 32 or 64 bit system?

---------------------------------------------------------------------