SQL Server的CLR UDF并行终极版 [英] SQL Server CLR UDF Parallelism redux

问题描述

我一直在研究了一段时间的SQL Server CLR UDF和并行性。普遍的共识似乎是，在SQL Server 2008及更高版本，以 DataAccessKind.None A标量值CLR UDF应该允许并行执行。

然而，当我用我的标量值UDF在我的SQL Server 2012中来看，它仍然在加入之类的杀死并行执行。

时有什么特别的，我需要添加到我的C＃代码或T-SQL UDF的定义，以表明它是并行执行安全吗？

感谢。

解决方案

据链接到关于该问题的第一个注释的MSDN论坛，C＃代码大致开始时为：

<预类=郎-CS prettyprint-覆盖> [Microsoft.SqlServer.Server.SqlFunction（）]
公共静态的SqlString MyUDF（数据的SqlString）


和按您所添加的问题 DataAccessKind.None 使其：

<预类=郎-CS prettyprint-覆盖> [Microsoft.SqlServer.Server.SqlFunction（数据访问= DataAccessKind.None ）
公共静态的SqlString MyUDF（的SqlString数据）


要开始使用，既数据访问和 SystemDataAccess 默认 DataAccessKind.None ，所以将它们设置明确到 DataAccessKind.None ，而一个好的做法，不应该作出任何显着的区别。

有两种需要其他属性进行设置： IsDeterministic 和 IsPrecise 。这些属性是元数据的查询优化器使用，并均假默认情况下。因此，最好是设置一个或两者都为true（假设，当然，这些设置准确地反映该特定函数内的代码）。

• 确定性意味着相同的输入是<青霉>保证的具有相同的输出。所以，如果你的函数总是会为一组特定的输入返回相同的值，那么它是确定的，应标记为 IsDeterministic = TRUE 。


• 您可以设置 IsPrecise = TRUE 如果你不使用任何浮点（即双击或单）的值（即 FLOAT 或实 。）

在SqlFunction属性应如下所示：

<预类=郎-CS prettyprint-覆盖> [Microsoft.SqlServer.Server.SqlFunction（SystemDataAccess = DataAccessKind.None，
数据访问= DataAccessKind.None，IsDeterministic = TRUE，IsPrecise = TRUE）]
公共静态的SqlString MyUDF（的SqlString数据）


更新：

• 这可能是需要一个额外的产品，含有这种方法装配有一个 PERMISSION_SET SAFE 的


• 这可能不是的需要的有 IsPrecise 的 SqlFunction 属性设置为的财产真实并行执行计划。



I have been researching SQL Server CLR UDFs and parallelism for some time. The general consensus seems to be that in SQL Server 2008 and later, a scalar value CLR UDF with DataAccessKind.None should allow parallel execution.

However, when I use my scalar value UDF in my view in SQL Server 2012, it still kills parallel execution in joins and the like.

Is there something special I need to add to my C# code or the T-SQL UDF definition to indicate that it is safe for parallel execution?

Thanks.

解决方案

According to the MSDN forum that is linked to in the first comment on the question, your C# code roughly starts out as:

 [Microsoft.SqlServer.Server.SqlFunction()]
    public static SqlString MyUDF(SqlString data)

and according to the question you have added DataAccessKind.None making it:

 [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None)]
    public static SqlString MyUDF(SqlString data)

To start with, both DataAccess and SystemDataAccess default to DataAccessKind.None, so setting them explicitly to DataAccessKind.None, while a good practice, shouldn't have made any noticeable difference.

There are two other properties that need to be set: IsDeterministic and IsPrecise. These properties are meta-data that the Query Optimizer uses and are both false by default. Hence, it is best to set one or both of them to true (assuming, of course, that the settings accurately reflect the code within that particular function).

• Determinism means that the same inputs are guaranteed to have the same output. So if your function will always return the same value for a particular set of inputs, then it is deterministic and should be marked as IsDeterministic = true.
• You can set IsPrecise = true if you are not using any floating point (i.e. Double or Single) values (i.e. FLOAT or REAL in T-SQL terms).

The SqlFunction attribute should look as follows:

[Microsoft.SqlServer.Server.SqlFunction(SystemDataAccess = DataAccessKind.None,
   DataAccess = DataAccessKind.None, IsDeterministic = true, IsPrecise = true)]
   public static SqlString MyUDF(SqlString data)

UPDATE:

• One additional item that is probably required is that the assembly containing this method has a PERMISSION_SET of SAFE.
• It is probably not required to have the IsPrecise property of the SqlFunction attribute set to true in order to get the UDF to work in a parallel execution plan.

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