SQL Server的CLR线程 [英] SQL Server CLR Threading

问题描述

我一直在努力与SQL Server CLR存储过程

背景：

我们正在使用SQL Server 2014 CLR存储过程已经实现它调用客户的Web服务。

该线程最初用于没有减缓的主要SQL Server的CLR线程。

虽然，现在，我知道，在CLR使用线程是没有最好的想法，已经正常工作了6年（自SQL Server 2008中）。它已被迁移到SQL Server 2014最近。

问题

在我的机器，同样的测试系统上，我们有解决方案没有问题。

在客户系统中，线程，它调用Web服务，它从未在执行某些原因。

我可以从日志文件中看到一切正常，直到线程执行。

有没有特定的错误，什么都没有。

我们一直在试图更改权限，但没有成功。因此，我认为它不是一个权限问题。

问题

1. 没有人知道如何去改变？我们无法找到这可能是卓有成效的任何配置。




2. 难道是好主意完全去除线程，并直接具有Web服务调用在SQL Server的主线程？

感谢您的任何建议，
切赫

解决方案

不知道有关问题1，尽管它可能并不重要给出问题＃2的建议。不过，SQL Server 2008中（在那里工作）和SQL Server 2014年（如果它不工作）之间的一个区别是，SQL服务器链接到CLR版本。 SQL Server 2005中/ 2008/2008 R2都与CLR 2.0版在SQL Server 2012中，更新的链接到CLR V4.0起。既然你没有看到的错误，你的客户，我会确保他们的系统已经更新到您正在运行相同的.NET Framework版本。

有关问题2，我会建议删除多线程。这有问题，太多的潜力，并要求大会为不安全。如果删除线程，可以在大会设置为 EXTERNAL_ACCESS 。

如果你想减少争，那么假设Web服务调用是相同的URI，那么你就需要增加允许的并发Web请求的数量。这可以通过设置 ServicePointManager.DefaultConnectionLimit属性。默认值是2，这意味着，任何其他要求将等待，等待，直到目前的2之一是关闭的。

此外，请务必妥善的Dispose 的WebRequest 。

---

的>

有关使外部调用（即Web服务），且有可能无法快速完成令人担忧的是，SQL Server使用合作多任务，其中每个线程负责在不同的点屈服控制回调度器（有效暂停它）从而使计划任务洗牌周围的东西和运行其他的东西，正在睡觉。

• 执行数据访问/查询实例<：这与问候SQLCLR代码的关注通常可以通过以下步骤中的至少一个来减轻/ LI>
  
• 电话的Thread.Sleep（0）;

然而，外部调用没有做数据访问，你不能方便地调用的Thread.Sleep（0）在等待 WebResponse类来完成。是的，你可以调用一个单独的线程中的WebService，并在等待它完成，假设你刚刚循环和检查，在睡眠（X）将使产量

不过是做必要的异步Web服务调用？这当然有要求大会被标记为PERMISSION_SET WITH不安全= 的下行空间。它极大地取决于如何长呼叫通常需要，以及它是如何经常被调用。更频繁的通话，就越有可能的是，任何延迟，至少部分，造成了多少个并发连接每每个URI允许低默认值。这涉及到我在上面所提出的建议。

但是，如果你想看到SQL Server如何实际工作，这应该是相当容易进行测试。在我的笔记本电脑，我去了在对象资源管理服务器属性，去处理器，取消选中了自动设置处理器关联...选项，在树视图下的处理器关联选择只有一个CPU对话框的中间，点击确定，然后重新启动该服务。然后，我建立了一个网页，什么也没做，但称之为睡60秒。我有一个SQLCLR TVF调用网页，所以我跑了同时在两个不同的选项卡/会话。在第三个标签/会话，我跑：

