Today I was writing a report to help audit collection settings to ensure we aren’t unnecessarily updating collections due to the wrong checkboxes being set (a topic for another day). As I was searching for offending collections, I would copy the collection name from my report then paste into the search box and hit Enter. I also had the All Subfolders search option selected. Generally, I wait about a minute for searches to come back when searching from a top level folder. At some point, I had navigated away from the search pane and when I returned and searched again, my result returned in about 1 second. That NEVER happens, so of course I had to investigate. Here’s what I found:

*The information below uses Device Collections as examples, but it applies to any folders in the console.

Quick Background

When we first implemented SCCM/ConfigMgr, we were coming from a homegrown system and attempted to mimic that system as we migrated to ConfigMgr 2007 then 2012.  During our Windows 7 deployment, we began moving our software installation workload into ConfigMgr and decided to use a folder structure to help organize things.  We are 6 years in and have around 1500 applications with a large number of our ~2000 collections, being computer collections with direct memberships (we are working on this as well…). The folder structure has been painful as it as grown into ~1000 nested folders nested something like this Device Collections\Desktop\Software[Manufacturer Name][Application Name][Collection with Application Name and Version]. Up until recently, searching for a collection wasn’t terribly useful if you needed to find it’s folder location, but fortunately the path has been added as a column in the console now.

Searching

From the Root

From the ConfigMgr console, select the Device Collections node. Then click in the Search box. The menu bar should change to show the All Subfolders icon.  In my experience, it doesn’t show up sometimes. Simply click away from the search dialog then back in and it will generally appear.  Be sure the loading indicator (green light bar thing just under the menu when busy). It should look like this:

From here, you can simply type your search term(s) and click Search. That’s the basics. From there, you can also select the Add Criteria button just to the right of the Search button. You can use this to more specifically define your search criteria (though I’m not going to cover using this feature here, though it does positively impact search performance).

From a Folder

As you have probably guessed, since we have folders, we use them. My process is to drill into Device Collections\Desktop\Software, then begin my search. My thinking is, if I drill down to Software, I don’t have to search as many other folders/collections and I should help speed up the query and while this is true, you will see why it’s problematic if you have a lot folders.

The Difference

Here’s a little video demonstrating the time difference between the 2 methods.  Most people zone out well before the Folder Method returns. Spoiler: The Root Node search is basically instant, while the Folder search (with ~900 subfolders) takes about 45 seconds!!

What’s going on in there?

I’ve learned that the SMSProv.log on your site server (I only have a single primary, not sure which server you’d look at in a larger infrastructure) shows users and the actions they perform in the console in real time.  I opened the log and watched it in CMTrace as I performed the various queries was amazed at the things I found. (Some items are formatted for readability and Names/IDs have been changed to protect the innocent). When you open the log, each entry is created in a block similar to this one. You are looking for a block that contains the queries as highlighted below:

Space vs No Space

When you add a space to your search term, the terms are each broken apart into separate SQL clauses for comparison.

No spaces, the term is compared to several columns in SMS_Collection

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

--Search Term "Test"
SELECT * 
FROM
  SMS_Collection
WHERE
  (CollectionType=2) AND
  ((
    CollectionID LIKE '%Test%' OR 
    Comment LIKE '%Test%' OR 
    ObjectPath LIKE '%Test%' OR 
    LimitToCollectionName LIKE '%Test%' OR 
    MemberClassName LIKE '%Test%' OR 
    Name LIKE '%Test%'
  )) 
ORDER BY Name

You can see here that the terms are separated into separate clauses.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

--Search Term "ts testing"
SELECT  *
FROM
  SMS_Collection
WHERE
  (CollectionType=2) AND
  (
    (
      CollectionID LIKE '%"ts%' OR
      Comment LIKE '%"ts%' OR
      ObjectPath LIKE '%"ts%' OR
      LimitToCollectionName LIKE '%"ts%' OR
      MemberClassName LIKE '%"ts%' OR
      Name LIKE '%"ts%'  
    ) AND
    (
      CollectionID LIKE '%testing"%' OR
      Comment LIKE '%testing"%' OR
      ObjectPath LIKE '%testing"%' OR
      LimitToCollectionName LIKE '%testing"%' OR
      MemberClassName LIKE '%testing"%' OR
      Name LIKE '%testing"%'
    )
  ) 
ORDER BY Name

Searching from a Folder vs the Root Node

When you search from within a folder, all of the folder IDs are added to the query. In my case, the log truncates the full statement when I search from the Desktop folder, so the below example is drilled down several folders deep so we can get the full query statement.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

select  all  SMS_Collection.SiteID, SMS_Collection.CollectionType, 
SMS_Collection.CollectionVariablesCount, SMS_Collection.CollectionComment, 
SMS_Collection.CurrentStatus, SMS_Collection.HasProvisionedMember, 
SMS_Collection.IncludeExcludeCollectionsCount, SMS_Collection.IsBuiltIn, 
SMS_Collection.IsReferenceCollection, SMS_Collection.ISVString, SMS_Collection.LastChangeTime, SMS_Collection.LastMemberChangeTime, 
SMS_Collection.LastRefreshTime, SMS_Collection.LimitToCollectionID, 
SMS_Collection.LimitToCollectionName, SMS_Collection.LocalMemberCount, 
SMS_Collection.ResultClassName, SMS_Collection.MemberCount, SMS_Collection.MonitoringFlags, 
SMS_Collection.CollectionName, SMS_Collection.ObjectPath, SMS_Collection.PowerConfigsCount, 
SMS_Collection.RefreshType, SMS_Collection.ServiceWindowsCount, SMS_Collection.UseCluster 
from vCollections AS SMS_Collection where ( SMS_Collection.CollectionType = 2 AND
((((SMS_Collection.SiteID like N'%Testing%' OR SMS_Collection.CollectionComment like N'%Testing%') OR
SMS_Collection.ObjectPath like N'%Testing%') OR SMS_Collection.LimitToCollectionName like N'%Testing%') OR
SMS_Collection.ResultClassName like N'%Testing%' ) OR SMS_Collection.CollectionName like N'%Testing%')) 
order by  SMS_Collection.CollectionName

Here’s the same query but searching from a folder that has subfolders:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

select  all  SMS_Collection.SiteID, SMS_Collection.CollectionType,
SMS_Collection.CollectionVariablesCount, SMS_Collection.CollectionComment,
SMS_Collection.CurrentStatus, SMS_Collection.HasProvisionedMember, SMS_Collection.IncludeExcludeCollectionsCount,
SMS_Collection.IsBuiltIn, SMS_Collection.IsReferenceCollection, SMS_Collection.ISVString,
SMS_Collection.LastChangeTime, SMS_Collection.LastMemberChangeTime,
SMS_Collection.LastRefreshTime, SMS_Collection.LimitToCollectionID, SMS_Collection.LimitToCollectionName,
SMS_Collection.LocalMemberCount, SMS_Collection.ResultClassName, SMS_Collection.MemberCount,
SMS_Collection.MonitoringFlags, SMS_Collection.CollectionName, SMS_Collection.ObjectPath,
SMS_Collection.PowerConfigsCount, SMS_Collection.RefreshType, SMS_Collection.ServiceWindowsCount, SMS_Collection.UseCluster 
from  vCollections AS SMS_Collection  
where  ( SMS_Collection.SiteID in  
(select  allFolder##Alias##810314.InstanceKey 
from vFolderMembers AS Folder##Alias##810314  
where(    Folder##Alias##810314.ObjectTypeName = N'SMS_Collection_Device' AND 
Folder##Alias##810314.ContainerNodeID in(   12345678,23456789,34567890,45678901,56789012,67890123,78901234,89012345    )) )  AND
(SMS_Collection.CollectionType = 2 AND
(((((SMS_Collection.SiteID like N'%Testing%' OR SMS_Collection.CollectionComment like N'%Testing%' ) OR
SMS_Collection.ObjectPath like N'%Testing%'  ) OR
SMS_Collection.LimitToCollectionName like N'%Testing%'   ) OR
SMS_Collection.ResultClassName like N'%Testing%'    ) ORSMS_Collection.CollectionName like N'%Testing%') ) ) 
order by  SMS_Collection.CollectionName

The key difference here is that the sub folder query adds this block of code. Each number represents the ID of a folder in the tree. The more subfolders that are beneath the folder you are searching from, the longer the string.  In my case, when searching from a top folder, the log truncates the folder ID list because there are so many -  900 folders x (8 digits + 1 comma) = 8100 characters - this is in addition to the rest of the characters in the SQL statement.

1
2
3

SMS_Collection.SiteID in  (select  allFolder##Alias##810314.InstanceKey from
vFolderMembers AS Folder##Alias##810314  where(    Folder##Alias##810314.ObjectTypeName = N'SMS_Collection_Device' AND
Folder##Alias##810314.ContainerNodeID in(   12345678,23456789,34567890,45678901,56789012,67890123,78901234,89012345    )) )

If you were to do the same tests and run these queries manually within SQL, you’d find that there’s barely a difference in performance between searching from the root node and a sub folder.  However, the piece that’s missing from the SMSProv.log (it may be elsewhere, I just haven’t found it) is the part where the console has to create the string of subfolders that it passes to the query.  If you watch the log as you click Search, you will see that the log pauses at the point just before it outputs the above queries. This would indicate that there’s a process possibly looping through each folder in in the tree to build the list. I can’t prove it with the data I have at this point, but this certainly appears to be what’s happening behind the scenes.

Bottom Line

If you need to have folders, search from the top, or drill down to a lower level before searching to decrease the time required to process the search.  While I’m sure you can get clever with your collections names and forgo folders (or as many folders), I haven’t gone down that path at this moment and I suspect that it will take some time convincing my teammates that it’s a worthwhile endeavor. Also keep in mind that when navigating through the tree and expanding each node, the console is performing a set of queries for each node that you click through.

Choose Your Folders Wisely!