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
tempdb
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.
Disclaimer
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.
