November 26, 2012 at 4:10 am
hello experts,
please go through the below configuration:-
sql server 2005 sp2
windows server 2003 R2 (64 bit)
CPU 2.13 GHz
RAM 128 GB
max server memory 100GB
min server memory 40GB
Today morning got an issue server was too slow and concernd team taken a screen shot of memory utilization till 102 GB, first concern, is any possibility that sql server can take more memory than what we have defined in max server memory??
November 26, 2012 at 4:11 am
Yes, SQL can go over what you have defined as the max server memory as certain things are stored in memory outside of the buffer pool.
November 26, 2012 at 4:27 am
but if it can go beyond the max server memory limit thn what is the feasibility to define it, please elaborate as i am unable to convince myself.
November 26, 2012 at 4:30 am
You set the max memory so that the buffer pool doesnt consume all your servers resources resulting in SQL starving the OS of resources and the time it takes for SQL to release the resources back to the OS which would involve a potential large amount of paging depending how much RAM SQL was force to release back to the OS, resulting in detremental performance while the operations are performed.
Objects such as CLR, linked servers, COM objects all use memory from outside of the buffer pool so they will increase your memory usage.
November 26, 2012 at 6:18 am
anthony,
today since morning it is showing that sqlservice taking more than 100 GB, i checked in backend current running querry which are very less, no blockages and all query taking very less cpu usage, can u tell me how can i find or troubleshoot the root cause why is sql service showing that much high memory utilization in windows task manager.
November 26, 2012 at 6:21 am
Read the memory management chapter in the Accidental DBA guide, link to the guide in my signature below.
November 26, 2012 at 6:53 am
1) High memory use by SQL Server is normal
2) Max server memory specifies the size of the buffer pool. SQL can and will use memory outside of the buffer pool. This is normal and not a cause for concern, alarm or panic.
3) Don't use Task Manager to monitor SQL's memory, it can be inaccurate (because of the way SQL allocates 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
November 26, 2012 at 7:01 am
Gail,
but for now is there any thing in sql or windows which i should check????
November 26, 2012 at 7:03 am
As I said...
High memory use by SQL is normal. Hence not a cause for concern.
SQL can and will use memory outside of the buffer pool (which is what is controlled by max server memory). This also is normal and not a cause for concern.
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 26, 2012 at 7:10 am
ok.... got your point ...
but windows team and client team always send the screen shot of high memory utilization and tell us they are getting alert because of SQL high memory utilization and ask for RCA... dont undertansd how would i convince them.
November 26, 2012 at 7:23 am
Send them the book Anthony mentioned and tell them to read chapter 4, and explain to them that high memory usage is normal and that they should stop telling you that everything's fine, please fix it.
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 26, 2012 at 7:26 am
ok thanks Gail 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply