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

This post has been republished via RSS; it originally appeared at: Configuration Manager Archive articles.

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

 

 

 

 

SQL Server version

Supported compatibility level values 

Current behavior with ConfigMgr 1810+

SQL Server 2016+

140 (SQL 2017), 130 (SQL 2016), 120 (SQL 2014), 110 (SQL 2012)

  • Local and Remote providers will use the OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
  • In 1810, the DB will be changed to 130 if we are running SQL 2016 at setup time. (We don’t change it if they are on SQL 2017.)
  • This means right after 1810 install the Admin UI/Providers will issue queries at 110, but the “ConfigMgr backend” will use 130 (or 140)
  • The main reason people change to 110 is for Admin UI performance, this should make it so people don’t have to do anything, yet the backend will be set to the “native” SQL level.
  • In 1902 we will stop changing SQL 2016 to 130 at setup time, just in case they changed it back to 110 on purpose to allow their backend queries to run at 110.

SQL Server 2014

120, 110

  • The DB will be set to 110 at setup time (since 120 is so bad all around).
  • So all queries Admin UI/Provider and “ConfigMgr backend” will run at 110.

 

 

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.

Leave a 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.