One of the common issues i have seen over the years when all of a sudden the collections would not refresh and collection membership would not update.You might simply see the hour glass on the collections, Generally nothing was changed on existing collections but a new collection might have been added.
This could generally happend if you have an unoptimized query , querying big tables like 'software files.'
There are multiple ways to look at it including colleval.log with sql enabled logging.
If you have some experience with SQL you could try looking if the Query is a long runner.
Use the SQL Management Studion and Connect to your SCCM/SMS Database
a) Run the below query on SQL
Find the offending SPID with high CPUTime and DISKIO eg SPID 67
b) Find the contents of the query
dbcc inputbuffer (67)
this will show the query example below
insert into #CollTemp (MachineID,ArchitectureKey,Name,SMSID,Domain ,IsClient) select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from System_DISC AS SMS_R_System INNER JOIN vSMS_G_System_SoftwareFile AS SMS_G_System_SoftwareFile ON SMS_G_System_SoftwareFile.ClientId = SMS_R_System.ItemKey INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey where (SMS_G_System_SoftwareFile.FilePath like '%C:\pwrpoint.exe%' OR __System_ADD_REMOVE_PROGRAMS0.DisplayName00 = 'SRSQuery')
Note : The above query is a sample query
C) Ran the below query to find the collection name based on the above query.
select * from collection_rules where collectionID in (select collectionid from collection_rules_sql where sql like '%<text snippet from step b>%')
select * from collection_rules where collectionID in (select collectionid from collection_rules_sql where sql like '%pwrpoint.exe%')
Remarks % is a wild card in SQL
d) Now you can decide to either modify the query / remove to avoid the problem.
I hope you find this post useful.
Jeevan S Bisht
This helped me out today. Thank you!