SQL Server CLR Monitoring and Optimization

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:

[codesyntax lang=”tsql”]

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

[/codesyntax]

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:-

[codesyntax lang=”csharp”]

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;
        }
    }

[/codesyntax]

 

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:-

[codesyntax lang=”tsql”]

select * from sys.dm_clr_tasks;
Select dbo.GetNums(1,100000000000,10);
select * from sys.dm_clr_tasks;

[/codesyntax]

 

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.

[info]Running the SQLCLR function causes other queries to be very slow.[/info]

[info]You may monitor SQL CLR Assembly by using Performance Monitor as well.[/info]

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.

[codesyntax lang=”csharp”]

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;
        }
    }

[/codesyntax]

 

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

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

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan