First published on TECHNET on Mar 04, 2019
Hello! My name is Matt Balzan and I am a PFE with the Windows and Devices team, in the UK. I was recently at a customer where they asked me if I could analyse why their collections were taking so long to evaluate (at least 4 hours!).
After digging about in their database I found they had way too many collections with Incremental Updates or collections with hardly any members in them - this resulted in a massive performance hit on their database.
In total there were:
As we know SCCM logs everything in logfiles and its own SQL database. Therefore all that was required was to create a dashboard to display the following information:
And here it is. See content and use explained below.
TOP & MID SECTION
This report is split into 3 parts:
The TOP section displays the evaluation times which range from 0-10, 10-20 and 20 and above in seconds. The yellow and red charts are hyper-linked to the tables shown in the bottom section.
The MID section displays the Collection Refresh Types, Total Collections and Collections with less than 5 members, the Highest Collection Modifier plus some modifier details. The Collection Refresh Types is also hyper-linked to table in the BOTTOM section.
All tables include a legend with the following detail:
S: Site Code | ID: Collection ID | CN: Collection Name | MC: Member Changes | M: Member Count | LMCT: Last Member Change Time | CS: Current Status | Q: Query | T: Time Evaluation (seconds)
The following table shows the legend with this detail:
ID: Collection ID | CN: Collection Name | LCN: Limit To Collection Name | M: Member Count | RF: Refresh Type
SUB REPORT – COLLECTIONS LESS THAN 5 MEMBERS
This report will show you:
ID: Collection ID | CN: Collection Name | M: Member Count | CT: Collection Type | CS: Current Status | LCN: Limit To Collection Name | RF: Refresh Type
HOW TO USE THESE REPORTS
GRANT SELECT ON [Collection_Rules_SQL] TO [smsschm_users]
GRANT SELECT ON [Collections_L] TO [smsschm_users]
GRANT SELECT ON [Collections_G] TO [smsschm_users]
[Edit: 08/06/2021] - You can download the reports located at the end of this blog!
Now you can start troubleshooting those problematic collections, begin tackling the ones that are taking too long to evaluate and investigate why the SQL/WQL queries are taking too long to return the desired data.
Hope this helps with your SCCM Collection housekeeping! In the next blog I will show you how to fix the issues with your collections and the best practices to keep your environment in good shape.
++Special kudos to Ross Moore & Doug Varner for testing this in their environments!
The sample files are not supported under any Microsoft standard support program or service. The sample files are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample files and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the files be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.
Matt Balzan | Premier Field Engineer | SCCM, Application Virtualisation
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.