Total Server Memory Vs Target Server Memory

Question

Total Server Memory Vs Target Server Memory

Viewing 10 posts - 31 through 39 (of 39 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If SQL has allocated 94 GB then it is using 94GB. If that's too much, reduce the max server memory setting.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

durai nagarajan SSCoach Points: 15775 More actions · Answer 2

In my case it is reverese , we have a server where in 8 GB available but sql is using only 1.8 GB , how can i increase it.

i have already added min memory as 1 GB and max as 6.5 GB but after 2 weeks also there was no increase in memory usage.

is it ok?

server - SQL 2005 SP4.

Regards
Durai Nagarajan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You have 32 bit SQL and are not using AWE.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

durai nagarajan SSCoach Points: 15775 More actions · Answer 4

It is a 32 bit system but enabled AWE but no use

Regards
Durai Nagarajan

durai nagarajan SSCoach Points: 15775 More actions · Answer 5

It is a 32 bit system but enabled AWE but no use

Regards
Durai Nagarajan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

You either haven't enabled AWE, haven't granted locked pages permission or haven't put the /PAE switch in boot.ini (or more than one of those). When you've got AWE working, SQL can use more than 1.8 GB of memory.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

durai nagarajan SSCoach Points: 15775 More actions · Answer 7

GilaMonster (10/25/2012)
haven't granted locked pages permission or haven't put the /PAE switch in boot.ini

i havent done these.

can you explain in detail and if possible any document or link for the same.

thanks in advance.

Regards
Durai Nagarajan

SimonLiew SSCrazy Eights Points: 8213 More actions · Answer 8

PAE switch is added to your c:\boot.ini to allow 32-bit OS to see more than 4GB server memory (requires server reboot).

Once this is done and you can see all the server memory, you grant SQL server service account "Lock pages in memory" into your Local Security Policy (requires server reboot).

Then you set AWE to 1 (requires SQL server reboot) and set your max server memory. SQL error log should tell you if SQL is able to grab the memory.

You may perform certain tasks that require server reboot at once rather than rebooting the server multiple time. There are lots of step-by-step instruction on how to perform each of these tasks on Google.

RE: SQL target vs total, SQL Server checks OS memory every second to adjust its target based on available OS memory and max server memory.

If target > total then you're fine i.e. after reboot sql server, target = sql max memory > total. sql will slowly obtain additional memory (called RampUp) until target = total. Total is the memory SQL has committed.

If target < total, its a notification to sql to try trim memory until target = total. This might indicate you could benefit from additional memory.

Total Server Memory and Target Server Memory corresponds to bpool_committed and bpool_commit_target in sys.dm_os_sys_info DMV. In SQL 2008, bpool are expressed in 8-KB buffers. In SQL 2012, they're expressed in kb.

http://msdn.microsoft.com/en-us/library/ms175048.aspx

If committed_target_kb is larger than committed_kb, the memory manager will try to obtain additional memory. If committed_target_kb is smaller than committed_kb, the memory manager will try to shrink the amount of memory committed. The committed_target_kb always includes stolen and reserved memory. Not nullable.

Simon

Simon Liew
Microsoft Certified Master: SQL Server 2008

chris_mayimele SSC Journeyman Points: 75 More actions · Answer 9

That's one case of "Don't Follow Me Follow The Birds."

carlos.trujillo Grasshopper Points: 11 More actions · Answer 10

I don't know where this myth originates, but Target server memory is the memory SQL Server could reserve, in other words your max server memory configuration, is not some "desired memory" calculated by sql, you can try that changing your max server memory config and see that reflected in the counter, doesn't make sense that SQL change his "desired" memory because you change the max memory parameter; Total is the actual memory used, so if total >= target you are under pressure