Time Trouble: SqlClient and Timeout(S)

Nothing new under the sun… just the same recurring issues, but after following a wrong track, I wanted to share a problem while dealing with a Stored Procedure with SqlClient, the dispair to resolve it and the actual *dumb* solution.

I have a small PowerShell (PoSH) module. One function use SqlClient to call some procedure on an SqlServer.
This proc makes use of Linked Server and it is not efficient. Under normal circonstance it runs for about 3 min. But that is not in itself an issue, it just is so.

From SSMS it is all fine. It takes the time and completes. From the PoSH/SqlClient: Timeout!

$conn_string = "Server=YOUR-INSTANCE;" + `
    "Database=TheDatabase;" + `
    "Integrated Security=true;Pooling=false;"    
         
$procName = 'Request.LongRemote'

$conn = New-Object System.Data.SqlClient.SqlConnection($conn_string)

$upSterbCmd = New-Object System.Data.SqlClient.SqlCommand($procName, $conn)
    $upSterbCmd.CommandType = [System.Data.CommandType]::StoredProcedure

try {
    $conn.Open()
    $upSterbCmd.ExecuteNonQuery() | Out-Null
} catch [Exception] {
    Write-Warning $_.Exception.Message
    Write-Warning $procName
} finally {
    $conn.Dispose()
    $upSterbCmd.Dispose()
}

Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en):  "Das Ausführungstimeout ist abgelaufen. Der Timeoutzeitraum wurde überschritten, bevor der Vorgang beendet wurde, oder der Server antwortet 
nicht."
In Zeile:2 Zeichen:13
+             $upSterbCmd.ExecuteNonQuery() | Out-Null
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

yes … working in Germany

Lets give it some more time: Connection Timeout=300: 5 minutes is good, above we have an issue anyway.

$conn_string = "Server=YOUR-INSTANCE;" + `
    "Database=TheDatabase;" + `
    "Integrated Security=true;Pooling=false;" + `
    "Connection Timeout=300;"

Back to test… and timeout. Why??

Fine: Google and Stack Exchange to the rescue with a search on “sqlclient time out not ssms” … And then the hells unwrapp 😦 . It goes on with Parameter Sniffing, and Plan Cache and RECOMPILE and ARITHABORT and more of try SET *yourfavoriteParam*.

Where to start? Linked Server, Procedure Compilation, SqlClient, … so many possibilities! The posts and answers are nice, but this is no 10 minutes solution (and today I needed some solution).

Before diving deeper in those topics and exploring this universe, I go back to PoSH and just output the objects to check my expectations.

First SqlConnection: as expected CommandTimeout is set to 600. So why is it timing out faster anyway? what about this SqlCommand?

I change it to a simple command to use SET ARITHABORT ON; . Just to give a try, it was the most cited solution … Timeout.

The object’s output gives me:

Connection              : System.Data.SqlClient.SqlConnection
NotificationAutoEnlist  : True
Notification            : 
Transaction             : 
CommandText             : Request.LongRemote
ColumnEncryptionSetting : UseConnectionSetting
CommandTimeout          : 30
CommandType             : StoredProcedure
DesignTimeVisible       : True
Parameters              : {}
UpdatedRowSource        : Both
Site                    : 
Container               : 

Duuuh ?… how that CommandTimeout : 30… Can this be so simple in the end?

$sqlcmd.CommandTimeout = 300

…running … working. Of course a connection timeout is not the same as a command timeout. Duuuh twice…

That was 2 lessons learned the hard way. First: no, not all timeouts are the same.
Second and most importantly: fully observe and collect first. Not just the error message, but the current status against you expectation, and only then can you go hunting in the wild for a solution.

It is trivial, but I needed my second “vaccin” shot to stay immune here… until the next time.