October 28, 2011 at 4:12 am
Hi,
My server have 32GB RAM and it's bind with 28GB to SQL Server but when I run the test load over 20hrs it's exceed the configured value.
And we are using shared memory connection with Applicaiton.
Can someone light on this, this is memory leak with SQL server or anything else...
If this is memory leak then Microsoft has released any hotfix for it?
Thanks,
October 28, 2011 at 4:39 am
First thing to come into my mind: Do you have multiple instances on the server? If so, you should be aware of the fact that max server memory is a per instance setting.
Apart from this, where/how do you look up the amount of memory allocated to SQL Server?
October 28, 2011 at 5:49 am
No there is only single instance and I bind memory in database level I means in property -> Memroy -> Max Memory setting.
Thanks for your prompt response.
Regards,
October 28, 2011 at 6:36 am
Yup. Perfectly normal.
Max server memory is the max size of the buffer pool, the memory area that contains the data cache, plan cache and a whole bunch of other caches. SQL also uses memory outside the buffer pool for things like backup buffers, thread stack, linked server drivers, CLR and a few other things. This is outside of the buffer pool, so it's not part of 'max server memory'
On 32-bit SQL, that's referred to as MemToLeave (memory to leave unallocated when assigning the buffer pool). On 64 bit that term has no meaning.
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 28, 2011 at 9:47 am
Thanks for you prompt reply...
Is anyway we can bind SQL Server memory which will not exceed configured value.... in 64-bit environment..
Regards,
October 28, 2011 at 10:01 am
You can only control the buffer pool. The non-buffer pool memory should be relatively small, unless you're doing weird things with CLR. If SQL as a whole is using too much memory, reduce the size of the buffer pool as that's you can control
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 28, 2011 at 2:39 pm
For example on one of my machines I have max memory set at 26,624 MB, which is my buffer size, the plan cache is an additional 4,408 MB. I don't believe there is a setting to force the plan cache to be smaller, only the buffer cache, you really shouldn't be running anything else on your sql box anyway.
October 29, 2011 at 4:26 am
benjamin.reyes (10/28/2011)
For example on one of my machines I have max memory set at 26,624 MB, which is my buffer size, the plan cache is an additional 4,408 MB.
The plan cache is part of the buffer cache, so your plan cache takes up 4408MB of the 26624MB buffer pool. What's outside of the buffer pool is things like CLR memory, backup buffers, thread stacks, linked server drivers and a couple other things. They're generally smallish 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 16, 2011 at 1:11 pm
Is there a way to find how much memory each of these specific components use?
November 16, 2011 at 2:56 pm
You mean something like this
select
SUM(pages_allocated_count)*8 as MemKB,
type
from
sys.dm_os_memory_objects
group by
type
order by
MemKB desc
select
SUM(pages_allocated_count)*8 as MemKB,
type
from
sys.dm_os_memory_cache_entries
group by
type
order by
MemKB desc
November 16, 2011 at 4:10 pm
I was going to add a topic on this, but then I saw this thread.
In the following article, it talks about setting min server memory based on an equation, 24k * numusers.
http://technet.microsoft.com/en-us/magazine/ff621496.aspx
It also states "recommended maximum value for stand-alone servers is at or near total RAM".
I have never heard of either of these. I set min to be 1gb myself, so the OS doesn't take all the RAM back from SQL and SQL can't run. I have also read M$ recommends min to be 512mb somewhere.
As for the max set to total RAM, I have never read that before. I usually give it 4gb to 8gb on most of my servers, or limit it to 2gb on small servers.
I'm asking what real-world DBAs use for Min and Max, are their rules the community uses, or a Best Practice that is used depending on certain conditions, maybe something laid out in a Matrix?
November 16, 2011 at 9:38 pm
(BALLPARK Numbers. No two sql server instances have the same workload.)
Dedicated to 1 instance of SQL Server (leaving 1.5-4 GB for the OS):
GB RAM/Max Memory Setting
8/6
16/14
32/29
64/60
128/124
If you've got more stuff running (multiple instances, SSAS, SSRS, SSIS, etc) then you'll need to figure out what makes sense for your situation.
For example, If you've got a server with 32 GB, running SQL, SSRS, SSAS and SSIS.
After watching memory usage, you determine that the SSIS packages max out around 800 MB total at any point in time. SSRS seems to stay around 1.2 GB.
So, do some math, 32 Gb - 1.5GB (for the OS) -.8GB (SSIS) - 1.2GB (SSRS) = 28.5GB for SQL and SSAS to split.
You experiment and determine that SQL getting 18 GB and SSAS getting 10 is a good split and those settings become your max memory settings. You may also decide to set min memory in case something bad happens (rogue SSRS report), so you decide that sql should always have at least 8 GB, and SSAS at least 6.
You've covered your bases, and you've got 14GB of wiggle room if something goes haywire, but on a good day, most of your ram will be utilized without paging.
November 16, 2011 at 10:26 pm
So, do some math, 32 Gb - 1.5GB (for the OS) -.8GB (SSIS) - 1.2GB (SSRS) = 28.5GB for SQL and SSAS to split.
It's good to start but there is no formula to get this number. You can guess (yes, it's a right word) some number based on your experience, which you MUST verify with different data load.
Take the same example:
8GB (SSIS)
1.2GB (SSRS)
Nobody runs SSIS packages all day. So you may count it for SQL Server database.
November 17, 2011 at 12:49 am
okbangas (11/17/2011)
Nice to see that I am nobody 🙂
LOL 😀
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply