This post is to address the issues which we may face after migration from SCCM/ConfigMgr SQL 2012 to SQL 2014 or 2016. This could be very common issue for SCCM/ConfigMgr Admins once SQL servers are migrated to 2014 or 2016 versions. This is the first time I’ve a guest blogger Kannan C S sharing his experience. He is expert in SCCM/ConfigMgr with several years of SCCM and System Center experience. I will let Kannan C S to explain his experience in details.
I’m Kannan C S and working as Sr. Infra Architect in leading IT Company having 15 years IT experience. I have been with Configuration Manager [Designing, Implementation, Migration and Support], System Center Orchestrator [Designing and Implementation], Windows Server support. You can refer to my blog here.
In this blog post, I will provide a quick overview about best practices we SCCM/ConfigMgr admins have to take care before and after the SQL Server 2014 or 2016 migrations. This also covers guidelines on troubleshooting issues on the issues like SQL query timed out and SCCM/ConfigMgr console slowness.
Microsoft Recommends the below compatibility level for SQL Server when SCCM DB is running with this SQL instance
|SQL Server Version||Support compatibility level values||Recommended compatibility level for ConfigMgr/SCCM|
|SQL Server 2016||130,120,110,100||130|
|SQL Server 2014||120,110,100||110|
The following plan affecting trace flags are available in Microsoft SQL Server 2014 when SCCM CB is running with this instance of SQL:
|9481||Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan.|
|2312||Use when running SQL Server 2014 with database compatibility level 110, which is the compatibility level for SQL Server 2012. Trace flag 2312 forces the query optimizer to use version 120 (the SQL Server 2014 version) of the cardinality estimator when creating the query plan.|
In the SQL 2014 XML plan, there is a new attribute in StmtSimple called CardinalityEstimationModelVersion. When the value is 120, it means the new cardinality estimator is used. If the value is 70, it means the old cardinality estimator is used.
Note that if my database compatibility level was for an earlier level, I see the following:
<StmtSimple StatementCompId=”1″ StatementEstRows=”1597640″ StatementId=”1″ StatementOptmLevel=”FULL” CardinalityEstimationModelVersion=”70″ StatementSubTreeCost=”21.0895″ StatementText=”SELECT c.[charge_no],
FROM [dbo].[charge] AS c
INNER JOIN [dbo].[member] AS m ON
c.[member_no] + ‘ ‘ = m.[member_no]
OPTION (RECOMPILE)” StatementType=”SELECT” QueryHash=”0xF698FF11488A3A3D” QueryPlanHash=”0x41F421A8F51D61CE” RetrievedFromCache=”false”>
After migration of SQL 2012 to SQL 2014 or 2016, to identify the SQL CE compatibility level is in use for SCCM/ConfigMgr database, run the following query
SELECT name, compatibility_level FROM sys.databases
See the following example of a specific-query test run at the SQL 2012 CE level:
Note: ANN00012 is example for Deployment ID, it will vary based on your environment.
If the above query returns the result in faster, then there is no change required in your environment. Before making any changes on the DB take a backup !!
If the above test indicates that performance gains can be achieved, use the following command in SQL Server Management Studio to set the ConfigMgr/SCCM database running on SQL Server 2014 to the SQL Server 2012 CE compatibility level:
ALTER DATABASE <CM_DB> SET COMPATIBILITY_LEVEL = 110; GO
To set a SCCM/ConfigMgr database running on SQL Server 2016 to the SQL Server 2016 CE compatibility level, use the following:
ALTER DATABASE <CM_DB> SET COMPATIBILITY_LEVEL = 130; GO
Note In the two examples above, replace <CM_DB> with your ConfigMgr/SCCM site database name.
However, when a database is upgraded from any earlier version of SQL Server, the database retains its existing compatibility level if it is at the minimum allowed for that instance of SQL Server. Upgrading SQL Server with a database at a compatibility level lower than the allowed level automatically sets the database to the lowest compatibility level allowed by SQL.
One of the performance improvement in SQL Server 2014 is the redesign of cardinality estimation. The component which does cardinality estimation (CE) is called cardinality estimator. It is the essential component of SQL query processor for query plan generation. Cardinality estimates are predictions of final row count and row counts of intermediate results (such as joins, filtering, and aggregation). These estimates have a direct impact on plan choices such as join order, join type etc. Prior to SQL Server 2014, cardinality estimator was largely based on SQL Server 7.0 code base. SQL Server 2014 introduces new design and the new cardinality estimator is based on research on modern workloads and learning from past experience.