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.
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
ORDER BY Title
SELECT top 100 *
ORDER BY Title
– CollID Integer
SELECT CollectionID + ‘ – ‘ + Name AS DisplayName, CollectionID, CollID
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
rs.Name0 AS DeviceName,
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.