November 2, 2012 at 3:34 pm
I have one sql server 2008 r2 hosted in a 2 node cluster. I have set min server memory as 64GB and max server memory is 118 GB out of 128GB RAM . It is x64 bit sql server and Lock pages in memory has been enabled
But the issue is not related to SQL Server. BUt current memory consumption is 126 GB. My worry is 127-118=9GB. What is consuming the 9 GB. DO, I need to reduce max server memory setting.
SO, how to find what is consuming that rest of 9 GB .
I ran tasklist and found that the sum of all private working set is around 2.5 GB including sql server.exe
I know that 118 GB is BPOOL size and rest 400MB is multi page allocator. SO, what is the best way to find the memory distribution.
November 2, 2012 at 3:45 pm
abishekshroff (11/2/2012)
I have one sql server 2008 r2 hosted in a 2 node cluster. I have set min server memory as 64GB and max server memory is 118 GB out of 128GB RAM . It is x64 bit sql server and Lock pages in memory has been enabledBut the issue is not related to SQL Server. BUt current memory consumption is 126 GB. My worry is 127-118=9GB. What is consuming the 9 GB. DO, I need to reduce max server memory setting.
SO, how to find what is consuming that rest of 9 GB .
I ran tasklist and found that the sum of all private working set is around 2.5 GB including sql server.exe
I know that 118 GB is BPOOL size and rest 400MB is multi page allocator. SO, what is the best way to find the memory distribution.
The max memory setting in SQL 2008 R2 establishes the upper limit to the amount of memory used by the buffer pool.
http://msdn.microsoft.com/en-us/library/ms180797(v=sql.105).aspx
"SQL Server as a process acquires more memory than specified by max server memory option. Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory, but the memory allocated to the buffer pool usually represents the largest portion of memory consumed by SQL Server."
You will likely want to considering lowering the value for max server memory in order to reduce the size of the buffer pool allocation and allow more memory for the operating system and other SQL Server processes.
November 2, 2012 at 4:04 pm
That's the easiest route . Reducing max server memory . But my concern is is there any memory leak. Apart from 118gb given to bool additional 400mb is used by SQL . Multi page alloc and linked server, there'd sack etc etc...so how to find what others r consuming memory.... 7gb is pretty big no , I want to know the rest of 7gb.. But I don't know best possibl way....
November 2, 2012 at 4:14 pm
My first guess would be the plan cache.
Here is a query I believe originated from Kalen Delaney.
SELECT objtype, count(*) AS 'number of plans',
SUM(size_in_bytes)/(1024.0*1024.0*1024.0) AS size_in_gb_single_use_plans
FROM sys.dm_exec_cached_plans
GROUP BY objtype
I had to modify it to run on my server due to an arithmetic overflow error:
SELECT objtype, count(*) AS 'number of plans',
convert(numeric(18,2),SUM(convert(numeric(18,2),size_in_bytes)))/(1024.0*1024.0*1024.0)-- AS size_in_gb_single_use_plans
FROM sys.dm_exec_cached_plans
GROUP BY objtype
On my server with 512 GB of RAM, the grand total is somewhere in the neighborhood of 16 GB roughly evenly split between Ad Hoc plans and Stored Procedure plans.
November 3, 2012 at 12:12 am
Plan cache is bpool component post of the times .. Correct? Then it ll be part of 118GB
November 3, 2012 at 2:11 am
Plan cache is part of the buffer pool, therefore constrained by max server memory.
No, not a memory leak (unless you have buggy linked server drivers). Combo of thread stacks, CLR memory, linked server drivers, backup buffers and a few other things.
If SQL is taking too much memory, reduce max server memory slightly. If you're using cLR, examine the code for large memory allocations
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
November 3, 2012 at 4:46 am
How can i measure the non Bpool component??
Note: Its 64 bit server
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply