ConfigMgr Software Update Summarization Task

By default the summarization task runs every 1 hour, on a very large sites it causes a large spike in CPU utilization.

What follows is a summary of investigating this issue on a very large site of approximately 150,000 clients.

The investigation began to determine why the CM console was running so slow. We updated the statistics, updated the indexes and found that things were still a little slow, particularly the software update compliance console view.

For the server patching, we have a 4 hour window to patch, verify results and manually patch any server that did not update. The SUM task was not providing the results fast enough. And, when we started digging into what stored procedures were hitting our servers the hardest, here’s what we found.

~~~

After researching the top “heavy” SQL stored procedures run against our sites, it turns out that #3, after the DRS site replication tasks, is the site summarization task. Presently set to run one time per day, the only thing this does, besides use a LOT of CPU and disk resources, is update the summarization used in the console display. Reference the top 5 most IO intensive queries in use at our largest primary site.

image

Note the Summarization task is number 3 in the list!

After discussion with several other MVPs, most set this summarization cycle to once every 31 days.

If the summarization is really needed, it would be best to run the appropriate report.

The problem arose that with Summarization set to every 31 days, the Compliance reports no longer gave the latest information since the views they used were reliant on summarized data. To solve this issue, I ended up creating 2 new reports based on Software Updates – A Compliance reports – Compliance 1 & Compliance 7 reports, based on the live views instead of the summary views. Now, the compliance data can be viewed within minutes from the time of update.

Here is the core SQL used for the 2 reports. I apologize in advance for the terrible formatting, if anyone knows of a good way to format this for wordpress, let me know?

– Update Group

– CI_ID Integer

SELECT Title, CI_ID, CI_UniqueID AS AuthListID

FROM v_AuthListInfo

ORDER BY Title

SELECT top 100 *

FROM v_AuthListInfo

ORDER BY Title

– Collection

– CollID Integer

SELECT CollectionID + ‘ – ‘ + Name AS DisplayName, CollectionID, CollID

FROM v_Collection

ORDER BY Name

– Compliance 1 (live updates)

SELECT        ali.Title, CASE ugsl.Status

WHEN 0 THEN ‘Compliance state unknown’

WHEN 2 THEN ‘Non-compliant’

WHEN 3 THEN ‘Compliant’

ELSE ‘Unknown’ END AS State,

COUNT(*) AS Total, ali.CI_UniqueID AS AuthListID, vcm.SiteID, ugsl.Status AS StatusID

FROM            v_UpdateGroupStatus_Live AS ugsl INNER JOIN

                         v_AuthListInfo AS ali ON ugsl.CI_ID = ali.CI_ID INNER JOIN

                         vCollectionMembers AS vcm ON vcm.MachineID = ugsl.ResourceID

WHERE        (ali.CI_ID = @UpdateGroup) AND (vcm.CollectionID = @Collection)

GROUP BY ali.Title, ugsl.Status, ali.CI_UniqueID, vcm.SiteID

– Compliance 7 (live updates) level 2

SELECT  ugsl.ResourceID,

                rs.Name0 AS DeviceName,

                vcm.Domain,

                asite.SMS_Assigned_Sites0 as AssignedSite,

rs.Client_Version0 AS ClientVersion,

rs.User_Domain0 + ‘\’ + User_Name0 as LastLoggedOnUser

FROM  v_UpdateGroupStatus_Live ugsl

                INNER JOIN v_AuthListInfo ali ON ugsl.CI_ID = ali.CI_ID

                INNER JOIN vCollectionMembers vcm ON vcm.MachineID = ugsl.ResourceID

                 INNER JOIN v_R_System rs on rs.ResourceID = ugsl.ResourceID

                LEFT JOIN v_RA_System_SMSAssignedSites asite on asite.ResourceID = ugsl.ResourceID

WHERE   ali.CI_ID = @UpdateGroupID

                AND vcm.CollectionID = @CollectionID

                AND ugsl.Status = @StatusID

Note: there may be some enhancements in the next version of CM2012 to correct this… will post an update if it proves that this issue is resolved.

 

email

Written by , Posted .