More often than not we may have a large SCCM environment with thousands of collections. A common occurrence that I see in environments are when collections are created to report on applications that are installed that have multiple architectures (x86 and x64). An example may be that a collection has been created to find all instances of any family of Microsoft Office 2013 (x86 and x64) as part of an upgrade rollout planning.

So based on this requirement, an administrator will know that you will need to look at referencing the following WMI classes:

• SMS_G_System_ADD_REMOVE_PROGRAMS
• SMS_G_System_ADD_REMOVE_PROGRAMS_64

then provide a condition to look at the DisplayName property for “Microsoft Office%2013%”

If you were to combine this all into a single query this would look 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 like “Microsoft Office%2013%” or
    SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like “Microsoft Office%2013%”

You may think that this is perfect and does all that you need for querying. But if we look further into this collection with CEVIEWER.exe, you can see that the run time of this particular query is 185.4380 seconds.

This may seem like an insignificant impact on the performance of your environment but if you take into consideration that there are thousands of collections that are also taking roughly the same amount of time to evaluate, then this will add up and be detrimental to the performance of SCCM. Generally, I would want to look at collections that take longer than 10-20 seconds to evaluate and see what improvements can be made for better performance.

What can be done to improve this?

Separate and split the query for separate classes. In this scenario, the first thing to do would be to separate out the query into multiple queries so that the query is only looking at a single class.

I have done this by creating the following queries in the collection (make sure to omit duplicate rows).

A query for 32-bit version of MS Office 2013:

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 like “Microsoft Office%2013%”

A query for 64-bit MS Office 2013:

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 like “Microsoft Office%2013%”

So it now looks like this:

Now if we complete this collection creation and initiate an Update Membership action on the new collection, we can then verify the evaluation run time in CEViewer after waiting 10-15min for a comparison:

As you can see, this has vastly improved the performance of the evaluation. 

Summary

This method of splitting up your queries was written to share how I applied this in my environment to optimise collection evaluation queries. Key takeaways:

• Use CEVIEWER to monitor your collection evaluations;
• Split queries when using multiple WMI classes;
• Use Select Distinct to omit duplicate rows;