By Garth Jones
There’s a lot of talk in the Microsoft TechNet forums about System Center Configuration Manager (ConfigMgr) collections. Sometimes these collections take a long while to update after being edited and that can be frustrating.
Here are some of the reasons why collections may take a long time to update:
- SQL CPU is overloaded.
- SQL disk I/O is either too little or too slow.
- Not enough RAM, so memory paging occurs.
- Using an underpowered Virtual Machine (VM).
- Having too many collections updated at the same time.
- A poorly designed WQL query.
It is now quite common for ConfigMgr admins to no longer, “own,” their physical ConfigMgr server. In most cases ConfigMgr is VM-hosted and managed by another team. We all know what it’s like to ask another team to review the server and fix, “slowness issues,” so before you do that you should review your ConfigMgr setup and make sure first that you are not the root cause of the problem.
Let’s examine how a poorly written WQL query will cause ConfigMgr to work harder than it should. Below is an example of a bad WQL query.
I’ll use this example to demonstrate how long ConfigMgr takes to process a poorly written query.
The ConfigMgr logs will tell you how long it takes to update a collection. The highlighted line in the log file below shows the processing start time for the bad query in my example.
If I scroll down in the log file, I will also find the end time of the collection update (a successful collection evaluation). See? It took 1.407 seconds to process this query.
Now I’m sure many of you are saying that 1.407 seconds is not a lot of time. That is true but, what you don’t know is that there are only 33 computers in this lab. This equates to roughly 0.043 seconds for each computer. What would the amount of time be for a larger environment?
I’m not suggesting that you read the log files for every query. Instead I recommend using Collection Evaluation Viewer (CEV)* to quickly review the stats for all of your collections. This way you can work on updating your “bad” WQL queries. Below is a screenshot of what you can expect from CEV.
As you can see from the screenshot above, the Bad query needed 1.3440 seconds (0.041 seconds per computer) to process. Isn’t this format a lot easier to read? You can quickly see what collections need their membership rules reviewed.
The next time you go to update the collection membership and see the dreaded hourglass, remember this post!
In my next blog post, I will provide you with tips on how to reduce the processing time. If you have any questions, please free to contact me @Enhansoft.
*CVE is part of the ConfigMgr Toolkit and can be downloaded here.