Hi everyone. Here's the scenario I wish to inquire about : I have at least 10 remote test machines with their own SQL Server installed. What I am doing is that I send at least 5 SELECT statements to pull data from those machines concurrently (on a multi-threaded approach) on 5 non-related tables. Some of those machines, depending on user usage, contains at least more than 2000 records, that what I needed to extract using the aforementioned SELECT query. I created a thread for each remote connection so that they will run concurrently and dispose with the problem of waiting for one select query to return a record before moving to the next queued connection. It is working fine as of my last test run. My question is, if I pushed it as far as 100 machines, will these have any adverse effect on the integrity of records being returned by each connection if I am going to run the SELECT statement concurrently? Your thoughts and opinions regarding this matter would be much appreciated as it will help me improve more the software I am developing as of the moment. May thanks and here's looking forward to your feedbacks :)
What I have tried:
Tried as much a 20 machines and set the SQL connection timeout by at least 3 minutes for each connection. It worked but I noticed a rather significant spike in my CPU and network usage. While it is executing. And some of the connections get dropped / timed out even if pinging the machine indicates that it is online.
Submit an article or tip