Solved

Application becomes unresponsive after SQL 2008 consumes server memory.

Posted on 2016-10-05
22
70 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
  • 11
  • 8
  • 2
  • +1
22 Comments
 
LVL 45

Expert Comment

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

Author Comment

by:tobe1424
Comment Utility
vitor, forgot to mention:

SQL Server 2008 R2 (x64) Enterprise edition

thx

t
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

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

Expert Comment

by:Vitor Montalvão
Comment Utility
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 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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
Comment Utility
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 45

Expert Comment

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

Author Comment

by:tobe1424
Comment Utility
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
Comment Utility
Thanks for all your comments and feedback!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
A procedure for exporting installed hotfix details of remote computers using powershell
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

762 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

12 Experts available now in Live!

Get 1:1 Help Now