My Application: I wrote a script in Go that fetches a large amount (>10k) of JSON files over FTP and writes the contents to a table in a local SQL Server 2016 instance. After a folder of files has been imported, it triggers a T-SQL procedure that deals with further data processing. Overall this solution seems to work very well. However, I need a high degree of parallel operations to have sufficient performance. Usually, each file has around 2,000 entries and I need to import around 5,000 files per folder. The SQL Server runs locally and performance of the statements is not an issue.
Execution Structure: The file list is retrieved via FTP and each filename is handed over to a Goroutine. The go-routine fetches the file (also via FTP), parses it and triggers the SQL insert. A simple insert statement is prepared at the start of the application ("INSERT INTO tbl (val1,val2,val3) SELECT $1,$2,$3") and then executed in the Goroutine (stmt.Exec(var1,var2,var3)) for each entry. I control the maximum number of running routines with a channel variable (make(chan bool,MAXPAR), where MAXPAR=10 but should be 30).
The Problem: Whenever I start more than ~15 parallel routines (30 would be optimal) I get the following error after a short while (~1k files)
A network-related or instance-specific error occurred while establishing a connection to SQL Server at 127.0.0.1:1433. Only one usage of each socket address (protocol/network address/port) is normally permitted.
I could find online that this may indicate an exhaustion of the TCP/IP stack(?), but couldn't find a solution for my problem yet. Due to the structure of the code, only 30 statements (when MAXPAR is set to 30) will be executed at the same time, so the problem shouldn't be the number of connections. As I understood, it may be that not the degree of parallelism, but the number of executions per second are the limiting factor here.
My ideas: Currently, I can think of three scenarios that could help:
The last option is currently the only one where I'm confident that it could work. However, as I have similar insert scripts (where the problem didn't occur so far), I would rather like to understand the underlying problem and if there is a way to prevent it before I go and change all applications.
Question: My question is if anyone has experience when this problem arises (i.e. is it predictable) and if one of the solutions above (or another solution) would work from your experience. If it was just about one script I'd just try all solutions, but as there are other scripts as well where I'm not sure if they could experience similar issues, I'd like to find a solution where I can be reasonably certain that this is solved.
System
Thanks
I have this vague recollection of SQL Server Developer Edition running on client operating systems only supporting 10 "connections". My memory and internet searches haven't really nailed down whether this is concurrent connections, concurrent queries, or something else. There is lots of chatter around that topic. If it works with 10 but not 11 I'd say that's the limit you're facing.