Tracking down high CLR_MANUAL_EVENT waits | Times of server

Tracking down high CLR_MANUAL_EVENT waits

I as of late got an email question from somebody in the network about the CLR_MANUAL_EVENT hold up type; particularly, how to investigate issues with this sit tight abruptly getting to be predominant for a current workload that depended vigorously on spatial information composes and inquiries utilizing the spatial techniques in SQL Server.

As an expert, my first inquiry is quite often, “What’s changed?” But for this situation, as in such a significant number of cases, I was guaranteed that nothing had changed with the application’s code or workload designs. So my first stop was to pull up the CLR_MANUAL_EVENT hold up in the SQLskills.com Wait Types Library to perceive what other data we had officially gathered about this hold up type, since it isn’t regularly a hold up that I see issues with in SQL Server. What I discovered extremely fascinating was the graph/heatmap of events for this hold up type given by SentryOne at the highest point of the page:

The way that no information has been gathered for this compose all through a decent cross-segment of their clients extremely affirmed for me this isn’t something that is ordinarily an issue, so I was charmed by the way that this particular workload was presently showing issues with this pause. I didn’t know where to go to additionally examine the issue so I answered to the email saying I was sad that I couldn’t help advance since I didn’t have any thought what might make truly many strings performing spatial inquiries all of sudden begin waiting for 2-4 seconds on end on this hold up type.

Multi day later, I got a kind follow-up email from the individual that made the inquiry that educated me that they had settled the issue. For sure, nothing in the real application workload had changed, yet there was a change to the condition that happened. An outsider programming bundle was introduced on the majority of the servers in their framework by their security group, and this product was gathering information at five-minute interims and causing the .NET rubbish accumulation preparing to run unimaginably forcefully and “go crazy” as they said. Furnished with this data and a portion of my past learning of .NET advancement I chose I needed to play around with this a few and check whether I could duplicate the conduct and how we could approach investigating the brings on additional.

Foundation Information

Throughout the years I’ve generally taken after the PSSQL Blog on MSDN, and that is typically one of my go-to areas when I review that I’ve perused about an issue identified with SQL Server sooner or later before yet I can’t recall the majority of the specifics.

There is a blog entry titled High looks out for CLR_MANUAL_EVENT and CLR_AUTO_EVENT by Jack Li from 2008 that clarifies why these holds up can be securely disregarded in the total sys.dm_os_wait_stats DMV since the holds up do happen under ordinary conditions, however it doesn’t deliver what to do if the hold up times are unreasonably long, or what could be making them be seen over different strings in sys.dm_os_waiting_tasks effectively.

There is another blog entry by Jack Li from 2013 titled An execution issue including CLR trash gathering and SQL CPU liking setting that I reference in our IEPTO2 execution tuning class when I discuss different example contemplations and how the .NET Garbage Collector (GC) being activated by one occurrence can affect alternate cases on a similar server.

The GC in .NET exists to diminish the memory utilization of uses utilizing CLR by permitting the memory designated to items to be tidied up consequently, in this way disposing of the requirement for engineers to need to physically deal with memory assignment and deallocation to the degree required by unmanaged code. The GC usefulness is reported in the Books Online on the off chance that you might want to find out about how it functions, yet the specifics past the way that accumulations can be blocking are not critical to investigating dynamic looks out for CLR_MANUAL_EVENT in SQL Server further.

Getting to the Root of the Problem

With the learning that junk accumulation by .NET was what was making the issue happen, I chose to do some experimentation utilizing a solitary spatial inquiry against AdventureWorks2016 and an exceptionally straightforward PowerShell content to conjure the city worker physically in a circle to track what occurs in sys.dm_os_waiting_tasks within SQL Server for the question:

Utilize AdventureWorks2016;

GO

SELECT a.SpatialLocation.ToString(), a.City, b.SpatialLocation.ToString(), b.City

FROM Person.Address AS a

Inward JOIN Person.Address AS b ON a.SpatialLocation.STDistance(b.SpatialLocation) <= 100

Request BY a.SpatialLocation.STDistance(b.SpatialLocation);

This question is looking at all of the addresses in the Person.Address table against each other to discover any address that is inside 100 meters of some other address in the table. This makes a long-running parallel assignment within SQL Server that likewise delivers a huge Cartesian outcome. In the event that you choose to recreate this conduct individually, don’t anticipate that this will finish or return results back. With the question running, the parent string for the undertaking starts to look out for CXPACKET pauses, and the inquiry keeps preparing for a few minutes. Be that as it may, what I was keen on was what happens when waste gathering occurs in the CLR runtime or if the GC is summoned so I utilized a straightforward PowerShell content that would circle and physically compel the GC to run.

NOTE: THIS IS NOT A RECOMMENDED PRACTICE IN PRODUCTION CODE FOR A LOT OF REASONS!

while (1 – eq 1) {[System.GC]::Collect() }

Once the PowerShell window was running, I very quickly started to see CLR_MANUAL_EVENT holds up happening on the parallel subtask strings (demonstrated as follows, where exec_context_id is more noteworthy than zero) in sys.dm_os_waiting_tasks:

Since I could trigger this conduct and it was beginning to end up obvious that SQL Server isn’t really the issue here and may simply be the casualty of other action, I needed to know how to burrow further and pinpoint the underlying driver of the issue. This is the place PerfMon proved to be useful for following the .NET CLR Memory counter gathering for all errands on the server.

This screen capture has been lessened to demonstrate the accumulations for sqlservr and powershell as applications contrasted with the _Global_ accumulations by the .NET runtime. By compelling GC.Collect() to run continually we can see that the powershell occurrence is driving the GC accumulations on the server. Utilizing this PerfMon counter gathering we can track what applications are playing out the most accumulations and from that point proceed facilitate examination of the issue. For this case, essentially ceasing the PowerShell content disposes of the CLR_MANUAL_EVENT holds up within SQL Server and the inquiry keeps preparing until the point when we either stop it or enable it to restore the billion columns of results that would be yield by it.

End

On the off chance that you have dynamic sits tight for CLR_MANUAL_EVENT causing application stoppages, don’t naturally accept that the issue exists within SQL Server. SQL Server utilizes the server level refuse accumulation (at any rate preceding SQL Server 2017 CU4 where little servers with under 2GB RAM can utilize customer level waste gathering to decrease asset use). In the event that you see this issue happening in SQL Server utilize the .NET CLR Memory counter gathering in PerfMon and verify whether another application is driving rubbish accumulation in CLR and obstructing the CLR undertakings inside in SQL Server accordingly.

Leave a Reply

Your email address will not be published. Required fields are marked *

Bitnami