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? (thanks Brian Mason for the formatting tip!)

— 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.

Advertisements
This entry was posted in ConfigMgr, SQL Server, SSRS. Bookmark the permalink.

One Response to ConfigMgr Software Update Summarization Task

  1. Chuck Herrington says:

    How can I adapt this to report on configuration baseline compliance?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s