SQLCLR resource waits
Op SQL Server omgevingen, zie je soms hoge waarden bij de Wait Time in de Resource Waits van Activity Monitor. Juist wanneer de betreffende omgeving performance problemen kent, ben je geneigd hier in het bijzonder naar te gaan kijken. Het lijkt immers al snel dé belangrijkste resource wait.
Voordat ik verder ga, eerst even wat is SQLCLR eigenlijk? Uit Wikipedia:
“SQL CLR or SQLCLR (SQL Common Language Runtime) is technology for hosting of the Microsoft .NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment.”
SQL CLR wordt onder andere gebruikt door Integration Services:
Mijn vermoeden is dan ook dat je de SQLCLR wait bijna altijd ziet op SQL Server omgevingen waar Integration Services op draait. Al is het wel weer vreemd dat ik het op mijn eigen laptop weer niet zie. Wil je zien welke assemblies er geladen zijn in de SQL CLR, dan kun je daar de dm_clr_loaded_assemblies DMV voor gebruiken. Samen met de sys.assemblies view, kun je zien welke assemblies het precies zijn. Op diverse omgevingen waar alleen SQL Server en SSIS geïnstalleerd zijn, levert dm_clr_loaded_assemblies één geladen assembly op. Voer je dan de volgende query uit, zie je dat het inderdaad om Integration Services gaat:
USE SSISDB;
SELECT a.name, a.clr_name
FROM sys.dm_clr_loaded_assemblies l
INNER JOIN sys.assemblies a ON l.assembly_id = a.assembly_id
Let op dat sys.assemblies alleen assemblies in de huidige database toont. Vandaar dat USE SSISDB noodzakelijk is.
Nu we weten dat Integration Services vrijwel zeker de SQLCLR waits veroorzaakt, blijft de vraag of dit dan een probleem is of niet. Het Microsoft Customer Service and Support (CSS) SQL Support team zegt daarover:
“When you start a CLR under SQL Server one(1) or more CLR workers are created and they wait for new work. CLR waits on an auto event and this surfaces as the CLR_AUTO_EVENT wait type. By including this in the SQLCLR wait category of activity monitor it appears you have a wait that needs attention when all it means is you have a CLR worker waiting for work and it is not interesting to the monitoring of your SQL Server.”
Kortom, het is logisch dat SQL CLR voor een wachttijd van 1 seconde per seconde zorgt. Ook in een ander artikel van het CSS SQL Support team, wordt bevestigd dat CLR waits alleen in uitzonderingssituaties aandacht vragen:
“If you have a SQL CLR application, you may notice that waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT from sys.dm_os_wait_stats are very high. This is normal and can be safely ignored because these waits reflect internal CLR runtime waits under normal conditions. Since SQL as a CLR host implements synchronization mechanism, it is able to track these kinds of waits and exposes them via DMV.
The only exception when you need to pay attention to these two wait types is that you actually use unsafe assemblies and use CLR Event objects (ManualResetEvent or AutoResetEvent) yourself. In other words, if you use ManualResetEvent and you code results in waiting on the object, the wait time will also be reflected in CLR_MANUAL_EVENT type. The same is true for AutoResetEvent which will be reflected in CLR_AUTO_EVENT type. But using these two objects will require explicitly creating the assembly in unsafe mode.”
Ergo, als alleen Integration Services gebruik maakt van SQL CLR, kun je resource waits in de SQLCLR categorie negeren, hoe hoog ze ook zijn.