ConfigMgr slow to display collection members but queries are normal

Question

question

MorrisseyEvan-2676 asked · Mar 10 at 09:05 PM

ConfigMgr slow to display collection members but queries are normal

Recently upgraded to ConfigMgr CB2010. Full disclosure I made a stupid mistake where I forgot to re-enable our SQL backup jobs after the upgrade. So, of course, a couple days later everything ground to stop when the disks ran our of space. That may not be related, but, just in case... That was resolved easy enough, run a backup, shrink the logs, fix replication.

So currently ConfigMgr seems to work fine except when you try to open a collection. It can take a minute or two for a small (fewer than 10) collection, but takes absolutely forever (10+ minutes) for a larger (200+) collection. What's odd is that queries run fine, and collections update their membership just fine. In fact EVERYTHING seems to work fine except display members. Unfortunately this hard to research as every result is about collections slow to update membership which is not the problem.

This affects all users, and the problem occurs even when using PowerShell to retrieve devices

My hierarchy is a CAS with a single PRImary site (used to have more if you're wonder why there's a CAS). If the console is connected to the PRI then this problem disappears. If the console is connected to the CAS the collections are slow to display members.

Any help would be appreciated as this is driving me crazy!

mem-cm-general

· 2

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MorrisseyEvan-2676 · Mar 12 at 09:17 PM

Ok, this may be the issue... I watched the SMSProv.log when I tried to open this collection of 63 devices, and I noticed this particular query took about a minute to return results. It also seems obscenely long...

Ok I can't even put the query here b/c I exceed the 1000 character limit with JUST the query... I have attached it as a TXT file77334-angrysccm.txt

I dropped this query into SQL and it took 48 seconds to complete. For returning just 63 devices that is much longer than I am used to...

0 ·

angrysccm.txt (5.5 KiB)

MorrisseyEvan-2676 MorrisseyEvan-2676 · Mar 12 at 09:27 PM

Already hitting the brakes... sort of...

So the query itself isn't the issue. Run against the CAS DB it took 48 seconds, but running it against the Primary Site DB it took 0 seconds

Definitely appears this is a DB issue...

0 ·

Answer 1 · 2021-03-13T16:42:18.3Z

MorrisseyEvan-2676 answered · Mar 13 at 04:42 PM

Solved. I had the opposite of this issue. Had to set the regkey to 0

https://docs.microsoft.com/en-us/troubleshoot/mem/configmgr/sql-query-times-out-or-console-slow-performance

· 1 ·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

FionaYan-MSFT · Mar 16 at 01:43 AM

@MorrisseyEvan-2676

Thank you very much for the update and sharing the solution here. I believe this should be useful for someone who has similar issue in the future.

0 ·

Answer 2 · 2021-03-12T07:57:17.157Z

FionaYan-MSFT answered · Mar 12 at 07:57 AM

@MorrisseyEvan-2676

Thank you for posting in Microsoft Q&A forum.

What's odd is that queries run fine.

Could we know which query do we use? WMI, SQL or Powershell?

It can take a minute or two for a small (fewer than 10) collection, but takes absolutely forever (10+ minutes) for a larger (200+) collection.

Could we know how many collections and the mount of "Incremental Update" collection in our SCCM environment? Based on my understanding, It's best to limit the number of incrementally updated collections to 200. Maybe it will cause the slow connection if we use over 200.
For more details, please refer to this microsoft article:
https://docs.microsoft.com/en-us/mem/configmgr/core/clients/manage/collections/best-practices-for-collections#bkmk_incremental

Have a nice weekend!

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1 ·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MorrisseyEvan-2676 · Mar 12 at 02:24 PM

The queries I'm referring to are through the console, so via Monitoring > Queries, and Collection Membership Queries, and the new Query Preview. They all return results very quickly.

We have 82 collections using Incremental Eval, total runtime is 1:39

Again I want to emphasize that the ONLY issue is displaying the collection members. Evaluating the collection membership takes a normal amount of time. Honestly I'm pretty sure collections aren't even related to the issue, since if I go to Assets > Devices to display all devices it takes absolutely forever. Normally that screen does take a while to show everything but at least starts populating after only a few seconds.

Curiously if I go to Assets > Users, to display all users, it displays the 1000 users (as per the limit) instantly

So it is literally only displaying devices

0 ·

question