Team,

We are running a Mission Critical Application on SQL Server 2012 SP2(11.0.5058) which is configured on Always ON Synchronous mode. Offlate due to heavy development work, Application team have come up with stating memory issues. I have analysed all the areas and everything looks normal. Please suggest if we have to patch the latest CU4 for SQL Server 2012 SP2. 

The checks were performed on the below areas:

- Errorlog, System Logs - No errors reported.

- There are close to 8 Databases hosted on the instances which are all configured for AlwaysON. SQL Server is running on VM Infrastructure and the total physical memory allocated is 96GB out of which SQL is capped for 92GB. 

-The Page Life Expectancy is healthy and is showing a greater number. There are no Signal waits either or pending memory grants. 

- The writes are more than reads for one of the databases which is flagged with application team. There are no blockings & Deadlocks.

Please suggest me the future course of action and your inputs are much appreciated.

Best Regards,

Sharath 

Application team have come up with stating memory issues.

Hello Sharath,

What for memory issues do you mean? It is quite normal that SQL Server allocate as much memory as it's need and as it gets. SQL Server releases the memory only by OS request.

You can limit the max Memory used by SQL Server to have enough memory left for the OS, see Server Memory Server Configuration Options and Effects of min and max server memory

Hi Sharath,

You need to ask the application team what they mean by memory issue and ask them to show stats.

However, I feel you could give bit more memory for the Operating System, I see now you have hardly given 4 GB. If you are going by this article https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/, then 4 Gb is too less, I guess.

How much is the Available Mbytes ? What other applications are running on the server?

Thanks for the responses. Yes the available memory is still GB overall. As I stressed the fill factor is set to default and the writes are more than reads for the database. Here is some more data:

index_type_desc       avg_fragmentation_in_percent fragment_count page_count
NONCLUSTERED INDEX 72.6241117462794 60735          82043
NONCLUSTERED INDEX 40.1576357672215 28623          65848
CLUSTERED INDEX 24.070340779121                     571652               2302107

We are running housekeeping jobs in the form of Rebuild Index (runs on Sunday), Reorganize Index(runs everyday) and also update Stats(everyday). 

Best Regards,
Sharath

Hello,

Nothing you're referring to so far deals with a memory leak. Could you add more information or change the title?

Currently, we have no idea what your actual issue is... is it slow? Is AOAGs not working? What's the actual issue?

-Sean

Actual issue is- Application team have reported a memory leak and their builds have significantly slowed down. They suspect that its Database memory leak. However I have verified from database end and gave the above inputs. 

The AOAGs are good. I was looking for any pointers whether there are any bugs which is related to memory leaks in SQL Server 2012.  I know all of them are addressed with SQL Server 2012 SP2. 

skrishnamu008, 

Its common that Application team would generalize the performance issue as "MEMORY issue" in most cases. But you may ask more details on the performance issue that they are facing. It may be somewhere else or it may not be even a concern. I would suggest you to talk to Application team and get Problem Statement in detail, that would really help you.

Actual issue is- Application team have reported a memory leak and their builds have significantly slowed down. They suspect that its Database memory leak. However I have verified from database end and gave the above inputs. 

The AOAGs are good. I was looking for any pointers whether there are any bugs which is related to memory leaks in SQL Server 2012.  I know all of them are addressed with SQL Server 2012 SP2. 

Application team has NO idea about SQL Server do they ? And to say SQL Server has memory leak you have to actually prove it did they showed any proof. Its common for application team to say SQL Server is leaking memory because they are unaware about fact by default SQL Server would take as much memory as possible and would release when SQLOS asks it to do so. This might give sign that it is leaking memory BUT IT IS NOT.

As you already said AOAG is working fine so I am presuming there is nothing much to worry. To monitor memory usage in SQL Server 2012 you can use below counters

SQL Server: Memory Manager-- Target Server Memory (KB)

SQL Server: Memory Manager--Total Server Memory (KB)

SQL Server: Memory Manager- Free Memory (KB)

SQL Server: Memory Manager--Database Cache Memory (KB)

Application team have reported a memory leak and their builds have significantly slowed down.

Their builds? So the build server (TFS?) is running on the same machine as the SQL Server? Best practice is to run SQL Server on a dedicated Server, but if it shares the resources on one machine with other server applications, like TFS, then you should limit the max memory setting for SQL Server, see my first post.

Thanks All for your responses. I had shared my analysis with application team and they are quite happy with my response. 

Just wanted to get thoughts from this forum as well. Appreciate all your inputs.

Best Regards,

Sharath

@Sharath Good job so far.

Your next step should be to analyze the application workload to identify expensive or long-running queries. You can do this simply with the built-in reports in SSMS.  Start with the Performance -Top Queries By Total CPU Time report. They may have no clue what queries are expensive or how often they are running them.

In addition to the built-in reports, you can add the Performance Dashboard reports

Thanks David. Have shared the analysis with them to have it for their internal review.

Performance Dashboards certainly helps!

Cheers!

Sharath

Thanks David. Have shared the analysis with them to have it for their internal review.

Performance Dashboards certainly helps!

Cheers!

Sharath

• Proposed as answer by Malisaqin Friday, February 06, 2015 1:29 PM
• Marked as answer by Lydia ZhangMicrosoft contingent staff, Moderator Saturday, February 07, 2015 10:42 AM

Rechecking , if you have increased the OS memory. For 64 bit system we generally keep (physical memory) / 4 and rest assign to SQL server max memory, keeping only 4 GB for OS will decrease OS performance and this will lead to memory leak from SQL Server.