Application becomes unresponsive after SQL 2008 consumes server memory.

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
tobe1424Asked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Is the SQL Server 32bit or 64bit version?
What's the SQL Server edition (Express, Standard, Enterprise, ...)?
0
 
tobe1424Author Commented:
vitor, forgot to mention:

SQL Server 2008 R2 (x64) Enterprise edition

thx

t
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
tobe1424Author Commented:
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
 
David Johnson, CD, MVPOwnerCommented:
that would tend to imply that you have a memory leak somewhere.. i.e. not closing connections
0
 
tobe1424Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
tobe1424Author Commented:
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
 
tobe1424Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
tobe1424Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
tobe1424Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
tobe1424Author Commented:
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
 
tobe1424Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
tobe1424, do you have any feedback for us?
Cheers
0
 
tobe1424Author Commented:
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
 
tobe1424Author Commented:
Thanks for all your comments and feedback!
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.