SQL Server CLR UDF Parallelism redux

Question

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.

Answer 1

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.

Answer 2

It might not be a problem with the CLR UDF but with SQL Server's query plan decision. You can force a parallel plan by using OPTION (QUERYTRACEON 8649) as explained here.