October 25, 2012 at 1:32 am
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
October 25, 2012 at 3:04 am
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
October 25, 2012 at 3:11 am
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
October 25, 2012 at 3:17 am
It is a 32 bit system but enabled AWE but no use
Regards
Durai Nagarajan
October 25, 2012 at 3:20 am
It is a 32 bit system but enabled AWE but no use
Regards
Durai Nagarajan
October 25, 2012 at 3:21 am
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
October 25, 2012 at 3:39 am
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
October 28, 2012 at 7:37 pm
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
April 20, 2016 at 12:32 am
That's one case of "Don't Follow Me Follow The Birds."
September 11, 2017 at 2:27 pm
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
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply