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 Power BI 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 in sys.dm_os_buffer_descriptors DMV. Error_code column represents the error information during latest attempt to populate the buffer in question. Op_history column represents operational history of the buffer in bit format (4 bit per operation, 16 operations total, last 4 bits represent the latest operation and shift left as time progresses).
Note This fix does not resolve any specific errors that you see while populating the buffer pages. It only exposes information about error codes and operations on buffers so 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.
