8

When I check our SQL Prod server, every time I open Activity Monitor I always see SQLCLR on the first list on the resource wait section in the Activity Monitor. It always has a high number. What is SQLCLR for? Is it bad for the server having that SQLCLR with high resource wait number in the activity monitor?

9

SQLCLR is the ability to run .NET code within SQL Server.

When people speak of SQLCLR they are usually referring to the ability to write custom .NET code (Stored Procedures, Functions, Triggers, User-Defined Types, and User-Defined Aggregates). In this case, this ability can be turned on and off via the server option of "clr enabled" within sp_configure, and I believe it's called "CLR Integration" in the Surface Area Configuration GUI. Adding .NET functionality requires using CREATE ASSEMBLY to load the compiled .NET code into SQL Server.

However, even with the "clr enabled" option set to 0 / "off" / "disabled", CLR functionality is still used for internal functionality and some built-in functions and will always be there, unless the "use Windows fibers" / "lightweight pooling" option is enabled.

For a very detailed description of what SQLCLR is, is not, and what it can do, please see the article that I wrote on this topic on SQL Server Central: Stairway to SQLCLR Level 1: What is SQLCLR? (free registration is required to view content on that site). A stripped-down version of that article is available in the following StackOverflow answer: When do we need a CLR function in SQL Server?.

If you want to know whether or not the instance is allowing custom .NET code to be added to databases, run the following and check the "run_value" field:

EXEC sp_configure 'clr enabled';

With regards to the meaning of the SQLCLR wait type in Resource Monitor, please see the following blog post:

How It Works: What is behind the SQLCLR wait category in SQL Server 2008 Activity Monitor

and I believe the pertinent line (as it pertains to the SQLCLR category you are looking at) from that post is:

What I found was that several of the wait_types should be ignored as they are expected waits.

  • That's SQL2008, and was supposedly scheduled to be corrected (based on the linked article). The OP has flagged SQL 2012. I'm just looking at the same thing as for me SQLCLR is sitting steady at 3000ms +-1 and we're not using it. – Roger Willcocks Aug 1 '16 at 0:54
  • @RogerWillcocks Scheduled to be fixed does not always mean that it will be fixed. If you read through the comments on that linked post, you will see people reporting that it is still an issue in 2012 SP1 and SP2. What version of SSMS are you using? Run the following (next comment) to see if it matches what you are seeing. Also, there are several built-in features that do use SQLCLR, such as the Geometry, Geography, and HierarchyID types; the FORMAT command, and some other stuff. – Solomon Rutzky Aug 1 '16 at 19:45
  • 1
    SELECT * FROM sys.dm_os_wait_stats WHERE wait_type IN ('CLR_AUTO_EVENT', 'CLR_CRST', 'CLR_JOIN', 'CLR_MANUAL_EVENT', 'CLR_MEMORY_SPY', 'CLR_MONITOR', 'CLR_RWLOCK_READER', 'CLR_RWLOCK_WRITER', 'CLR_SEMAPHORE', 'CLR_TASK_START', 'CLRHOST_STATE_ACCESS', 'ASSEMBLY_LOAD', 'FS_GARBAGE_COLLECTOR_SHUTDOWN', 'SQLCLR_APPDOMAIN', 'SQLCLR_ASSEMBLY', 'SQLCLR_DEADLOCK_DETECTION', 'SQLCLR_QUANTUM_PUNISHMENT') ORDER BY wait_time_ms DESC, wait_type ASC; – Solomon Rutzky Aug 1 '16 at 19:45

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.