Solved

Application becomes unresponsive after SQL 2008 consumes server memory.

Posted on 2016-10-05
22
138 Views
Last Modified: 2016-11-01
hello,

I have an application running on a windows 2008 r2 server which connects to a back end also running windows 2008 r2 along with a sql server 2008 r2 instance.

The server has 24gb of ram and our application vendor says that we shouldn't need more than 16gb of ram. So within sql, i went to the server properties of the instance and i set the 'maximum server memory' to 16384 mb

I have to bounce the server once a week via iLO port because all of the physical memory ends up being consumed. however, once i set the max server mem to 16, i also need to reboot the server because the application becomes unstable and freezes. it seems like something within sql is asking for more memory until it can't consume any more and then the server crashes or the application hangs.

Additionally, I have about 10 databases which are just sitting within the instance. Only one is used with about 55 active connections.

I have limited sql training and was wondering if anyone can help me decipher this weird 'memory leak' ?

many thanks in advance

t

server properties
0
Comment
Question by:tobe1424
[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
  • 11
  • 8
  • 2
  • +1
22 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41830032
Is the SQL Server 32bit or 64bit version?
What's the SQL Server edition (Express, Standard, Enterprise, ...)?
0
 

Author Comment

by:tobe1424
ID: 41830043
vitor, forgot to mention:

SQL Server 2008 R2 (x64) Enterprise edition

thx

t
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41830075
You can try to give 20GB to SQL Server and leave 4GB to OS (it should be enough for a 64bit system).
Also, is the server dedicated only for SQL Server or do you have any other applications installed? Mind that SSAS, SSIS and SSRS are also "other applications" since they fight for the same resources as SQL Server engine.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:tobe1424
ID: 41830228
thanks for the feedback. yes this is a dedicated server and no ssas ssis ssrs.

Im afraid that even dialing in 20gb will not fix the issue. But i can try.

The db is about 120gb in size (raw) when the server is bounced the memory consumption drops to 7 gb. afterwards, the memory utilization climbs about .5-1gb per day until the server or app in this case since i implemented the max memory cap.
0
 
LVL 82

Expert Comment

by:David Johnson, CD, MVP
ID: 41830803
that would tend to imply that you have a memory leak somewhere.. i.e. not closing connections
0
 

Author Comment

by:tobe1424
ID: 41830904
I see.

'Memory leak' is the exact title of the ticket I have opened with the vendor. But they claim it's an issue with sql not the application.

I ran a query that shows active connection and they seem to be on par with the connections on the terminal services session host.

Aside from connection, do you think there is something else I can look into?
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41830967
May be this will help.

Max Server Memory only controls the memory allocated to the buffer pool for cache. This doesn't affect the amount of memory complete SQL Server can use.


Try with

DBCC MEMORYSTATUS 

SELECT 
* FROM 
sys.dm_os_memory_clerks

Open in new window

Multipage allocations occur outside of the buffer pool and would cause the process to use more memory. However, there are some allocations outside of the buffer pool that aren't managed by a memory clerk but contribute to the working set allocation.

For more granular details, pls refer Jonathan's blog post.

http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Memory+Allocation/default.

You need to check number of this in this. please refer Microsoft post on this.

https://social.msdn.microsoft.com/Forums/en-US/264ea932-7155-465a-98d2-d88006f401c8/sql-server-2008-r2-consuming-all-available-memory?forum=sqldatabaseengine
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41831261
The db is about 120gb in size (raw)
Ok, that's a good information. Depending on how the application deals with data you might need in extreme occasions the same value for memory. With that said, ideally you should have 120GB RAM for SQL Server but you might try with half (60GB) to see if it enough for the application performance.

Database engines are more memory consumers than CPU consumers so always give the maximum memory you can.
0
 

Author Comment

by:tobe1424
ID: 41833659
Thanks again for the feedback. I had to prepare for a hurricane which is why i am late to respond.

Pawan, I ran the dbcc query and it returned plent of information. However, here is the memory manager results which is the first to display

Memory Mgr       KB
VM Reserved      25784120
VM Committed      590732
Locked Pages Allocated      6047936
Reserved Memory      1024
Reserved Memory In Use      0


--


victor, I apologize for the incorrect information i provided. I gathered the properties from the root folder rather than drilling down to the primary db file. The actual size of the primary database file is 3GB. The prod db is named "WI"

thanks again

t
0
 

Author Comment

by:tobe1424
ID: 41836852
I talked to one of my dba's in a different office. he recommended to run this stored procedure periodically so that the cache/swap memory is cleared. It seems to drop my memory utilization. although not dramatically. But enough to keep it hovering around 12-13 gb. At the same time. I increased the sql instance buffer mem to 18gb.

--

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[USR_SP_CLEAN_CACHE_MEMORY]    Script Date: 10/10/2016 09:28:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--USE Master
--GO
ALTER Procedure [dbo].[USR_SP_CLEAN_CACHE_MEMORY]
As
BEGIN
      BEGIN TRY
            EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
            EXEC sys.sp_configure N'max server memory (MB)', N'4096' --- Maximo (para hacerlo bajar del Task Manager)
            RECONFIGURE WITH OVERRIDE
            EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
            WAITFOR DELAY '00:01:50'
      END TRY
      BEGIN CATCH
      END CATCH
 
 
      -- Volvemos a establecer los valores normales seteados
      EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
      EXEC sys.sp_configure N'max server memory (MB)', N'16384'
      RECONFIGURE WITH OVERRIDE
      EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
END
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41836874
That's not a good thing to do. What you're really doing is to shrink the memory to 4GB and then increasing it again to 16GB. This will mess with the engine memory management.
0
 

Author Comment

by:tobe1424
ID: 41836953
I see. I have not set a maintenance task. I've only ran it twice to test the results. However, I am also not convinced that this is a good fix.

Any other recommendations or places to look or tweak?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41836963
To be honest this start to seems weird to me.
You have a 64bit system dedicated only for SQL Server and with 24GB RAM , 16GB reserved for SQL Server where the largest database is only 3GB size. In a normal condition I would say that's more than enough and you should not face any kind of performance issue.
I'm more and more inclined for an application design issue than a SQL Server issue. It's the application that hangs and not the SQL Server, right?
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41837120
Yes with Db so small, you should not face any issues. In your query (g)iven to you by your DBA) is setting max server memory (MB), which I had given you in my first comment.

