Right click your sqlserver then select reports -> standard reports -> Performance - Top Queries by Total IO
Look at the the Ave. Logical IO bar column and the once that spike are the queries with heavy writes.
click the + next to each query and it will tell you whats it's doing.
merge Update_ComplianceSummary dst using (select * from v_Update_ComplianceSummary_Live where CI_ID in (select CI_ID from @ci) and NumUnknown>=0) src on dst.CI_ID=src.CI_ID when matched then update set LastSummaryTime=src.LastSummaryTime, NumTotal=src.NumTotal, NumUnknown=src.NumUnknown, NumNotApplicable=src.NumNotApplicable, NumMissing=src.NumMissing, NumPresent=src.NumPresent, NumInstalled=src.NumInstalled, NumFailed=src.NumFailed when not matched then insert (CI_ID, LastSummaryTime, NumTotal, NumUnknown, NumNotApplicable, NumMissing, NumPresent, NumInstalled, NumFailed) values(src.CI_ID, src.LastSummaryTime, src.NumTotal, src.NumUnknown, src.NumNotApplicable, src.NumMissing, src.NumPresent, src.NumInstalled, src.NumFailed) ;
There will be a table like the following:
Considering that the only other query of note performed 50k writes once a night vs 1,000K hourly I know this was the problem.
Googling led me to this these threads
https://social.technet.microsoft.com/Forums/en-US/5a8adf02-7e9f-40e2-b849-f7c6135d7312/sum-update-status-summarizer-consumes-100-cpu-every-hour-for-15-minutes?forum=configmanagergeneral
https://social.technet.microsoft.com/Forums/en-US/84c58af9-b340-4e96-876b-e7b7d3ddb144/software-update-summarization-process-uses-90-of-cpu?forum=configmanagergeneral
Which state that there is a bug in the Software Update Summarization report. And you essentially need to change it's schedule.
Using the SCCM console click on Administration -> Sites -> right click -> Status Summarizer
select Site System Status Summarizer and change that to weekly
Under Software Library -> All Software Updates -> Schedule summarization
change to Recur every 7 days
Here is the cpu after the changes were made:
