How to Fix SQL Query Timeout or SCCM Console Slowness Issues

You experience slow SCCM/ConfigMgr console performance or unusual SQL query timeouts for certain Configuration Manager database queries or OSD Auto Apply driver’s execution will fail due to times out in environments running Microsoft SQL Server 2016 or 2014.

0

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.

Hi All,

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 VersionSupport compatibility level valuesRecommended compatibility level for ConfigMgr/SCCM
SQL Server 2016130,120,110,100130
SQL Server 2014120,110,100110

The following plan affecting trace flags are available in Microsoft SQL Server 2014 when SCCM CB is running with this instance of SQL:

Trace FlagDescription
9481Use 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.
2312Use 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.

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],&#xD;&#xA;        m.[lastname],&#xD;&#xA;        m.[firstname]&#xD;&#xA;FROM [dbo].[charge] AS c&#xD;&#xA;INNER JOIN [dbo].[member] AS m ON&#xD;&#xA;    c.[member_no] + ‘ ‘  =  m.[member_no] &#xD;&#xA;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:

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)

Note: ANN00012 is example for Deployment ID, it will vary based on your environment.

Solution:-

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.

Important:

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.

Conclusion:-

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.

Microsoft Reference: https://support.microsoft.com/en-nz/help/3196320/sql-query-times-out-or-console-slow-on-certain-configuration-manager-database-queries

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.