high memory usage
-
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??
-
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.
-
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.
-
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.
-
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.
-
Read the memory management chapter in the Accidental DBA guide, link to the guide in my signature below.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gail,
but for now is there any thing in sql or windows which i should check????
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
ok thanks Gail
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply