9

Our application needs to work equally well with an Oracle database or a Microsoft SQL Server database. To facilitate this, we created a handful of UDFs to homogenize our query syntax. For example, SQL Server has GETDATE() and Oracle has SYSDATE. They perform the same function but they are different words. We wrote a wrapper UDF called NOW() for both platforms which wraps the relevant platform specific syntax in a common function name. We have other such functions, some of which do essentially nothing but exist solely for the sake of homogenization. Unfortunately, this has a cost for SQL Server. Inline scalar UDFs wreak havoc on performance and completely disable parallelism. As an alternative, we wrote CLR assembly functions to accomplish the same goals. When we deployed this to a client they started experiencing frequent deadlocks. This particular client is using replication and high availability techniques and I'm wondering if there's some sort of interaction going on here. I just don't understand how introducing a CLR function would cause problems like this. For reference, I've included the original scalar UDF definition as well as the replacement CLR definition in C# and the SQL declaration for it. I also have deadlock XML that I can provide if that helps.

Original UDF

CREATE FUNCTION [fn].[APAD]
(
    @Value VARCHAR(4000)
    , @tablename VARCHAR(4000) = NULL
    , @columnname VARCHAR(4000) = NULL
)

RETURNS VARCHAR(4000)
WITH SCHEMABINDING
AS

BEGIN
    RETURN LTRIM(RTRIM(@Value))
END
GO

CLR Assembly Function

[SqlFunction(IsDeterministic = true)]
public static string APAD(string value, string tableName, string columnName)
{
    return value?.Trim();
}

SQL Server Declaration for CLR Function

CREATE FUNCTION [fn].[APAD]
(
    @Value NVARCHAR(4000),
    @TableName NVARCHAR(4000),
    @ColumnName NVARCHAR(4000)
) RETURNS NVARCHAR(4000)
AS
EXTERNAL NAME ASI.fn.APAD
GO
  • 9
    Deterministic scalar CLR functions should not contribute to deadlocks. Of course CLR functions that read the database might. You should include the deadlock XML in your question. – David Browne - Microsoft Apr 26 at 14:47
7

What version(s) of SQL Server are you using?

I do recall seeing a slight change in behavior in SQL Server 2017 not too long ago. I will have to go back and see if I can find where I made a note of it, but I think it had to do with a schema lock being initiated when a SQLCLR object was being accessed.

While I am looking for that, I will say the following regarding your approach:

  1. Please use the Sql* types for input parameters, return types. You should be using SqlString instead of string. SqlString is very similar to a nullable string (your value?, but it has other functionality built in that is SQL Server-specific. All of the Sql* types have a Value property that returns the expected .NET type (e.g. SqlString.Value returns string, SqlInt32 returns int, SqlDateTime returns DateTime, etc).
  2. I would recommend against this entire approach to begin with, whether or not the deadlocks are related. I say this because:

    1. Even with deterministic SQLCLR UDFs being able to participate in parallel plans, you are most likely going to get performance hits for emulating simplistic built-in functions.
    2. The SQLCLR API does not allow for VARCHAR. Are you ok with implicitly converting everything to NVARCHAR and then again back to VARCHAR for simple operations?
    3. The SQLCLR API does not allow for overloading, so you might need multiple versions of functions that do allow for different signatures in T-SQL and/or PL/SQL.
    4. Similar to not allowing for overloading, there is a big difference between NVARCHAR(4000) and NVARCHAR(MAX): the MAX type (having even a single one of them in the signature) make the SQLCLR call take twice as long as not having any MAX type in the signature (I believe this holds true for VARBINARY(MAX) vs VARBINARY(4000) as well). So, you need to decide between:
      • using only NVARCHAR(MAX) to have a simplified API, but take the performance hit when you are using 8000 bytes or less of string data, or
      • creating two variations for all / most / many string functions: one with MAX types, and one without (for when you are guaranteed to never go over 8000 bytes of string data in or out). This is the approach I chose to take for most of the functions in my SQL# library: there is a Trim() function which likely has one or more MAX types, and a Trim4k() version which never has a MAX type anywhere in the signature or result set schema. The "4k" versions are absolutely more efficient.
    5. You are not being careful to emulate functionality given the example in the question. LTRIM and RTRIM only trim spaces, while .NET String.Trim() trims white space (at least space, tabs, and newlines). For example:

        PRINT LTRIM(RTRIM(N'      a       '));
      
    6. Also, I just noticed that your function, both in T-SQL and in C#, only uses 1 of the 3 input parameters. Is this just a proof of concept, or redacted code?
  • 1. Thanks for the tip on using the Sql types. I'll make that change now. 2. There are external forces at work here which necessitate the use of them. I'm not thrilled about it but trust me, it's better than the alternative. My original question contains a little bit of the explanation as to why a seemingly asinine function exists and is being used. – Russ Suter Apr 26 at 15:54
  • @RussSuter Understood re: external forces. I was just pointing out some pitfalls that might not have been known when that decision was made. Either way, I'm unable to find my notes or reproduce the scenario from the few details I remember of it. I just remember something definitely changing in 2017 with regards to transactions and calling code from an assembly, and being really annoyed with it as it seemed like a needless change for the worse, and I had to work around it for what I was testing that worked fine in prior versions. So, please post a link in the question to the deadlock XML. – Solomon Rutzky Apr 26 at 16:24
  • Thanks for that additional info. Here's a link to the XML: dropbox.com/s/n9w8nsdojqdypqm/deadlock17.xml?dl=0 – Russ Suter Apr 26 at 17:30
  • @RussSuter Have you tried this with inlining the T-SQL? Looking at the deadlock XML (which is not easy as it's a single line--all newlines got removed somehow) it appears to be a series of PAGE locks between sessions 60 and 78. There are 8 pages locked between both sessions: 3 for one SPID and 5 for the other. Each with a different process ID, so this is an issue of parallelism. If this is related to SQLCLR, it might ironically be the fact that SQLCLR isn't preventing parallelism. This is why I asked if you have tried putting the simple function inline as that might also show the deadlock. – Solomon Rutzky Apr 26 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.