Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

high memory usage Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 4:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 12:28 AM
Points: 207, Visits: 1,014
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??



Post #1388508
Posted Monday, November 26, 2012 4:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1388510
Posted Monday, November 26, 2012 4:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 12:28 AM
Points: 207, Visits: 1,014
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.
Post #1388521
Posted Monday, November 26, 2012 4:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1388522
Posted Monday, November 26, 2012 6:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 12:28 AM
Points: 207, Visits: 1,014
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.
Post #1388575
Posted Monday, November 26, 2012 6:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
Read the memory management chapter in the Accidental DBA guide, link to the guide in my signature below.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1388578
Posted Monday, November 26, 2012 6:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1388593
Posted Monday, November 26, 2012 7:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 12:28 AM
Points: 207, Visits: 1,014
Gail,
but for now is there any thing in sql or windows which i should check????
Post #1388600
Posted Monday, November 26, 2012 7:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1388602
Posted Monday, November 26, 2012 7:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 12:28 AM
Points: 207, Visits: 1,014
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.
Post #1388607
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse