sql-serverperformancememory-leaksmemory-poolresource-governor

SQL Server internal/default resource pool increasing over time

Problem

I have an SQL Server 2017 database deployed on a customer VM with a single database installed. The database has a single web application that connects to it with around 100 active users at peak times. For the past few months the customer has had to restart the sql server instance after 5 days of use because the application starts to show signs of performance degradation (sometimes a page takes a second to load then the same page can take 15 seconds). I have been monitoring the query store for several months and making performance optimisations to queries, however I still cannot prevent the database from experience performance issues towards the end of the week.

When the issues are present (after 5 days uptime) the server shows high cpu and memory useage although the frequency of queries and the types of queries running are exactly the same throughout the week. After looking at the query store while experiencing the issues I do not see any long running queries and nothing is hitting higher on the CPU or Memory analysis charts than I see on the days when the application is running fine.

One thing I notice is that the internal/default resource pools increase in usage throughout the week. Shortly after restart the resource pool useage looks low: enter image description here

After 5 days time the resource pools look a lot higher: enter image description here

I notice that when I run queries the resource pool increases while queries are running but memory is always released after the queries have finished running, it would appear that this isn't happening properly on the customer instance otherwise I don't understand why the resource pool would increase over time.

Questions

Is the resource pool usage increasing over time something that could cause performance issues?

What would cause the resource pool memory not to be released?

The things I have implemented to alleviate the performance issues we have been facing are as follows:

  1. Turned on READ_COMMITTED_SNAPSHOT isolation, this helped mitigate issues with the application appearing unresponsive due to locking.
  2. Set max server memory to ensure it isn't starving the OS.
  3. Spent months optimising a bunch of the queries to reduce the CPU duration and logical reads and also added indexes which helped a fair bit but hasn't resolved all of my issues.
Solution

  • I believe I have found the culprit to this issue. The sqljdbc4.2.jar that is being used to connect to the database and execute queries has a memory leak. After running a load test of the application and monitoring sys.dm_os_memory_clerks I can see MEMORYCLERK_SQLCONNECTIONPOOL pages_kb increasing as more and more queries are executed against the database. This also increases the 'used memory' in the resource pool as a side effect.

    After updating the jdbc library to a later version the MEMORYCLERK_SQLCONNECTIONPOOL did not increase over time.

    <dependency>
    <groupId>com.microsoft.sqlserver</groupId>
     <artifactId>mssql-jdbc</artifactId>
     <version>8.4.1.jre8</version>
</dependency>