January 24, 2014 at 5:18 am
Hi,
On one of our prod server memory is taking 97% in task manager and i have observed that SQL is taking high in task manger.
OS:Win server 2008
DB Instance:Sql server 2008
Ram : 4 GB
I have checked SQL and there are no huge process are running,no jobs are running, no blockings,no memory errors in error log.
but Max and min memory is not set and it is default.
I have checked in task manager that sql is taking not more that 2944 399 KB(i.e 2.8 GB), i think it is normal and there is no issue with Please suggestion me whether i need to set max memory on the server .
advance thanks
January 24, 2014 at 6:10 am
I suggest you always set the max memory. You're right. Your server doesn't seem to be under stress and the chances are, it won't be. But, SQL Server will attempt to take all the memory it can. Meanwhile, the OS needs memory too. You don't want them fighting for resources, so it's good to set aside some for the OS by limiting the amount that SQL Server can take by setting the Max memory.
There's a lot of debate about minimum memory settings. Myself, for a production server, I'd probably just set the Min to the same as the Max so that when you start SQL Server it just allocates it's memory and then is done with that task. But opinions on that really differ.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 24, 2014 at 6:18 am
Grant Fritchey (1/24/2014)
Myself, for a production server, I'd probably just set the Min to the same as the Max so that when you start SQL Server it just allocates it's memory and then is done with that task. But opinions on that really differ.
I don't recommend that. Setting min to max means that SQL can't respond to memory pressure. Normally, if the server comes under memory pressure (file copy, application running, etc), Windows will notify SQL that memory is low and please to reduce memory usage. SQL will reduce it's memory usage, down to min server memory. If min is the same as max, then SQL won't reduce memory at all, leaving the OS under memory pressure. The OS will then try to page applications out (to the swap file) to get memory. If locked pages is not enables, SQL may be the app that gets paged out causing all sorts of performance problems. If locked pages is enabled and the OS can't page enough memory out from other apps, the server may crash.
Personally, min server memory = 0 unless I have some reason to set it otherwise.
Min server memory is not the amount that SQL will allocate at startup. It's the amount that, once it reaches, it will not reduce its memory allocation below.
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
January 24, 2014 at 6:21 am
Like I said. Opinions differ.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply