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

High Memory consuming by Database Expand / Collapse
Author
Message
Posted Wednesday, August 07, 2013 12:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 07, 2013 4:54 AM
Points: 64, Visits: 204
Hello Everyone,

I have a database which size is 250 GB and it contains the information about 70 Lak users with photograph.
but as per our reservation the database consuming 64 GB of memory of database server.
Report as attached


Memory Consumption
on EDSERVER at 7/26/2013 3:44:02 PM
This report provides detailed data on the memory consumption of components within the Instance as well as historical data on changes in the Instance's memory footprint as recorded by the Default Trace.
Memory Grants Outstanding 2 Page life expectancy 52506
Memory Grants Pending 0




Memory Changes Over Time (Last 7 Days)

There are no major changes in memory consumption or default trace is not enabled


Memory Usage By Components
Component Type Allocated memory(KB) Virtual Memory Reserved(KB) Virtual Memory Committed(KB) AWE Memory Allocated(KB) Shared Memory Reserved(KB) Shared Memory Committed(KB)
CACHESTORE_SQLCP 6,502,736 0 0 0 0 0
OBJECTSTORE_LOCK_MANAGER 654,936 262,144 262,144 0 0 0
MEMORYCLERK_SQLOPTIMIZER 72,040 0 0 0 0 0
MEMORYCLERK_SOSNODE 70,368 0 0 0 0 0
CACHESTORE_OBJCP 64,792 0 0 0 0 0
CACHESTORE_PHDR 54,704 0 0 0 0 0
USERSTORE_SCHEMAMGR 21,424 0 0 0 0 0
USERSTORE_TOKENPERM 20,208 0 0 0 0 0
MEMORYCLERK_SQLGENERAL 15,776 0 0 0 0 0
OBJECTSTORE_LBSS 11,392 0 0 0 0 0
MEMORYCLERK_SQLSTORENG 10,128 16,128 16,128 0 0 0
USERSTORE_DBMETADATA 9,128 0 0 0 0 0
OBJECTSTORE_SNI_PACKET 9,040 0 0 0 0 0
MEMORYCLERK_XE 5,536 0 0 0 0 0
CACHESTORE_SYSTEMROWSET 3,184 0 0 0 0 0
MEMORYCLERK_SQLQUERYEXEC 2,320 0 0 0 0 0
USERSTORE_OBJPERM 1,144 0 0 0 0 0
MEMORYCLERK_SQLCONNECTIONPOOL 1,080 0 0 0 0 0
MEMORYCLERK_SQLBUFFERPOOL 912 67,190,784 62,377,576 0 0 0
MEMORYCLERK_SQLSERVICEBROKER 688 0 0 0 0 0
MEMORYCLERK_SNI 552 0 0 0 0 0
OBJECTSTORE_SERVICE_BROKER 424 0 0 0 0 0
CACHESTORE_XPROC 192 0 0 0 0 0
MEMORYCLERK_BHF 152 0 0 0 0 0
USERSTORE_SXC 120 0 0 0 0 0
MEMORYCLERK_SQLUTILITIES 96 240 240 0 0 0
CACHESTORE_TEMPTABLES 80 0 0 0 0 0
CACHESTORE_BROKERTBLACS 56 0 0 0 0 0
CACHESTORE_BROKERREADONLY 56 0 0 0 0 0
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT 48 0 0 0 0 0
CACHESTORE_FULLTEXTSTOPLIST 32 0 0 0 0 0
CACHESTORE_CONVPRI 32 0 0 0 0 0
MEMORYCLERK_FULLTEXT 24 0 0 0 0 0
CACHESTORE_VIEWDEFINITIONS 16 0 0 0 0 0
MEMORYCLERK_HOST 16 0 0 0 0 0
MEMORYCLERK_SQLXP 16 0 0 0 0 0
CACHESTORE_STACKFRAMES 16 0 0 0 0 0
OBJECTSTORE_SECAUDIT_EVENT_BUFFER 16 0 0 0 0 0
CACHESTORE_NOTIF 16 0 0 0 0 0
CACHESTORE_EVENTS 16 0 0 0 0 0
CACHESTORE_XMLDBTYPE 8 0 0 0 0 0
CACHESTORE_BROKERRSB 8 0 0 0 0 0
CACHESTORE_XMLDBATTRIBUTE 8 0 0 0 0 0
MEMORYCLERK_SQLHTTP 8 0 0 0 0 0
CACHESTORE_XMLDBELEMENT 8 0 0 0 0 0
MEMORYCLERK_SQLCLR 8 0 0 0 0 0
CACHESTORE_BROKERTO 8 0 0 0 0 0
CACHESTORE_BROKERKEK 8 0 0 0 0 0
CACHESTORE_BROKERUSERCERTLOOKUP 8 0 0 0 0 0
CACHESTORE_BROKERDSH 8 0 0 0 0 0
MEMORYCLERK_XE_BUFFER 0 4,224 4,224 0 0 0
MEMORYCLERK_FULLTEXT_SHMEM 0 0 0 0 288 288
MEMORYCLERK_SQLSOAPSESSIONSTORE 0 0 0 0 0 0
MEMORYCLERK_SQLQERESERVATIONS 0 0 0 0 0 0
MEMORYCLERK_SQLQUERYCOMPILE 0 0 0 0 0 0
MEMORYCLERK_SQLXML 0 0 0 0 0 0
MEMORYCLERK_SQLSOAP 0 0 0 0 0 0
MEMORYCLERK_TRACE_EVTNOTIF 0 0 0 0 0 0
MEMORYCLERK_SQLQUERYPLAN 0 0 0 0 0 0
MEMORYCLERK_QSRANGEPREFETCH 0 0 0 0 0 0
MEMORYCLERK_SQLCLRASSEMBLY 0 0 0 0 0 0



