A more common problem has recently surfaced that causes certain ConfigMgr database queries to time out or slow the admin console to a crawl. The problem is due to changes in SQL Server Cardinality Estimation (CE) changes. Essentially, Microsoft has made updates to SQL Server 2014, SQL Server 2016 and SQL Server 2017 that require some additional configuration for ConfigMgr environments when the database software is upgraded instead of installed from scratch. Fresh installations will be automatically configured according to the new specs.
The article SQL query times out or console slow on certain Configuration Manager database queries details the issue and offers fixes.
The issue can affect System Center Configuration Manager (current branch), SQL Server 2017 on Windows (all editions), SQL Server 2016 Enterprise, SQL Server 2016 Standard, SQL Server 2014 Enterprise, and SQL Server 2014 Standard.
Looking for an awesome, no-nonsense technical conference for IT Pros, Developers, and DevOps? IT/Dev Connections kicks off in Dallas, Texas in 2018!