CLR and Preemptive wait events drive low performance however CLR Wait Events show nothing (4228287)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

CLR and Preemptive wait events drive low performance however CLR Wait Events show nothing
Description

CLR and Preemptive wait events drive low performance and most of the wait is coming from Preemptive_XE_GETTARGETSTATE from Spotlight and CLR Wait Events show nothing.
Cause

The CLR wait events are the culprit, it causes the instance to show low performance. The Performance Health and Wait Events drill-downs use different ways to collect data and the Wait Events drill unfortunately has not retrieved CLR Wait Events data from Extended Events, so it shows nothing in CLR category and Spotlight thinks that most of the wait is Preemptive, which is wrong and misleading.
Resolution

WORKAROUND:
1. In Spotlight, go to Wait statistics drill-down.
2. Find out CLR wait types affected. (Can use the save as to export as .CSV and copy the names from Excel).
3. Go to default folder:
{Diagnostic Server Install Path}\Agent\conf\Package\sqlserver_spotlight
i.e.
"C:\Program Files\Quest Software\Diagnostic Server\Agent\conf\Package\sqlserver_spotlight"
4. Locate the file called "IdleWaits.txt" and save a backup file and then open it in Notepad.
5. Put the names in the list (one name per line returned) and save the file.
6. Restart the Diagnostic Server Service and see if that will ignore the CLR Wait Events from Performance report.
STATUS:
A fix is available in the latest version of Spotlight on SQL Server Enterprise which can be downloaded here.
Defect ID

DS-872

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title