Can anyone guide how can I reduce the uses of memory.
Post #1481669
Posted Wednesday, August 07, 2013 2:09 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: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
Why do you want to reduce memory usage? SQL uses memory to improve performance because memory is faster than disk. It will cache as much as it can to avoid having to read off slow disks.


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 #1481705
Posted Wednesday, August 07, 2013 2:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 07, 2013 4:54 AM
Points: 64, Visits: 204
Because it effects the execution of other applications
Post #1481721
Posted Wednesday, August 07, 2013 3:07 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: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
If SQL is using too much memory, reduce max server 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 #1481746
Posted Wednesday, August 07, 2013 10:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:49 AM
Points: 226, Visits: 667
amitsingh308 (8/7/2013)
Because it effects the execution of other applications


Reduce the max memory allowed for SQL.

Personally.. If other applications require large amounts of memory, I would put them on seperate servers.
Post #1481955
Posted Wednesday, August 07, 2013 10:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
SQLSteve (8/7/2013)
amitsingh308 (8/7/2013)
Because it effects the execution of other applications


Reduce the max memory allowed for SQL.

Personally.. If other applications require large amounts of memory, I would put them on seperate servers.

+10

You shouldn't have mixed database server with other apps on the server, one OR the other, NOT both..

CEWII
Post #1481961
Posted Wednesday, September 11, 2013 8:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 12:11 PM
Points: 19, Visits: 80
This sounds like you are running other applications on the same server as SQL server. This is generally not considered best practice because of the way that SQL server works by using as much of the memory cache as possible to speed things up.

I did however work at a company that had to work within the same constraints as you in so far as having to run multiple applications from the same server. The best thing you can do is make sure that you set the max memory usage in SQL server so that it doesn't take over memory that should be used by other applications.

In closing, the options you have are either limiting the max memory usage in the settings of SQL Server, increasing the physical memory (this will eventually run into the same problem if you don't limit the max memory), or move the other applications to another box.
Post #1493700
Posted Friday, September 13, 2013 1:27 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:32 AM
Points: 215, Visits: 444
It sounds to me that the memory is set to default on the database engine and in turn SQL will take all the memory on the machine if not set under the SQL engine.
If the machine has 64GB give 56 to 60 to SQL and 4 to 8 for the OS.
Do you know where to set the memory on SQL?


MCSA SQL Server 2012
Post #1494705
Posted Monday, October 14, 2013 4:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 8:34 AM
Points: 43, Visits: 178
My recommendations:

1. Change the default value for max memory in SQL Server.
2. Leave appropriate memory for the OS too.
3. Check if there is memory presssure for SQL Server (If Yes, Increase the memory but also remember point 2).
4. If possible move the application to a different server (may increase the performance of the application).
5. If there's no memory pressure on the database and by limiting the max memory settings performance is not affected then you may leave the settings to that , Senior members please advise.
Post #1504395
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse