Solved

SQL Server Eating all memory on Server

Posted on 2011-09-26
9
514 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
9 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:marvo2010
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 25

Expert Comment

by:TempDBA
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I fixed the problem using my own solution which I have also made available here.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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

Don't lose your head updating email signatures!

Do your end users still have the wrong email signature? Do email signature updates bore you or fill you with a sense of dread? You can make this a whole lot easier on yourself by trusting an Exclaimer email signature management solution. Over 50 million users do...so should you!

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now