Click here to monitor SSC
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
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47177 Visits: 44356
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
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 224
Because it effects the execution of other applications
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47177 Visits: 44356
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
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 1146
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
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 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 (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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.
lkennedy76
lkennedy76
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 755
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 (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

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