SQL query times out or console slow on certain Configuration Manager database queries

Symptoms
You experience slow Configuration Manager console performance or unusual SQL query timeouts for certain Configuration Manager database queries in environments running Microsoft SQL Server 2016 or 2014.
Cause
SQL Server Cardinality Estimation (CE) changes introduced with SQL Server 2014 and SQL Server 2016 may cause performance issues with certain Configuration Manager queries in some environments, depending on variables such as site configuration, environment complexity, and the general state of load and performance of the SQL server.
Resolution
In affected environments, Configuration Manager may run better when the site database is configured at a different SQL CE compatibility level. To identify the recommended CE level for your version of SQL Server, refer to the following chart:

SQL Server versionSupported compatibility level valuesRecommended compatibility level for ConfigMgr
SQL Server 2016130, 120, 110, 100130
SQL Server 2014120, 110, 100110
To identify what SQL CE compatibility level is in use for the Configuration Manager database, run the following query:

SELECT name, compatibility_level FROM sys.databases
On SQL Server 2014, to identify whether using SQL Server 2012 CE may improve Configuration Manager query performance, identify a query that is running slowly and manually test its performance at the SQL 2012 CE compatibility level. To do this, run the query in SQL Server Management Studio with <option (querytraceon 9481)> and compare the execution time to its performance without the flag. For more information on using querytraceon with trace flag 9481 at the specific-query level, see Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level. For information about using SQL Profiler to identify slow queries see, SQL Server Profiler.

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 = N'CS100012' option (querytraceon 9481)
Note The query above and deployment ID CS100012 are for demonstration purposes only and will vary by environment.

If the above test indicates that performance gains can be achieved, use the following command in SQL Server Management Studio to set the Configuration Manager 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 Configuration Manager 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 Configuration Manager site database name.
More information
New Configuration Manager sites deployed on SQL Server 2014 and SQL Server 2016 will automatically use the most-current SQL CE compatibility version supported by SQL Server. 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. For more information on SQL CE compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section and is investigating.
SCCM 1511 SCCM 1602 ConfigMgr 1511 ConfigMgr 1602
Özellikler

Makale No: 3196320 - Son İnceleme: 10/04/2016 18:22:00 - Düzeltme: 1.0

System Center Configuration Manager, v1606 released July 2016, System Center Configuration Manager, v1602 released March 2016, System Center Configuration Manager, v1511 released November 2015, System Center Configuration Manager (current branch), Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Standard

  • KB3196320
Geri bildirim