SCCM Software Update Summarization is bad for you son

Our sql server was getting hit hard by a query. Initially I wrote a script to email me when queries were taxing the server. I saw the query but thought it was a one off. Looking into ssd wear levels this same query was writing A LOT so I decided to use another tool to find all of the heavy hitters.

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: