TCP Provider Error/Semaphore Timeout from an SSIS Package

I had a particularly tricky case recently that took a while to track down the cause, sharing in case it helps save anyone a few weeks of thier time and thier blood pressure!

Symptoms

An SSIS package fails with the following error;

Executing the query "Some_Query" failed with the following error: "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)".

Cause

If priority boost is enabled for an instance on the server in question then while under stress it is possible for the OS to be denied access to required resources that can cause the above behaviour to occur and the likelihood of this behaviour occurring increases when multiple instances are on the same machine with the option enabled.

You can ascertain the state of priority boost by running the following command;

EXEC sp_configure 'priority boost'

GO

As priority boost is an advanced command, you may need to enable them first otherwise you will see the following error;

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'priority boost' does not exist, or it may be an advanced option.

To enable them, run the following script;

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

The following shows the “EXEC sp_configure ‘priority boost’” results from a server that has priority boost disabled however the service has not been restarted so is still active at runtime.

name minimum maximum config_value run_value

----------------------------------- ----------- ----------- ------------ -----------

priority boost 0 1 0 1

Resolution

For each instance on the server, run the following;

EXEC sp_configure 'priority boost', 0

RECONFIGURE WITH OVERRIDE

GO

More Information

Priority boost:
https://msdn.microsoft.com/en-in/library/ms180943(v=SQL.105).aspx

Sp_configure:
https://msdn.microsoft.com/en-in/library/ms188787(v=SQL.105).aspx