SQL Server CLR Monitoring and Optimization

The estimated reading time for this post is 4 minutes

The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

This technology, introduced in Microsoft SQL Server 2005, allow users for example to create the following types of managed code objects in SQL Server in .NET languages such as C# or VB.NET.

  • Stored procedures (SPs) which are analogous to procedures or void functions in procedural languages like VB or C,
  • triggers which are stored procedures that fire in response to Data Manipulation Language (DML) or Data Definition Language (DDL) events,
  • User-defined functions (UDFs) which are analogous to functions in procedural languages,
  • User-defined aggregates (UDAs) which allow developers to create custom aggregates that act on sets of data instead of one row at a time,
  • User-defined types (UDTs) that allow users to create simple or complex data types which can be serialized and deserialized within the database.

The SQLCLR relies on the creation, deployment, and registration of CLI assemblies, which are physically stored in managed code dynamic load libraries (DLLs). These assemblies may contain CLI namespaces, classes, functions and properties.

The common language runtime (CLR) integration feature is off by default in Microsoft SQL Server, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration using Transact-SQL, use the clr enabled option of the sp_configure stored procedure as shown:

SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

Monitoring SQLCLR

SQL Server provides few Dynamic Management Views (DMVs) for monitoring SQL CLR process for each installed assembly, as following:

  1. sys.dm_clr_appdomains
  2. sys.dm_clr_tasks
  3. sys.dm_clr_prop0erties
  4. sys.dm_clr_loaded_assemblies

To understand how to use mentioned DMVs to monitor SQL CLR, I would like to deploy an assembly which has the following function:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
    public static class Calc
    {
        [Microsoft.SqlServer.Server.SqlFunction()]
        public static System.Data.SqlTypes.SqlInt64 GetNums(System.Data.SqlTypes.SqlInt64 Start, System.Data.SqlTypes.SqlInt64 End, System.Data.SqlTypes.SqlInt16 Step)
        {
            System.Data.SqlTypes.SqlInt64 Result = new System.Data.SqlTypes.SqlInt64();
            Result = Start;
            while(true)
            {
                Result += Step.Value;
                if (Result.Value >= End.Value)
                    break;
            }
 
            return Result;
        }
    }

 

Above function does very simple task as get the Start, End and the Interval values as parameter and does some calculations and returns the result value.

Lets run the following statement and check the result, It takes few minutes to be completed and slows down the entire SQL Server operations:-

SELECT * FROM sys.dm_clr_tasks;
SELECT dbo.GetNums(1,100000000000,10);
SELECT * FROM sys.dm_clr_tasks;

 

Below figure shows that SQL Server forced the CLR function to yield due to high processor usage, this behavior is due to we made a look inside the code to do some calculation.

clr1

Pay attention to the last resultset’s Forced_Yield_Count column of the third row, this CLR process (thread/worker) been yielded 35 times by SQLOS to let other workers also able to run their tasks on the same scheduler. Lets take a look at the appdomain’s  cost,value,total memory allocation and survived memory.

clr2

As above figure shows, our CLR appdomain cost is quiet high, and the risk of being unloaded during memory preasure but in the other hand the value is very high which it avoids the appdomain to be unloaded during memory pressure.

The total allocated memory is the total memory usage by appdomain and the survived memory is the last full, blocking collection and that are known to be referenced by the current appdomain.

Running the SQLCLR function causes other queries to be very slow.
You may monitor SQL CLR Assembly by using Performance Monitor as well.

Optimizing SQLCLR

After monitoring the appdomain and the CLR assembly, we found that the assigned worker/thread to run the function been yielded 35 times to allow other workers/threads to run their own task on the same scheduler. To reduce forced yields in CLR we need to yield the thread manually inside our codes by using System.Threading.Thread.Sleep(0) function to allow other threads to be executed as well.

Below code statements are optimized.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
    public static class Calc
    {
        [Microsoft.SqlServer.Server.SqlFunction()]
        public static System.Data.SqlTypes.SqlInt64 GetNums(System.Data.SqlTypes.SqlInt64 Start, System.Data.SqlTypes.SqlInt64 End, System.Data.SqlTypes.SqlInt16 Step)
        {
            System.Data.SqlTypes.SqlInt64 Result = new System.Data.SqlTypes.SqlInt64();
            Result = Start;
            int yieldThreshold = 0;
            while(true)
            {
                Result += Step.Value;
                yieldThreshold +=1;
                if (yieldThreshold >= 20)
                {
                    System.Threading.Thread.Sleep(0);
                    yieldThreshold = 0;
                }
                if (Result.Value >= End.Value)
                    break;
            }
 
            return Result;
        }
    }

 

After replacing the new assembly with optimized code, you will realized that the # of Forced_Yield_Counts are reduce significantly, as the following figure shows.

clr3

By using System.Threading.Thread.Sleep(0) function in SQL CLR, the function process takes quiet longer than usual.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

2 Comments on "SQL Server CLR Monitoring and Optimization"

Notify of
avatar
1000
Sort by:   newest | oldest | most voted
ramin
Guest

Beginning with SQL Server 2017, an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security

wpDiscuz