ConfigMgr Current branch (1810+) guidance for the SQL CE levels with various SQL versions

First published on TECHNET on Jan 28, 2019
https://blogs.technet.microsoft.com/umairkhan/2019/01/28/configmgr-current-branch-1810-guidance-for-the-sql-ce-levels-with-various-sql-versions/

Hi Folks,

So first things first before I bombard with Jargons on SQL to a non-SQL ConfigMgr admin.

What is CE or Cardinality Estimation?

The CE predicts how many rows your query will likely return. The cardinality prediction is used by the Query Optimizer to generate the optimal query plan. With more accurate estimations, the Query Optimizer can usually do a better job of producing a more optimal query plan.

If you are reading this, I hope you might have already come across scenarios in ConfigMgr where you have had to manually change the Cardinality Estimator in SQL to a lower level or Legacy version which makes the performance better at times.

Why does this happen?

The ConfigMgr Provider queries have been written and tested with older version of SQL. So it is possible that a provider query on a new SQL version chooses not so optimal execution plans.

Given the nature of the issue, It was technically not feasible to test innumerable SQL queries for each SQL version and correct the code for each of them.

So what did we do then?

ConfigMgr team decided to simply run the Provider queries on a lower CE level (110) on which they are supposed to be performing well.

How is it implemented?

We simply want ConfigMgr Admins not touching those CE levels and hence we let ConfigMgr code do the best for us.

On the remote providers you will now see a registry

UseLegacyCardinality -> Set to 1

We still support running the DB at 110 as per this KB: https://support.microsoft.com/en-us/help/3196320

We just hope most people won’t need to do this anymore after 1810.

Having said all the above, there are still some Provider queries that seems to be performing bad at 110 and better at the latest CE level.

OR

The “ConfigMgr backend” queries which run better at the legacy CE level than the latest CE level.

What options are there for folks who want to run Provider queries with latest CE level after 1810?

• Change the above UseLegacyCardinality reg key to 0 would make it to use the current CE level set.

What options for making the “ConfigMgr Backend” queries with legacy cardinality ?

ALTER DATABASE <CM_DB> 
SET COMPATIBILITY_LEVEL = 110; 
GO

NOTE

In the above example, replace < CM_DB > with your Configuration Manager site database name. To change the CE compatibility level to a different level, change the value in 

SET COMPATIBILITY_LEVEL

OR

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON

Hope it helps!

~UK

Support Escalation Engineer | Microsoft System Center Configuration Manager

Disclaimer: This posting is provided “AS IS” with no warranties and confers no rights.