FIX: Performance issues and timeouts may occur while connecting with SQL Server 2017

Applies to: SQL Server 2017 on Linux (all editions)

Symptoms


Assume that you use SQL Server 2017 and you have many SQL Server Integration Services (SSIS) packages, you may notice that SSIS or Power BI reports fail sporadically with the following connection timeout errors:

"_Main_XXXXX:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005."

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Login timeout expired".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in login response".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: Timeout error [258]. "."

Otherwise, the issue is not limited to SSIS or PowerBI reports only. Any application can hit performance issues and timeouts while connecting with SQL Server. 

This fix introduces two new columns, error_code and op_history insys.dm_os_buffer_descriptors DMV. Error_code column represents the errorinformation during latest attempt to populate the buffer in question.Op_history column represents operational history of the buffer in bit format (4bit per operation, 16 operations total, last 4 bits represent the latestoperation and shift left as time progresses). 

Note This fix does not resolve any specific errors that you see while populating the bufferpages. It only exposes information about error codes and operations on buffersso that additional troubleshooting can be performed with the new information. 

Root Cause


The timeout and performance issues are generic in nature and can have many underlying root causes. In this particular case, there are many errors and retries which are observed while trying to read data pages from data files and populating the memory Buffers (memory representation of data pages). The root cause for buffer population errors are not identified clearly as the underlying error codes while trying to populate the buffers that are not exposed.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. 

Resolution


This issue is fixed in the following cumulative update for SQL Server:

About cumulative updates for SQL Server:

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

References


Learn about the terminology that Microsoft uses to describe software updates.