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 Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 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 (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220021 Visits: 46279
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 Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

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

Group: General Forum Members
Points: 220021 Visits: 46279
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1285
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
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23526 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
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3332 Visits: 919
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
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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