SQL Server Eating all memory on Server

Hello,

My SQL server 2008 R2 is currently running on a server with 16GB RAM , and its currently using almost all the 16GB Ram and at some point it does use all the memory and causes the database not to be available. The Minimum and maximum server memory have been set to
0 -Min
13500 MB  - Max
on the server properties page

Yet sql server is currently running on 15738 MB, exceeding the Max memory setting on the database server and atimes reaches 16000 MB and the database server becomes unavailable.

Who can point me to what is causing this pls?

My investigation shows that it might be COM ,extended stored procs, non-shared DLLs etc but how do I make them not to eat up all the memory on the server?

Thanks


marvo2010Asked:
Who is Participating?
 
marvo2010Connect With a Mentor Author Commented:
Hello All,

Thanks so much for the quick attention given to this question today. I do appreciate it immensely.
However I have been able to solve the problem and will like to share the solution which was very really simple.
The database server is running on sql server 2008 R2 and the database makes memory allocation to its buffer pool automatically even if there is no need to do that because SQL server prefers to write to the cache than to disk so it makes sure it has enough memory at all times. Given that the max server memory is at 16GB and then the SQL server 2008 R2 server max memory have been set to 13.5GB, the slightest WRITE and UPDATE causes SQL server to go for more memory from the Server and therefore maxing out the server memory.The solution was therefore to reduce the Max server memory of the database server , using

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 10000;
GO
RECONFIGURE;
GO

which reduces the max memory and also the tendency for the database to max-out the server memory outlay. This can also be done using SSMS.

Thanks to all that tried to help. Much appreciated .

Thanks
0
 
LowfatspreadCommented:
can you clarify ...

has the instance been started with those limits inplace, or have you just altered them to those limits?

the extended stored procedures etc... are these standard sql server supplied ones , or ones that come with third party (or your own) applications?
0
 
marvo2010Author Commented:
Hello ,

The instance was started with the limits in place .

My mention of extended stored procedures etc . . . is that microsoft have said that these have unreserved memory usage and therefore does not adhere to memory caps. I am wondering how I can track and find out what ever is causing the problem.

Thanks
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
TempDBACommented:
What all processes are running during the time. Is this the memory usage you are talking about comes from task manager?
Since you already said you configure the max memory setting, just wanted to know whether the configuration was made after the service was up or before that?
You can also check the memory used by various process and provided by various ways. Can you post the result of the following query?

 1. DBCC MEMORYSTATUS
 
 
 2. SELECT TEXT,
        query_plan,
        requested_memory_kb,
        granted_memory_kb,
        used_memory_kb
 FROM   sys.dm_exec_query_memory_grants emg
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)
 
 CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle)
 ORDER BY
        emg.requested_memory_kb DESC
0
 
marvo2010Author Commented:
Hello,
I have run DBCC MEMORYSTATUS  and these are the result:

Memory Manager      KB
VM Reserved      16948392
VM Committed      14299088
Locked Pages Allocated      0
Reserved Memory      1024
Reserved Memory In Use      0


Memory node Id = 0      KB
VM Reserved      16944552
VM Committed      14295400
Locked Pages Allocated      0
MultiPage Allocator      77984
SinglePage Allocator      557424

MEMORYCLERK_SQLBUFFERPOOL (node 0)      KB
VM Reserved      16809984
VM Committed      14172160
Locked Pages Allocated      0
SM Reserved      0
SM Committed      0
SinglePage Allocator      0
MultiPage Allocator      408

And the second Query Result:


