SQL Server Version : SQL Server 2017(RTM-CU21) on Linux
Runing environment : kernel 4.19,Docker 18.09
Docker Image : docker pull mcr.microsoft.com/mssql/server:2017-CU21-ubuntu-16.04
Problem Description : there is high SQLCONNECTIONPOOL consumption ,but why ?
select * from sys.dm_os_memory_clerks order by pages_kb desc
check current connections , there is only 95 user connenctions , but none of them seem to take up a lot of memory. I have tried to kill all user connnetions , but did not help reduce SQLCONNECTIONPOOL size
select login_time,host_name,program_name,memory_usage from sys.dm_exec_sessions where is_user_process = 1 order by memory_usage desc
I run a test instance with 2c8g, there is only three long-lived connections for health check using go-mssqldb .and after a week , the connection pool grow up to about 2 g . i tried to release memory by setting "max server memory" to 2g , the instance crash with error "FAIL_PAGE_ALLOCATION"
error message:
2021-09-15 16:21:10.76 spid56 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2021-09-15 16:21:10.79 spid56 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2021-09-15 16:21:10.80 spid56 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2021-09-15 16:22:26.34 spid56 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Accepted publickey for root from 10.244.158.219 port 41592 ssh2: RSA SHA256:T909VAKw8D9hcZfVLXJeURfgWJB2BqAaUTA3G3V6T54
Accepted publickey for root from 10.244.158.219 port 41594 ssh2: RSA SHA256:T909VAKw8D9hcZfVLXJeURfgWJB2BqAaUTA3G3V6T54
2021-09-15 16:23:16.44 spid56 Configuration option 'max server memory (MB)' changed from 2147483647 to 2048. Run the RECONFIGURE statement to install.
2021-09-15 16:24:02.66 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Failed to execute SystemTaskEntryPoint: unable to allocate MsqlXactManager2021-09-15 16:24:02.66 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2021-09-15 16:24:02.66 Server
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 3122057216
Available Virtual Memory 140733193256960
Available Paging File 3122057216
Working Set 4294967296
Percent of Committed Memory in WS 100
Page Faults 0
System physical memory high 1
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
2021-09-15 16:24:02.68 Server
Memory Manager KB
---------------------------------------- ----------
VM Reserved 9777632
VM Committed 2097144
Locked Pages Allocated 0
Large Pages Allocated 0
Emergency Memory 1024
Emergency Memory In Use 8
Target Committed 2097152
Current Committed 2097144
Pages Allocated 1955224
Pages Reserved 0
Pages Free 53496
Pages In Use 2002544
Page Alloc Potential -10248
NUMA Growth Phase 2
Last OOM Factor 1
Last OS Error 0
2021-09-15 16:24:02.66 spid43s Error: 701, Severity: 17, State: 123.
2021-09-15 16:24:02.66 spid43s There is insufficient system memory in resource pool 'internal' to run this query.
Failed to execute SystemTaskEntryPoint: unable to allocate MsqlXactManager2021-09-15 16:24:02.66 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2021-09-15 16:24:02.66 spid51 Error: 701, Severity: 17, State: 130.
2021-09-15 16:24:02.66 spid51 There is insufficient system memory in resource pool 'default' to run this query.
2021-09-15 16:24:02.78 spid51 Error: 701, Severity: 17, State: 130.
2021-09-15 16:24:02.78 spid51 There is insufficient system memory in resource pool 'default' to run this query.
2021-09-15 16:24:02.66 spid56 Error: 701, Severity: 17, State: 130.
2021-09-15 16:24:02.66 spid56 There is insufficient system memory in resource pool 'default' to run this query.
Failed to execute SystemTaskEntryPoint: unable to allocate MsqlXactManagerFailed to execute SystemTaskEntryPoint: unable to allocate MsqlXactManagerFailed to start system task System Task
Failed to execute SystemTaskEntryPoint: unable to allocate