Submitting forms on the support site are temporary unavailable for schedule maintenance. If you need immediate assistance please contact technical support. We apologize for the inconvenience.
Poor performance health appears at least in part due to spotlight monitor agent itself.
Description
Spotlight monitoring account has shown as the user for the alert: TempDB in the Preemptive category.
Most of the wait is PREEMPTIVE_XE_GETTARGETSTATE wait, which can occur in over 95% of total wait time.
Cause
Spotlight now uses SQL Server Extended Events to gather data for these three features:
Deadlocks
Wait Events
Workload Analysis
This issue we feel is related to a Microsoft SQL Server issue as described by Microsoft kb2511963. Please review both of the following Microsoft Links.
We haven't had reports of this in SQL Server 2012 and up. So presumably, Microsoft addressed the issue there.
Resolution
There are 2 possible workarounds:
Disable Spotlight from using Extended Events by going to "Configure | Connections " select the problematic connection and uncheck “Use Extended Events.” Keep in mind that when you disable the use of Extended Events, Spotlight will still be able to gather deadlock data by using SQL Trace but you will 'not' be able to use Wait Events or Workload Analysis as they depend exclusively on the use of Extended Events to get these data. Inferring from the Microsoft KB articles above, it could be necessary to wait for the next SQL Server schedule re-boot to see the SQL Server WAIT disappear since the session can't be killed unless with a re-boot.
Change the frequency of calls that affect the WAIT by increasing the collection schedule so as not to collect so frequently for those servers.
Go to Configure | Scheduling
Choose the SQL Server connection from the drop down menu
Look for...
- Deadlock
- Waits Events
- Workload Analysis
... and increase the collection schedule to hours and not minutes.
Your Request will be reviewed by our technical reviewer team and, if approved, will be added as a Topic in our Knowledgebase.
Recommended Content
Product(s):
Spotlight on SQL Server Enterprise
11.6.1
Topic(s):
Configuration
Article History:
Created on: 9/30/2016 Last Update on: 5/7/2023
Thank you for your feedback for Topic Request
Your Request will be reviewed by our technical reviewer team and, if approved, will be added as a Topic in our Knowledgebase.
Welcome to Quest Support
You can find online support help for Quest *product* on an affiliate support site. Click continue to be directed to the correct support content and assistance for *product*.
Search All Articles
IE 8, 9, & 10 No longer supported
The Quest Software Portal no longer supports IE8, 9, & 10 and it is recommended to upgrade your browser to the latest version of Internet Explorer or Chrome.
