Solved

SQL Server Eating all memory on Server

Posted on 2011-09-26
9
532 Views
Last Modified: 2012-05-12
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


0
Comment
Question by:marvo2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36598691
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
 

Author Comment

by:marvo2010
ID: 36598713
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
 
LVL 25

Expert Comment

by:TempDBA
ID: 36598768
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:marvo2010
ID: 36598836
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
 
LVL 25

Expert Comment

by:TempDBA
ID: 36599043
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
 

Author Comment

by:marvo2010
ID: 36599178
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
 

Accepted Solution

by:
marvo2010 earned 0 total points
ID: 36708523
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
 

Author Closing Comment

by:marvo2010
ID: 36898835
I fixed the problem using my own solution which I have also made available here.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36711916
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question