Like many of you, my SCCM environment contains a rather large number of collections (1000+). These collections are used for various purposes from identifying systems with certain Software installed, or identifying systems by Hardware Attributes such as Make, Model or Free Disk Space.
For each one of these collections, we have different ways we can populate them with members. We can use Direct Memberships, Collection Queries, or Collection Include/Exclude rules. Microsoft has a nice little guide showing How to Create Collections which gives an explanation of each. Go ahead and read up, I’ll wait…
Ok, now that you are all caught up on the various Collection Membership Rules, I want to dive into the Query Rule a bit further. Again, Microsoft has some information on How to Create Query Rules. If you are unfamiliar with this process, please read up before continuing.
Scenario
Lets say your environment has 10,000+ clients and you need to define a collection of systems that have Microsoft Visio Professional 2016 installed. Lets lay out the criteria for this collection before we build it.
- The collection must contain ALL instances of ‘Microsoft Visio Professional 2016’ regardless of architecture (x86 and x64)
- The collection should be updated once per day and NOT use incremental updates.
If we take the above parameters into account, we should be able to come up with a collection query rule that looks something like this:
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType ,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier ,SMS_R_SYSTEM.ResourceDomainORWorkgroup ,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = 'Microsoft Visio Professional 2016' or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = 'Microsoft Visio Professional 2016'
As you can see from the above query, we are looking at both the 32-Bit and 64-Bit ADD_REMOVE_PROGRAMS WMI class. Once the new collection is created, it will take a few moments for the Collection Evaluator to update the collection membership so we can see how many systems we have. Each environment will vary on how long it takes to execute this query, and how many members the collection has once it has updated.
Analyzing the Results
The Collection Evaluator is the Site System component responsible for executing Collection Membership Queries and ultimately keeping your collections up to date. Microsoft has an excellent tool that comes with the ConfigMgr Toolkit called CEViewer.exe which can be used to see all of your collections and details about their most recent evaluations. Microsoft has a nice post on How to use CEViewer.exe.
If we open CEViewer on our Site Server and look at the last evaluation time for our new collection, we can see how much time it took for that evaluation to occur. In our case here, we see that it took 28.18 seconds to evaluate.
You may be asking what is an “acceptable” threshold for collection evaluations. Unfortunately, I haven’t seen anything from Microsoft on the subject so here is my own personal recommendation. If a collection evaluation takes more than 20 seconds, you should look at optimizing the query rules.
Help! My collection evaluations are taking too long!
There are a couple of really simple tweaks we can make to help reduce our overall collection query evaluation times. (NOTE: Making changes to existing collections or collection queries will immediately cause that collection to update its membership)
- Use SELECT DISTINCT
- Split up your Query Rules into individual Queries.
Lets start with the first item. Using SELECT DISTINCT on all your query rules ensures that when a query rule is evaluated, each potential system will only be returned one time. We can see the behavior of this using the Monitoring > Queries node in the ConfigMgr console. Lets take a look at the difference between these two queries. First, the “bad” way.
If we copy the query rule from above into a new Query Rule (Monitoring > Queries > New Query Rule) and execute it, we can see from the following screenshot that each Resource ID gets returned multiple times. In this instance, they were each returned 59 times!
Now, lets try it using SELECT DISTINCT.
select distinct SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType ,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier ,SMS_R_SYSTEM.ResourceDomainORWorkgroup ,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = 'Microsoft Visio Professional 2016' or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = 'Microsoft Visio Professional 2016'
After running this new query, you can see that each Resource ID is only listed once and the total execution time is waaaay less.
Now, lets go back to our collection and change it to use SELECT DISTINCT. There are two ways you can do this. First, is to edit the WQL directly as I’ve shown above. The other (easier) way, is to just check this box. And if you ask me, this should be checked by default!
Divide And Conquer
The second way to speed up your collection evaluations is to split up your query rule into multiple query rules. In our example, we are joining three different WMI classes (SMS_R_SYSTEM, SMS_G_ADD_REMOVE_PROGRAMS, SMS_G_ADD_REMOVE_PROGRAMS_64). Running this query essentially pulls all results from all three classes, checks for the matches against DisplayName and THEN finally pulls them into the collection. Even with SELECT DISTINCT, we are still having to pull ALL DISTINCT results from each class.
To improve the performance here, simply split out your query against SMS_G_ADD_REMOVE_PROGRAMS and SMS_G_ADD_REMOVE_PROGRAMS_64 into their own queries. And don’t forget to use SELECT DISTINCT!
select distinct SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Visio Professional 2016"
select distinct SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Visio Professional 2016"
Now if we evaluate collection membership and go back to CEViewer, we can see that the evaluation time has drastically been reduced to well within our artificially defined “threshold”.
Conclusion
To recap, use CEViewer to keep an eye on your Collection Evaluations. In addition, when creating your collection queries make sure to use SELECT DISTINCT and split out your query rules to improve performance where possible.
![clip_image001[5]](https://deploymentramblings.wordpress.com/wp-content/uploads/2016/09/clip_image0015.jpg "clip_image001[5]")
![clip_image001[7]](https://deploymentramblings.wordpress.com/wp-content/uploads/2016/09/clip_image0017.jpg "clip_image001[7]")
deploymentramblings 