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 |
|
SQL Server 2016+ |
140 (SQL 2017), 130 (SQL 2016), 120 (SQL 2014), 110 (SQL 2012) |
|
SQL Server 2014 |
120, 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.