Application becomes unresponsive after SQL 2008 consumes server memory.

Is the SQL Server 32bit or 64bit version?
What's the SQL Server edition (Express, Standard, Enterprise, ...)?

vitor, forgot to mention:

SQL Server 2008 R2 (x64) Enterprise edition

thx

t

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.

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.

that would tend to imply that you have a memory leak somewhere.. i.e. not closing connections

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?

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

Select allOpen 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

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.

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

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

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.

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?

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?

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 ?

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

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.

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?

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.

tobe1424, do you have any feedback for us?
Cheers

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

Thanks for all your comments and feedback!