Return
-
Title
CPU stays close to 100% when deleting playback data -
Description
The CPU on Spotlight Diagnostic server is staying at or near 100% at all times. This seems to be due to a query that’s being run over and over again by Spotlight. The query seems like it is looking to delete old playback data, but it is not doing so in an efficient manner.
The query is:delete from spotlight_playback_data where pb_timestamp < '2010-09-03T08:16:16.590' and pb_domain_name = 'XYZQLMON02:3843'This is being run over and over again, with the timestamp value being incremented a few seconds each time, and as soon as it reaches the cutoff point (seven days), it starts over again at some point far into the past. The end result is that it’s not actually deleting much, since there’s never much new data there to delete, but it’s constantly scanning the table looking for records to delete. This is what’s eating up all the CPU time.
-
Cause
CPU stays close to 100%
-
Resolution
By design, Spotlight runs delete data in playbackdatabase once every 24 hour.
Check pb_timestamp in table spotlight_playback_data of playbackdatabase to see if is holding more than 7 days ( per spotlight store playback data setting option.)
SELECT [pb_id]
,[pb_timestamp]
,[pb_me_name]
,[pb_package_name]
,[pb_table_name]
,[pb_data]
,[pb_domain_name]
,[pb_data_compressed]
FROM [SpotlightPlaybackDatabase].[dbo].[spotlight_playback_data]
If it's holding data older than 7 or 14 days per pb_timestamp column, delete its by using below query.delete from spotlight_playback_data where pb_timestamp < getdate()-7 //to purge all data older than 7 days