MEMCM SQL Query Poor Performance

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