0

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 enter image description here

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 enter image description here

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
Improve this question
9
  • Apparently this can be caused by a creating too many prepared statements. Look at the Java code first. As preparing statements is more common there, and the .NET Connection Pool should clean up prepared commands. Read: learn.microsoft.com/en-us/archive/blogs/sqlcat/…
    – David Browne - Microsoft
    Sep 8, 2021 at 19:02
  • yeah,I suspected that it was caused by prepared statements，but it was not . I create an extended event to findout events for sp_prepare and sp_unprepare event Refer to this blog : blog.dbi-services.com/… .but in my case, prepare_sql:unprepare_sql is 1:1
    – kaiqiang zhang
    Sep 9, 2021 at 3:30
  • Did you also track sp_prepexec and sp_prepececrpc?
    – David Browne - Microsoft
    Sep 9, 2021 at 7:02
  • 1
    there is there event (exec_prepared_sql,prepare_sql,unprepare_sql) , and i think prepare_sql event include sp_prepare , sp_prepexec and sp_prepexecrpc
    – kaiqiang zhang
    Sep 9, 2021 at 8:15
  • Yes, I believe that's correct. How quickly do these clerks grow over time? Do you have long-lived connections?
    – David Browne - Microsoft
    Sep 9, 2021 at 12:54

1 Answer 1

Reset to default
0

Can you pull the resources on the server and the database size? My guess is that this server is underpowered for its use case

I'd also recommend checking out this link by brent ozar: https://www.brentozar.com/archive/2014/05/connections-slow-sql-server-threadpool/

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.