<预类=郎-SQL prettyprint-覆盖> SELECT SUM（SO1 [schema_id]） ，SO1。[type_desc]，二氧化硫[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
，其中SO3 [CREATE_DATE<> 。SO4 [modify_date]
GROUP BY SO1 [type_desc]，二氧化硫[type_desc] SO5 [名]
ORDER BY SO2 [type_desc] SO5 [名] DESC。。。


最后，在第4个选项卡，拉开第3后，我跑到下面来监视系统：

<预类=郎-SQL prettyprint-覆盖> SELECT * FROM sys.dm_os_schedulers WHERE [scheduler_id] = 0;

SELECT *
从sys.dm_exec_requests视图
WHERE [scheduler_id] = 0
和[状态]<> N'background'
ORDER BY [状态] DESC，SESSION_ID;


有关运行SQLCLR功能2会议总是正在运行的状态和会话状态在运行选项3那个丑陋的查询总是可运行。只是可以肯定，再次运行丑陋查询，当没有的SQLCLR功能是执行，采取了同样的1分钟14秒，它确实与2会话并发运行的运行SQLCLR调用网页时正在睡觉60秒。

请不要推断存在的没有的成本运行SQLCLR代码，以使网络电话。由于这些线程忙于整个时间，如果系统繁忙那么这将减少为SQL Server分配这些线程更快完成其他查询的能力。但它似乎安全地得出这样的结论，至少在低系统，以中等负荷，加入线程所获得的利益似乎不值得增加的复杂性成本（特别是因为现在有一个还未可再现问题调试）。

I have been struggling with a SQL Server CLR stored procedure.

Background:

We are using SQL Server 2014 and a CLR stored procedure has been implemented which calls a customer's web service.

The threading was initially used not to slow the main thread of SQL Server CLR.

Although, now, I know that using threading under CLR is no best idea, it has been working correctly for 6 years (since SQL Server 2008). It has been migrated to SQL Server 2014 recently.

The problem

On my development machine, same as on test system we have no problem with the solution.

On the customer system, the thread, which calls the web service, is never executed for some reason.

I can see from the log files that everything is working correctly till the thread execution.

There is no specific error, nothing.

We have been trying to change the permissions, but without a success. Therefore I think its not a permission issue.

Questions

1. Does anyone know how to change the behavior? We couldn't find any configuration which might does the trick.

2. Would it be good idea to remove the threading completely, and having the calling of web services directly on SQL Server main thread?

Thank you for any advice, Petr

解决方案

Not sure about Question #1, though it might not matter given the recommendation for Question #2. Still, one difference between SQL Server 2008 (where it is working) and SQL Server 2014 (where it is not working) is the CLR version that SQL Server is linked to. SQL Server 2005 / 2008 / 2008 R2 are linked to CLR v2.0 while SQL Server 2012 and newer are linked to CLR v 4.0. Since you are not seeing the error and your client is, I would make sure that their system has been updated to the same .NET Framework version that you are running.

For Question #2, I would recommend removing the multi-threading. That has too much potential for problems, and requires the Assembly to be UNSAFE. If you remove the threading, you can set the Assembly to EXTERNAL_ACCESS.

If you want to reduce contention, then assuming the Web Service calls are to the same URI, then you need to increase the number of allowed concurrent web requests. That can be done by setting the ServicePointManager.DefaultConnectionLimit Property. The default value is 2. Which means, any additional requests will wait and wait until one of the current 2 is closed.

Also, be sure to properly Dispose of the WebRequest.

---

The concern about making external calls (i.e. the Web Service) that can potentially not complete quickly is that SQL Server uses Cooperative Multitasking wherein each thread is responsible for "yielding" control back to the Scheduler (effectively pausing it) at various points so that the Scheduler can shuffle things around and run other things that are currently "sleeping". This concern with regards to SQLCLR code can typically be mitigated by doing at least one of the following:

• Perform data access / querying the instance
• Calling thread.sleep(0);

However, an external call is not doing data access, and you cannot easily call thread.sleep(0) while waiting for the WebResponse to complete. Yes, you can call the WebService on a separate thread and while waiting for it to finish, assuming you are just looping and checking, the sleep(x) will allow for the yield.

But is doing the Web Service call asynchronously necessary? It certainly has the downside of requiring the Assembly to be marked as WITH PERMISSION_SET = UNSAFE. It greatly depends on how long the call usually takes, and how frequently it is being called. The more frequent the call, the more likely it is that any delays are, at least in part, caused by the low default value for how many concurrent connections are allowed per each URI. This relates to the recommendation I made at the top.

But if you want to see how SQL Server actually works, this should be fairly easy to test. On my laptop, I went to the Server "Properties" in Object Explorer, went to "Processors", unchecked the "automatically set processor affinity..." option, selected only a single CPU under "Processor Affinity" in the tree view in the middle of the dialog, clicked "OK", and then restarted the service. I then set up a web page that did nothing but call "sleep" for 60 seconds. I have a SQLCLR TVF that calls web pages so I ran that concurrently in two different tabs / sessions. In a 3rd 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, in a 4th tab, after kicking off 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 2 sessions running the SQLCLR function was always "running" and the status for the session running that ugly query in tab 3 was always "runnable". But just to be sure, running that ugly query again, when neither of the SQLCLR functions was executing, took the same 1 minute and 14 seconds that it did when running concurrently with the 2 sessions running the SQLCLR call to the web page that was sleeping for 60 seconds.

Please do not infer that there is no cost to running the SQLCLR code to make the web calls. Since those threads were busy the whole time, if the system was busy then it would have reduced the ability for SQL Server to allocate those threads to complete other queries faster. But it does seem safe to conclude that, at least on systems with low to moderate load, the benefit gained by adding the threading doesn't seem to be worth the cost of increased complexity (especially since now there is a not-yet-reproducable problem to debug).

这篇关于SQL Server的CLR线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！