ConfigMgr\MEM 101: Advanced Performance Troubleshooting Part 2

As mentioned in Part 1 of this series, there are multiple different causes for performance issues on ConfigMgr servers.

We have had a look at SQL Troubleshooting here and Inbox troubleshooting in the previous post.

What we have failed to look at however, is on the day-to-day management and housekeeping of ConfigMgr, namely Collections

Collections are the key component in managing clients in Configuration Manager

Collection Basics

There are a couple of Collection Basics that we need to be aware of:

Collections are a logical grouping of resources, either computers or users, based on a common need for those resources

When we speak about performance for collections

• Processing speed depends entirely on SQL servers and DRS performance
• Collections are processed on all Primary sites. The slowest site can be the bottleneck for the whole hierarchy
• The number of collections will grow over time. Introduce procedures to review and delete unused items
• Be aware of the query performance when creating collection query rules

Long running queries can slow down not only collection evaluation, but also affect SQL performance in general.

This is why the Colleval.log is so important for you to monitor

So what is Colleval.log?

Colleval.log is important in investigating collection evaluation performance and collection dependency chain issues

• Collection evaluation is processed by the component SMS_COLLECTION_EVALUATOR (CollEval) only on Primary Site Servers, not on the CAS or Secondary Sites
• Each Primary Site’s CollEval runs SQL queries against its database
• CollEval has 4 threads for different evaluation types
• Activity is logged in <ConfigMgr Installation>\Logs\colleval.log
• CollEval actions are triggered by empty files put in \inboxes\colleval.box\
• An hourglass icon for a prolonged amount of time on collections is one of the first signs of a performance issue
• CollEval prioritizes manual actions above scheduled updates, the latter need at least one minute of Colleval idling

• Collection evaluation is handled by different threads depending on the type of evaluation
• When one collection query runs, other evaluations wait in thread queue
• If Express/Incremental evaluation finds too many changes, a Full evaluation will be triggered

So why do so many customers enable Incremental updates? That is a question that I ask regularly ask my customers. DO you REALLY need to know every 5 minutes if X has changed?

Sure there are instances where this is valid and required, however the majority of the time, this does not make up for the potential issues that are raised with Incremental updates

So what are the Caveats for Incremental Updates?

If number of Resource IDs multiplied by Number of Incrementally-enabled collections > 10 000 000, Colleval starts Full Evaluation In this case, look for “spCollBeginIncEvaluation: Too many changes, marking incremental collections for full evaluation” in Colleval.log

Do not enable Incremental updates on collections targeting views  with frequently changing data, such as v_CH_ClientSummary

Incremental updates do not work with CI compliance tables

The CollectionNotifications table is a key element storing changed records between Incremental Evaluations

Collection Update Types

Direct Collection Rules

• Resource IDs are added as a result of creating a Direct Rule
• Direct Rules can be found in the v_CollectionRulesDirect view
• Collections are not re-populated if the Resource IDs change
• Warning – A full update of every dependent collection runs every time you add a new Direct rule

Query Collection Rules

• Collection query rules allow administrator to create Select performance-impactful statement that can affect all Collections updates
• Query Rules are recommended to use over Direct Membership because:
  • Dynamically add/remove members
  • Update on a schedule
  • Little or no maintenance
  • Resources re-populate query-based Collections in case of deletion
• Collection rules can be found in v_CollectionRuleQuery view and dbo.Collection_Rules_SQL table

Membership changes during Full or Incremental Collection updates, will trigger Full or Incremental update of all the dependent collections

Collection have dependent collections if those are…

• Collections limited by this collection
• Collections having include rule with this collection
• Collections having exclude rule with this collection

This will effectively mean that all collections depend on “All Systems”

Build Collections using Indexed Fields

In order to discover what indexed Collumns are available, you can run

USE CM_Sitecode
SELECT       TableName = t.name,     IndexName = ind.name,    
IndexId = ind.index_id,    ColumnId = ic.index_column_id,
ColumnName = col.name,     ind.,     ic.,     col.*
FROM       sys.indexes ind
INNER JOIN       sys.index_columns ic ON  ind.object_id = ic.object_id
and ind.index_id = ic.index_id
INNER JOIN       sys.columns col ON ic.object_id = col.object_id
and ic.column_id = col.column_id
INNER JOIN       sys.tables t ON ind.object_id = t.object_id
WHERE       ind.is_primary_key = 0      
AND ind.is_unique = 0      
AND ind.is_unique_constraint = 0      
AND t.is_ms_shipped = 0
ORDER BY t.name, ind.name, ind.index_id, ic.index_column_id

Typical Bottlenecks for Collections

1. Single query rule indefinitely in “running” state
2. Thousands of small queries running 24/7 due to long evaluation graphs and/or too frequent schedules
3. Data Replication Service queue issues
4. Generally slow SQL Performance ( TempDB in most cases is the culprit here)

So what are the Collection Best Practices?

Use Query rules, not Direct rules

• Adding a Direct rule will cause collection update
• Creating Query rule by resource name helps to avoid collection updates, but causes CollEval errors if the list becomes empty

Use indexed columns for building query rules

use SMS_R_System.NetBios_Name instead of SMS_R_System.Name or SMS_InstalledSoftware.ProductName instead of ARPDisplayName

Do not enable incremental evaluation on collections without actual need

Always mind the resulting SQL query cost:

• Use JOIN for large tables (e.g. SoftwareFile) with caution
• Avoid using LIKE with double wildcards: %SMTH% – as well as NOT LIKE
• Do not use ORDER BY or DISTINCT statements (useless)

Implement regular maintenance tasks for administrators to remove unused collections

By keeping your collections lean, relevant and using the correct type of queries, you will have less performance issues in your MEM infrastructure

As of 1902 Version of SCCM\MEM, you can also use the Management insights listed here to gain a better view of your infrastructure and proactively start planning on removing dead collections.