August 7, 2013 at 12:10 am
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 2Page 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 TypeAllocated memory(KB)Virtual Memory Reserved(KB)Virtual Memory Committed(KB)AWE Memory Allocated(KB)Shared Memory Reserved(KB)Shared Memory Committed(KB)
CACHESTORE_SQLCP6,502,73600000
OBJECTSTORE_LOCK_MANAGER654,936262,144262,144000
MEMORYCLERK_SQLOPTIMIZER72,04000000
MEMORYCLERK_SOSNODE70,36800000
CACHESTORE_OBJCP64,79200000
CACHESTORE_PHDR54,70400000
USERSTORE_SCHEMAMGR21,42400000
USERSTORE_TOKENPERM20,20800000
MEMORYCLERK_SQLGENERAL15,77600000
OBJECTSTORE_LBSS11,39200000
MEMORYCLERK_SQLSTORENG10,12816,12816,128000
USERSTORE_DBMETADATA9,12800000
OBJECTSTORE_SNI_PACKET9,04000000
MEMORYCLERK_XE5,53600000
CACHESTORE_SYSTEMROWSET3,18400000
MEMORYCLERK_SQLQUERYEXEC2,32000000
USERSTORE_OBJPERM1,14400000
MEMORYCLERK_SQLCONNECTIONPOOL1,08000000
MEMORYCLERK_SQLBUFFERPOOL91267,190,78462,377,576000
MEMORYCLERK_SQLSERVICEBROKER68800000
MEMORYCLERK_SNI55200000
OBJECTSTORE_SERVICE_BROKER42400000
CACHESTORE_XPROC19200000
MEMORYCLERK_BHF15200000
USERSTORE_SXC12000000
MEMORYCLERK_SQLUTILITIES96240240000
CACHESTORE_TEMPTABLES8000000
CACHESTORE_BROKERTBLACS5600000
CACHESTORE_BROKERREADONLY5600000
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT4800000
CACHESTORE_FULLTEXTSTOPLIST3200000
CACHESTORE_CONVPRI3200000
MEMORYCLERK_FULLTEXT2400000
CACHESTORE_VIEWDEFINITIONS1600000
MEMORYCLERK_HOST1600000
MEMORYCLERK_SQLXP1600000
CACHESTORE_STACKFRAMES1600000
OBJECTSTORE_SECAUDIT_EVENT_BUFFER1600000
CACHESTORE_NOTIF1600000
CACHESTORE_EVENTS1600000
CACHESTORE_XMLDBTYPE800000
CACHESTORE_BROKERRSB800000
CACHESTORE_XMLDBATTRIBUTE800000
MEMORYCLERK_SQLHTTP800000
CACHESTORE_XMLDBELEMENT800000
MEMORYCLERK_SQLCLR800000
CACHESTORE_BROKERTO800000
CACHESTORE_BROKERKEK800000
CACHESTORE_BROKERUSERCERTLOOKUP800000
CACHESTORE_BROKERDSH800000
MEMORYCLERK_XE_BUFFER04,2244,224000
MEMORYCLERK_FULLTEXT_SHMEM0000288288
MEMORYCLERK_SQLSOAPSESSIONSTORE000000
MEMORYCLERK_SQLQERESERVATIONS000000
MEMORYCLERK_SQLQUERYCOMPILE000000
MEMORYCLERK_SQLXML000000
MEMORYCLERK_SQLSOAP000000
MEMORYCLERK_TRACE_EVTNOTIF000000
MEMORYCLERK_SQLQUERYPLAN000000
MEMORYCLERK_QSRANGEPREFETCH000000
MEMORYCLERK_SQLCLRASSEMBLY000000
Can anyone guide how can I reduce the uses of memory.
August 7, 2013 at 2:09 am
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
August 7, 2013 at 2:35 am
Because it effects the execution of other applications
August 7, 2013 at 3:07 am
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
August 7, 2013 at 10:08 am
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.
August 7, 2013 at 10:24 am
SQLSteve (8/7/2013)
amitsingh308 (8/7/2013)
Because it effects the execution of other applicationsReduce 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
September 11, 2013 at 8:10 am
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.
September 13, 2013 at 1:27 pm
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?
MCSE SQL Server 2012\2014\2016
October 14, 2013 at 4:47 am
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.
September 14, 2017 at 5:09 am
Hello
Have you check Locked Pages in Memory?
A
September 15, 2017 at 6:49 am
September 15, 2017 at 8:12 am
amitsingh308 - Wednesday, August 7, 2013 12:10 AMHello Everyone,I have a database which size is 250 GB and it contains the information about 70 Lak users with photograph.
How big is the average photograph in bytes and can they be stored outside the database? I'd assume that only a small portion of your queries actually need the photograph data returned to the application.
September 17, 2017 at 7:42 am
Please note: 4 year old thread
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply