I’m not sure about Question 1, although that may not matter, given the recommendation for Question 2. However, the one difference between SQL Server 2008 (where it works) and SQL Server 2014 (where it does not work) is the version of the CLR that SQL Server is associated with. SQL Server 2005/2008/2008 R2 is associated with CLR version 2.0, and SQL Server 2012 and newer is associated with CLR version 4.0. Since you do not see the error and your client, I would make sure that their system was updated to the same version of the .NET Framework in which you work.
In question number 2, I would recommend removing multithreading. This has too much potential for problems and requires the Assembly to be UNSAFE . If you remove the thread, you can set Assembly to EXTERNAL_ACCESS .
If you want to reduce the conflict, then provided that the web service calls belong to the same URI, you need to increase the number of concurrent web requests allowed. This can be done by setting the ServicePointManager.DefaultConnectionLimit property . The default value is 2. This means that any additional requests will wait and wait until one of the current 2 is closed.
Also, don't forget to properly Dispose WebRequest .
The concern that external calls (i.e., a web service) that could potentially not be completed quickly is that SQL Server uses collaborative multitasking, in which each thread is responsible for “assigning” control to the Scheduler (effectively pausing him) at different points so that the Scheduler can mix things up and run other things that are currently “sleeping”. This problem with SQLCLR code can usually be mitigated by at least one of the following:
- Perform data access / instance request
- Call
thread.sleep(0);
However, an external call does not access the data, and you cannot easily call thread.sleep(0) , waiting for WebResponse complete. Yes, you can call the WebService in a separate thread and, waiting for it to finish, assuming that you are just looping and checking, sleep(x) will generate revenue.
But does the web service call asynchronously? Of course, the flaw requires that the Assembly be marked as WITH PERMISSION_SET = UNSAFE . This greatly depends on how long the call usually takes and how often it is called. The more often the call occurs, the more likely it is that any delays, at least in part, are caused by the low default value for how many concurrent connections are allowed per URI. This concerns the recommendation I made above.
But if you want to see how SQL Server works, it's pretty easy to check. On my laptop, I switched to the "Properties" of the server "Object" in the "Object Browser", switched to "Processors", did not remove the option "automatically establish processor compatibility ...", selected only one processor in the "Processor Initiative" section in the tree view In the middle of the dialog box, click OK, and then restart the service. Then I created a web page that did nothing but called "sleep" for 60 seconds. I have an SQLCLR TVF that calls web pages, so I ran it simultaneously in two different tabs / sessions. In the third tab / session, I ran:
SELECT SUM(so1.[schema_id]), so1.[type_desc], so2.[type_desc] FROM sys.objects so1 CROSS JOIN sys.objects so2 CROSS JOIN sys.objects so3 CROSS JOIN sys.objects so4 CROSS JOIN sys.objects so5 WHERE so3.[create_date] <> so4.[modify_date] GROUP BY so1.[type_desc], so2.[type_desc], so5.[name] ORDER BY so2.[type_desc], so5.[name] DESC;
And finally, on the fourth tab, after starting the first 3, I ran the following to monitor the system:
SELECT * FROM sys.dm_os_schedulers WHERE [scheduler_id] = 0; SELECT * FROM sys.dm_exec_requests WHERE [scheduler_id] = 0 AND [status] <> N'background' ORDER BY [status] DESC, session_id;
The status for the two sessions performing the SQLCLR function was always “started”, and the status of the session running with the ugly request in tab 3 was always “runnable”. But, to be sure that launching this ugly query again when none of the SQLCLR functions was running, it took the same 1 minute and 14 seconds as when 2 sessions were launched simultaneously making an SQLCLR call to a sleeping web page in within 60 seconds.
Please do not conclude that there is no cost to running SQLCLR code to launch web calls. Since these threads were busy all the time, if the system was busy, this would reduce the ability of SQL Server to allocate these threads for faster execution of other queries. But it seems safe to conclude that, at least in systems with low and medium load, the advantage obtained by adding threads, apparently, is not worth the cost of increased complexity (especially since there is now an unreproducible problem for debugging).