TEXT      query_plan      requested_memory_kb      granted_memory_kb      used_memory_kb
    SELECT TEXT,          query_plan,          requested_memory_kb,          granted_memory_kb,          used_memory_kb   FROM   sys.dm_exec_query_memory_grants emg          CROSS APPLY sys.dm_exec_sql_text(sql_handle)       CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle)   ORDER BY          emg.requested_memory_kb DESC       <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.1600.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="&#xD;&#xA;&#xD;&#xA;SELECT TEXT,&#xD;&#xA;        query_plan,&#xD;&#xA;        requested_memory_kb,&#xD;&#xA;        granted_memory_kb,&#xD;&#xA;        used_memory_kb&#xD;&#xA; FROM   sys.dm_exec_query_memory_grants emg&#xD;&#xA;        CROSS APPLY sys.dm_exec_sql_text(sql_handle) &#xD;&#xA; &#xD;&#xA; CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle)&#xD;&#xA; ORDER BY&#xD;&#xA;        emg.requested_memory_kb DESC " StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0474044" StatementEstRows="100.99" StatementOptmLevel="FULL" QueryHash="0xFEAFE7040FB399D1" QueryPlanHash="0x8FF45A583DE7E337" StatementOptmEarlyAbortReason="GoodEnoughPlanFound"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /><QueryPlan CachedPlanSize="32" CompileTime="114" CompileCPU="114" CompileMemory="288"><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="100.99" EstimateIO="0" EstimateCPU="0.000422138" AvgRowSize="5157" EstimatedTotalSubtreeCost="0.0474044" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="requested_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="granted_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="used_memory_kb" /><ColumnReference Table="[FNGETSQL]" Column="text" /><ColumnReference Table="[FNGETQUERYPLAN]" Column="query_plan" /></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="plan_handle" /></OuterReferences><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="100.99" EstimateIO="0" EstimateCPU="0.000422138" AvgRowSize="1117" EstimatedTotalSubtreeCost="0.0468811" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="requested_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="granted_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="used_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="plan_handle" /><ColumnReference Table="[FNGETSQL]" Column="text" /></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="sql_handle" /></OuterReferences><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="100.99" EstimateIO="0" EstimateCPU="0.0090597" AvgRowSize="101" EstimatedTotalSubtreeCost="0.0463578" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="requested_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="granted_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="used_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="plan_handle" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="sql_handle" /></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="session_id" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="request_id" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="plan_handle" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="sql_handle" /></OuterReferences><RelOp NodeId="3" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="100" EstimateIO="0.0112613" EstimateCPU="0.00113655" AvgRowSize="107" EstimatedTotalSubtreeCost="0.012498" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="session_id" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="request_id" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="requested_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="granted_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="used_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="plan_handle" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="sql_handle" /></OutputList><MemoryFractions Input="1" Output="1" /><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="0"><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="requested_memory_kb" /></OrderByColumn></OrderBy><RelOp NodeId="4" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="100" EstimateIO="0" EstimateCPU="0.000100157" AvgRowSize="107" EstimatedTotalSubtreeCost="0.000100157" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="session_id" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="request_id" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="requested_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="granted_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="used_memory_kb" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="plan_handle" /><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="sql_handle" /></OutputList><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="session_id" /></DefinedValue><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="request_id" /></DefinedValue><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="requested_memory_kb" /></DefinedValue><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="granted_memory_kb" /></DefinedValue><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="used_memory_kb" /></DefinedValue><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="plan_handle" /></DefinedValue><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="sql_handle" /></DefinedValue></DefinedValues><Object Table="[DM_EXEC_QE_GRANTSINFO]" /></TableValuedFunction></RelOp></Sort></RelOp><RelOp NodeId="5" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="21.6739" EstimateIO="0" EstimateCPU="0.000148" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0248002" Parallel="0" EstimateRebinds="99" EstimateRewinds="1.23379e-005"><OutputList /><Filter StartupExpression="0"><RelOp NodeId="6" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="100" EstimateIO="0" EstimateCPU="0.000100157" AvgRowSize="83" EstimatedTotalSubtreeCost="0.0100002" Parallel="0" EstimateRebinds="0" EstimateRewinds="99"><OutputList><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="session_id" /><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="request_id" /><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="plan_handle" /><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="sql_handle" /></OutputList><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="session_id" /></DefinedValue><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="request_id" /></DefinedValue><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="plan_handle" /></DefinedValue><DefinedValue><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="sql_handle" /></DefinedValue></DefinedValues><Object Table="[DM_EXEC_QE_GRANTWAITERS]" /></TableValuedFunction></RelOp><Predicate><ScalarOperator ScalarString="DM_EXEC_QE_GRANTSINFO.[session_id]=DM_EXEC_QE_GRANTWAITERS.[session_id] AND DM_EXEC_QE_GRANTSINFO.[request_id]=DM_EXEC_QE_GRANTWAITERS.[request_id] AND DM_EXEC_QE_GRANTSINFO.[sql_handle]=DM_EXEC_QE_GRANTWAITERS.[sql_handle] AND DM_EXEC_QE_GRANTSINFO.[plan_handle]=DM_EXEC_QE_GRANTWAITERS.[plan_handle]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="session_id" /></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="session_id" /></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="request_id" /></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="request_id" /></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="sql_handle" /></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="sql_handle" /></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="plan_handle" /></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="[DM_EXEC_QE_GRANTWAITERS]" Column="plan_handle" /></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp></NestedLoops></RelOp><RelOp NodeId="8" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-006" AvgRowSize="1059" EstimatedTotalSubtreeCost="0.000101147" Parallel="0" EstimateRebinds="90.9406" EstimateRewinds="9.04938"><OutputList><ColumnReference Table="[FNGETSQL]" Column="text" /></OutputList><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[FNGETSQL]" Column="text" /></DefinedValue></DefinedValues><Object Table="[FNGETSQL]" /><ParameterList><ScalarOperator ScalarString="DM_EXEC_QE_GRANTSINFO.[sql_handle]"><Identifier><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="sql_handle" /></Identifier></ScalarOperator></ParameterList></TableValuedFunction></RelOp></NestedLoops></RelOp><RelOp NodeId="10" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-006" AvgRowSize="4083" EstimatedTotalSubtreeCost="0.000101147" Parallel="0" EstimateRebinds="90.9406" EstimateRewinds="9.04938"><OutputList><ColumnReference Table="[FNGETQUERYPLAN]" Column="query_plan" /></OutputList><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[FNGETQUERYPLAN]" Column="query_plan" /></DefinedValue></DefinedValues><Object Table="[FNGETQUERYPLAN]" /><ParameterList><ScalarOperator ScalarString="DM_EXEC_QE_GRANTSINFO.[plan_handle]"><Identifier><ColumnReference Table="[DM_EXEC_QE_GRANTSINFO]" Column="plan_handle" /></Identifier></ScalarOperator></ParameterList></TableValuedFunction></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>      1024      1024      0

I hope this is what you wanted.

Many Thanks

0
 
TempDBACommented:
Strange. The second query says you are running nothing except the query which was requested earlier in the server. Is there really nothing running there?

The first query result looks good. Can you run the query on this dmv's too?
sys.dm_os_sys_memory
sys.dm_os_process_memory

and plz print the result in tabular form.
0
 
marvo2010Author Commented:
Hello TempDBA,

I ahve runthe two queries and I have attached the results to an excel sheet where I have labelled the tabs for each dmv.

thanks forExpertExchange.xlsx
0
 
marvo2010Author Commented:
I fixed the problem using my own solution which I have also made available here.
0
 
Anthony PerkinsCommented:
Right, that is why Lowfatspread asked if you had successfully set the max memory.  Obviously you had not.

Incidentally, the only time you would want to do this is when you are sharing SQL Server (against recommended best practices) on the same server with other applications.  Othewise, by limiting the memory it uses all you have achieved is cripple SQL Server performance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.