I think root cause is something else. So you have some other heavy processes on your server ?
0
 

Author Comment

by:tobe1424
ID: 41837456
vitor, both the application and sql server (host) in general will hang. If the sql host memory is  consumed(all 24gb) then sql server crashes because the entire server hangs. therefore the app generates a database error when you attempt to connect to it. If i set the mem cap, when sql eats all of the memory configured, the app generates the same sql/db connection error message and/or the application hangs/freezes. some users receive an error while others simply experience an unresponsive app.

Pawan, with respect to other processes on the server, I can assure you there isn't anything else on this server other than sql server and a few other low overhead agents for backups, etc - nothing else.

lastly, in what negative way does this this newly created sp mess with the engine memory management?

thx

t
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41837929
in what negative way does this this newly created sp mess with the engine memory management?
In everything. Don't play with SQL Server memory management. That's a task for the engine itself. You just need to provide the minimum and the maximum memories so the engine knows with which amount of memory he can works it.

If you really need to clear the buffers then use the following commands:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE

But I still don't recommend you to clear the buffers. Those commands are usually used in testing environment only to have a clean base for restart the tests without restarting the SQL Server engine.
0
 

Author Comment

by:tobe1424
ID: 41838568
Thank you for the enlightening details.

Indeed I would like the sql mem mgmt handle itself as it should.

At this point, do you guys suggest there is something in the application itself that is causing the mem leak?
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41839565
At this point, do you guys suggest there is something in the application itself that is causing the mem leak?
That will be my guess. A database by itself wouldn't do that unless some heavy operation is being fired from somewhere. If there are no jobs in the SQL Server side or any other process outside the application then the only clue is the application itself.
There's any pattern for the memory leaking? i.e. it happens always at same time of the day or after some particular operation?
1
 

Author Comment

by:tobe1424
ID: 41840974
I have reached out to the developer again in effort for them to take a closer look. They have blamed sql several times. This time I underlined that all clues point back to the app.

I should be updating you sooner than later as they respond fairly quick.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41861690
tobe1424, do you have any feedback for us?
Cheers
0
 

Author Comment

by:tobe1424
ID: 41869260
Sorry for not getting back to you guys. The vendor says they are looking into it. According to them, they have a replica site(dev/test) identical to ours and the issue does not exist.

In any case, I cloned the server (p2v) and removed the front end(connections) and sql behaves as it should.

I suppose the issue resides within the application after all.

Thanks for your help
0
 

Author Closing Comment

by:tobe1424
ID: 41869261
Thanks for all your comments and feedback!
0

Featured Post

Industry Leaders: 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

This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

617 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