SCCM Collection Evaluation Duration – SQL Query

The Problem:

My collection membership seems to take forever to update! Also, for some reason the console seems SUPER slow lately.

One of the COMMON Solutions

Collection evaluation is a common sticking point that causes major performance issues in any well constructed environment. In fact it’s probably the number one cause of console slowness and a major pain point for “why hasn’t my machine got X software yet”.

Without going into much detail, when a collection’s membership is evaluated one of the primary site servers MUST evaluate the collections membership rules. Because of how the collection evaluation process works only one collection may be evaluated at a time. Due to this design limitation it is imperative to have some method of determining how long a collection is taking to evaluate. This is doubly important in an environment where you are either not the only administrator OR are not responsible for deploying software using SCCM.

While the SCCM toolkit does include a server tool that will allow you to view evaluation times, CEviewer, if you have multiple primary site servers you must connect to each primary site server on a case by case basis.

The following SQL query is something I use in my environment in order to get the evaluation time of all collections in the environment in seconds. I then take the data and manipulate it via SSRS and output a meaningful report to my team members.

My environment has four primary’s in it and if you have more or less simply remove or add more lines to the site number “case” section and run against your CAS. Please also note this evaluation time is based off of a FULL evaluation and not an incremental evaluation.

Select T2.CollectionName
  , (CAST(T1.EvaluationLength as float)/1000) as 'Time Spent On Eval'
  , CASE T1.SiteNumber
       WHEN 1 THEN 'PR1'
       WHEN 2 THEN 'PR2'
       WHEN 3 THEN 'PR3'
       WHEN 4 THEN 'PR4'
       Else 'UNKNOWN'
    End as SiteNumber
from DBO.Collections_L as T1
Inner Join Collections_G as T2
on T2.CollectionID = T1.CollectionID

Once you have performed this or a similar query you’ll be presented with a list of collections and their assorted run times. Simply sort the output by the “Time Spent on Eval” column and then investigate why those collections are taking so long to process.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: