[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
SQL Server 2019 scalar function generates dump

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

SQL Server 2019 scalar function generates dump.


Applies to: SQL Server 2019 Enterprise Edition Always On AG (Cumulative Update 8)

Date created: December 20, 2020.
 

Problem Description.
 

Our SQL Server Always On Availability Group instance is running cumulative update 8 (15.0.4073.23), the latest cumulative update at this moment.

We started to see SQL Server database engine service restarting after generating the following text dump file on the server:

2020-12-09 12:37:56.39 spid1556    ***Stack Dump being sent to D:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\LOG\SQLDump0069.txt
2020-12-09 12:37:56.39 spid1556    SqlDumpExceptionHandler: Process 1556 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2020-12-09 12:37:56.39 spid1556    * *******************************************************************************
2020-12-09 12:37:56.39 spid1556    *
2020-12-09 12:37:56.39 spid1556    * BEGIN STACK DUMP:
2020-12-09 12:37:56.39 spid1556    *   12/09/20 12:37:56 spid 1556
2020-12-09 12:37:56.39 spid1556    *
2020-12-09 12:37:56.39 spid1556    *
2020-12-09 12:37:56.39 spid1556    *   Exception Address = 00007FFA5C7AC473 Module(sqllang+000000000090C473)
2020-12-09 12:37:56.39 spid1556    *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
2020-12-09 12:37:56.39 spid1556    *   Access Violation occurred reading address 000001DE907EC000
2020-12-09 12:37:56.39 spid1556    * Input Buffer 44 bytes -
2020-12-09 12:37:56.39 spid1556    *  EXEC [StoredProcedureName] 7
2020-12-09 12:37:56.39 spid1556    *  
2020-12-09 12:37:56.39 spid1556    *

Our monitoring systems were also reporting high active worker threads:

High - Active_Worker_Thread_Threshold_SQL2019instance alert raised on SQL2019instance.local > SQLServerComputer\(local) > admin
Raised at 09 Dec 2020 12:37 - View in SQL Monitor
The custom metric value has passed an alert threshold.

 

Cause.


Initially, we were able to find only SQL Server Dump text files but finally the server generated a full dump file. After analyzing the SQL Server Dump file we found the following:

EXCEPTION_RECORD:  (.exr -1)
ExceptionAddress: 00007ffa5c7ac473 (sqllang!CScaOp_Udf::TransformToSubQuery+0x00000000000007d3)
   ExceptionCode: c0000005 (Access violation)
  ExceptionFlags: 00000000
NumberParameters: 2
   Parameter[0]: 0000000000000000
   Parameter[1]: 000001de907ec000
Attempt to read from address 000001de907ec000

PROCESS_NAME:  sqlservr.exe

READ_ADDRESS:  000001de907ec000
 
ERROR_CODE: (NTSTATUS) 0xc0000005 - The instruction at 0x%p referenced memory at 0x%p. The memory could not be %s.
 
EXCEPTION_CODE_STR:  c0000005
 
EXCEPTION_PARAMETER1:  0000000000000000
 
EXCEPTION_PARAMETER2:  000001de907ec000
 
MISSING_CLR_SYMBOL: 0

 

From the dump analysis we got that a call to a scalar user define function was creating the dump. The stored procedure was calling just one scalar function from its T-SQL code.

 

Workaround.


Microsoft is promising a fix for Cumulative Update 9 expected to be released on January 2021, meanwhile we came up with 2 workarounds:

  1. We increased the number of CPU cores on all database servers that were part of the Always On configuration. This stopped SQL Server service from restarting right away.
  2. We later eradicated the scalar function and move its T-SQL code inside 2 stored procedures that were calling the scalar function. 

Solution.

Apply the fix contained on KB4538581 (FIX: Scalar UDF Inlining issues in SQL Server 2019). The fix is contained on Cumulative Update 9 for SQL Server 2019.

 

 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.