ConfigMgr Collections: Dos and Don’ts

Collections are very important in ConfigMgr, though there are some limits. We will explore some of those limits and I will provide some PowerShell commands to help you.

Collection Limits

First, there is a limit to the number of collections that you have. It’s dependent on the resources that you allocate to your SQL server. The more resources, the more collections you can have. Remember that for every collection you create, two corresponding views are created in the database. This can make your database very large and can consume a lot of resources.

Collection Updates

Second, collection updates is the most resource intensive process that ConfigMgr performs. You should really think about whether or not you need incremental updates turned on and how often your collections reevaluate their membership. You should come up with a strategy as to what collections need both of these options.

To determine which collections have incremental updates enabled, run this PowerShell command (be sure to input the primary site server name and site code where indicated):

 (get-wmiobject -computername <primary site server> -namespace root\sms\site_<site code> -class SMS_Collection | where-object -filterscript {$_.RefreshType -eq “4” -or $_.RefreshType -eq “6”}).Name


Collection updates are also performed using the tempdb on your SQL server, not in the ConfigMgr database. Collection changes are then written into the ConfigMgr database. You will know fairly quickly if you have a collection update problem because this database will get very large, very fast (I once saw one grow to 20GB in four hours). The larger this database is, the slower your environment will run.

If you are having this problem, you can establish which collection is causing the problem by looking at the reports on tempdb. To do that, open SQL Management Studio. Expand Databases, then System Databases. Right-click on tempdb, select Reports, then Standard Reports, then “Top Transactions by Age”.


That will open the report and you can see what is causing the database to fill up. Unfortunately, it does not give you a collection name, but hopefully you can determine which collection it is by the query (it displays the query that is used to build the collection).

like and not like

If you can help it, do not use like or not like when building your collection queries. These are very database intensive, and again, could fill up your tempdb. To determine if you are using any collections with “like” or “not like”, run this SQL query:

 select SiteID, CollectionName

from Collections

where CollectionID in (select CollectionID from Collection_Rules_SQL where SQL like ‘%Like%’ or SQL like ‘%NOT LIKE%’)

I would highly recommend removing any of these, or using them to initially build a collection, then not updating it, or updating it infrequently.


All content provided on this blog is for information purposes only. Windows Management Experts, Inc makes no representation as to accuracy or completeness of any information on this site. Windows Management Experts, Inc will not be liable for any errors or omission in this information nor for the availability of this information. It is highly recommended that you consult one of our technical consultants, should you need any further assistance.



Contact Us

On Key

More Posts

Mastering Azure AD Connect - A Comprehensive Guide by WME
Active Directory

Mastering Azure AD Connect – A Comprehensive Guide

Modern businesses are fast moving toward cloud-based infrastructure. In fact, cloud-based business is not just a trend anymore but a strategic necessity. Microsoft’s Azure Active Directory (Azure AD) has become a frontrunner in this domain. It

Read More »
Security Best Practices in SharePoint
Office 365

Security Best Practices in SharePoint

Microsoft SharePoint is an online collaboration platform that integrates with Microsoft Office. You can use it to store, organize, share, and access information online. SharePoint enables collaboration and content management and ultimately allows your teams to

Read More »
The Ultimate Guide to Microsoft Intune - Article by WME
Active Directory

The Ultimate Guide to Microsoft Intune

The corporate world is evolving fast. And with that, mobile devices are spreading everywhere. As we venture into the year 2024, they have already claimed a substantial 55% share of the total corporate device ecosystem. You

Read More »
Protecting Microsoft 365 from on-Premises Attacks
Cloud Security

How to Protect Microsoft 365 from On-Premises Attacks?

Microsoft 365 is diverse enough to enrich the capabilities of many types of private businesses. It complements users, applications, networks, devices, and whatnot. However, Microsoft 365 cybersecurity is often compromised and there are countless ways that

Read More »
Be assured of everything

Get WME Services

Stay ahead of the competition with our Professional IT offerings.