Menu
CCMEXEC.COM – Enterprise Mobility
  • Home
  • General
  • Configuration Manager
  • Windows 10
  • Windows 11
  • Intune
  • GitHub
  • About
CCMEXEC.COM – Enterprise Mobility

MEMCM SQL Query Poor Performance

Posted on March 18, 2022March 18, 2022 by Johan

A few days ago, we experienced performance issues when querying a ConfigMgr DB view. A very simple query: “SELECT Name, MachineID, IsActive, AADDeviceID FROM v_CombinedDeviceResources WHERE CoManaged = ‘1’” could take up to a minute to complete.

Since we already knew that “v_CombinedDeviceResources” is the source from where the Device view in the ConfigMgr console pulls its data we dug into the smsprov.log and looked up the query issued by the console when loading the view. We found that apart from the query being “long”, the console added an Option to force “Legacy Cardinality Estimation” to the end of the query. This would imply that the query above, if issued by the console, instead would have looked like: “SELECT Name, MachineID, IsActive, AADDeviceID FROM v_CombinedDeviceResources WHERE CoManaged = ‘1’ OPTION(USE HINT(‘FORCE_LEGACY_CARDINALITY_ESTIMATION’))”

A quick test confirmed that the long running query, with the addition of this Option, instead performed as expected, delivering the expected result in less than a second. Difference!

Obviously “v_CombinedDeviceResources” is built from several underlaying queries. Many of these have been around for a while and are not optimized to use the new Cardinality Estimator introduced in SQL Server 2016, thus the directive to instead use legacy estimation to achieve <<normal>> performance also on newer DB Engines. Even though it is possible to configure the CM database to only use legacy estimation, adding the option to queries suffering from performance issues is probably better, since it does not prevent other parts of the DB to use new and better estimation?

However, there seems to be a glitch; the new estimator was introduced in SQL Server 2016 whereas the possibility to use the OPTION directive was only added in SP1. We have not investigated this further but if you are running SQL 2016 without SP, you might not be able to use this possibility?

So, when can this be of help? This is our conclusion:

  1. You experience poor performance when querying CM_xxx views.
  2. You are running SQL Server 2016 SP1 or higher.
  3. Your CM_xxx Database is on compatibility level 130 or higher.

Hopefully this can be of help to some of you, who like me, didn’t know?

@josch62

1 thought on “MEMCM SQL Query Poor Performance”

  1. Dan Clark says:
    July 2, 2024 at 3:20 pm

    This post helped me resolve an issue with slow SSRS reports in ConfigMan after a migration to SQL Server 2019. Thanks for the information!

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

My name is Jörgen Nilsson and I work as a Senior Consultant at Onevinn in Malmö, Sweden. This is my blog where I will share tips and stuff for my own and everyone elses use on Enterprise Mobility and Windows related topics.
All code is provided "AS-IS" with no warranties.

Recent Posts

  • New settings in Intune Security Baseline Windows 11 24H2 -2504
  • Managing extensions in Visual Studio Code
  • Reinstall a required Win32app using remediation on demand
  • Administrator protection in Windows 11 – First look
  • Remediation on demand script – ResetWindowsUpdate
©2025 CCMEXEC.COM – Enterprise Mobility | WordPress Theme by Superb Themes
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.Accept Reject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT