SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


High Memory consuming by Database


High Memory consuming by Database

Author
Message
amitsingh308
amitsingh308
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 224
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86238 Visits: 45231
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, MVP, M.Sc (Comp Sci)
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


amitsingh308
amitsingh308
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 224
Because it effects the execution of other applications
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86238 Visits: 45231
If SQL is using too much memory, reduce max server 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

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


SQLAssAS
SQLAssAS
Say Hey Kid
Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)

Group: General Forum Members
Points: 690 Visits: 1218
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.
Elliott Whitlow
Elliott Whitlow
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9996 Visits: 5314
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
zachary.curtin
zachary.curtin
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1157 Visits: 904
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
sql-noob
sql-noob
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search