-
Title
Spotlight runs "exec master.dbo.sp_trace_getdata @traceid=2, @records=0" -
Description
When monitoring is enabled from Spotlight it starts a sql trace on the monitored database server, below is the profiler trace command it runs.
exec master.dbo.sp_trace_getdata @traceid=2, @records=0
"Enable SQL Analysis" is not checked or enabled from Configuration.
-
Resolution
The sp_trace_getdata stored procedure is run as part of the deadlocks collection - not SQL Analysis.
To disable the deadlock collection:
• On Spotlight, go to the Configure tab and choose scheduling.
• In the “Background Schedules” dialogue:
1. Change the connection drop-down to the appropriate server
2. Find and select the “Deadlock List” collection in the table of collections.
3. Check the “Override the default settings for the collection “Deadlock List” checkbox.
4. Clear the “Collect data when Spotlight is not connected” checkbox.
5. Click OK.
Normally, this collection is low impact, but in extremely rare cases it can cause a performance issue. Obviously disabling the deadlock collection compromises the benefit that Spotlight can provide. Users may need to manually remove the session already running with the tracing command after doing the steps above.