SCCM Console Slowness Issues How to Fix SQL Query Timeout Configuration Manager ConfigMgr? 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 a very common issue for SCCM/ConfigMgr Admins once SQL servers are migrated to 2014 or 2016 versions.
This is the first time I have a guest blogger Kannan C S sharing his experience. SCCM Console Slowness Issues How to Fix SQL Query Timeout Configuration Manager ConfigMgr?
He is an expert in SCCM/ConfigMgr with several years of SCCM and System Center experience. I will let Kannan C S explain his experience in detail.
Introduction
Hi All,
I’m Kannan C S and working as Sr. Infra Architect in a leading IT Company having 15 years of IT experience. I have been with Configuration Manager [Designing, Implementation, Migration, and Support], System Center Orchestrator [Designing and Implementation], and Windows Server support. You can refer to my blog here.
In this blog post, I will provide a quick overview of best practices we SCCM/ConfigMgr admins have to take care of before and after the SQL Server 2014 or 2016 migrations. This also covers guidelines on troubleshooting issues the issues as 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 is available in Microsoft SQL Server 2014 when SCCM CB is running with this instance of SQL:
| Trace Flag | Description |
| 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. |
Getting Started
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.
SCCM Console Slowness Issues How to Fix SQL Query Timeout Configuration Manager ConfigMgr? 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],
 m.[lastname],
 m.[firstname]
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
SCCM Console Slowness Issues How to Fix SQL Query Timeout Configuration Manager ConfigMgr? See the following example of a specific-query test run at the SQL 2012 CE level:
select all SMS_DeploymentSummary.ApplicationName,SMS_DeploymentSummary.AssignmentID,SMS_DeploymentSummary.CI_ID,SMS_DeploymentSummary.CollectionID,SMS_DeploymentSummary.CollectionName,SMS_DeploymentSummary.CreationTime,SMS_DeploymentSummary.DeploymentID,SMS_DeploymentSummary.DeploymentIntent,SMS_DeploymentSummary.DeploymentTime,SMS_DeploymentSummary.DesiredConfigType,SMS_DeploymentSummary.EnforcementDeadline,SMS_DeploymentSummary.FeatureType,SMS_DeploymentSummary.ModelName,SMS_DeploymentSummary.ModificationTime,SMS_DeploymentSummary.NumberErrors,SMS_DeploymentSummary.NumberInProgress,SMS_DeploymentSummary.NumberOther,SMS_DeploymentSummary.NumberSuccess,SMS_DeploymentSummary.NumberTargeted,SMS_DeploymentSummary.NumberUnknown,SMS_DeploymentSummary.ObjectTypeID,SMS_DeploymentSummary.PackageID,SMS_DeploymentSummary.PolicyModelID,SMS_DeploymentSummary.ProgramName,SMS_DeploymentSummary.SecuredObjectId,SMS_DeploymentSummary.SoftwareName,SMS_DeploymentSummary.SummarizationTime,SMS_DeploymentSummary.SummaryType from fn_DeploymentSummary(1033) AS SMS_DeploymentSummary where SMS_DeploymentSummary.DeploymentID = 'ANN00012' option (querytraceon 9481)SCCM Console Slowness Issues How to Fix SQL Query Timeout Configuration Manager ConfigMgr? Note: ANN00012 is an example for Deployment ID, it will vary based on your environment.
Solution:-
SCCM Console Slowness Issues How to Fix SQL Query Timeout Configuration Manager ConfigMgr? If the above query returns the result 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 an 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.
Important:
However, when a database is upgraded from an 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.
Conclusion:-
One of the performance improvements in SQL Server 2014 is the redesign of cardinality estimation. The component which does cardinality estimation (CE) is called the cardinality estimator.
It is the essential component of the SQL query processor for query plan generation. Cardinality estimates are predictions of the 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, the cardinality estimator was largely based on SQL Server 7.0 codebase.
SQL Server 2014 introduces a new design and the new cardinality estimator is based on research on modern workloads and learning from past experience. SCCM Console Slowness Issues How to Fix SQL Query Timeout Configuration Manager ConfigMgr?
Resources
Microsoft Reference: https://support.microsoft.com/en-nz/help/3196320/sql-query-times-out-or-console-slow-on-certain-configuration-manager-database-queries
Author
Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with more than 20 years of experience (calculation done in 2021) in IT. He is a blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. E writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc…
What Deployment ID should I use for running this query?