Often combined with a general lack of familiarity with the settings configured, or teams having the ability to rely on other teams to configure or manage their database instance. Doing that can leave you in the unfortunate circumstances, such as; poor console performance, over specified hardware resources used to combat performance problems, unnecessary downtime, and uncomfortable discussions with other teams about why your environment appears to be demanding. You might spend a considerable amount of time planning your Configuration Manager hierarchy, but overlook some important SQL Server considerations. Below we dive into six topics worth considering in your Configuration Manager SQL Server database.
This value is the most common setting left at its default value. The default setting allows SQL Server to give the database as much of the memory as it can consume, up to 2147483647 megabytes. Leaving the setting at its default can, and likely will cause performance issues. Also, it could be expected to see extensive pagefile usage, as the OS is eventually starved of memory, and no longer able to operate efficiently. SQL Server 2016 Standard Edition can address or make use of up to 128GB of memory, with Enterprise Edition supporting the operating system maximum. If your server is equipped with 128GB of memory, and running SQL Server 2016 Standard; you want to plan on leaving the Operating System 4-6GB of memory. In this example, you would set the maximum memory value to 123000 (123GB). Source
The ability to configure "Boost SQL Server Priority" has existed for several versions of SQL Server, and for several versions, it has not been a recommended setting to enable. Microsoft is explicit in the documentation about the risk of enabling this setting, saying it can starve essential OS components. An example of the risk of enabling this could be you lose the ability to remote into the OS. Microsoft has recently stated that the option to configure this will even be removed from a future version of the product. Source
Your WSUS database cannot be discounted. It must be maintained. It is an inseparable part of your ConfigMgr environment. You have likely already considered enabling the WSUS Cleanup Wizard, declining superseded updates, and established a maintenance plan for your Configuration Manager database. However there also need to be jobs in place for your WSUS database; to perform a backup, index optimization.
Database recovery is a serious topic, but your Configuration Manager instance likely does not warrant the need for a full or bulk logged recovery model. These recovery models create exponentially more growth in transaction logs, and without a solid use case, there are few reasons to consider it. Source
Imagine you had a team of people to compete for a task, and you could distribute the job to a defined number of people, aka parallel plan execution. At some point, it becomes less productive for the team (less CPU efficient) to distribute a workload to more team members (processors cores to be used in parallel plan execution). Consideration for processor cores, temp dB configuration, and workload will allow you to ensure these values are not robbing you of console performance. With no easy rule of thumb to apply, consulting ITS Partners and reviewing your SQL Server hardware will allow ITS Partners Advisory Services to make the best recommendations relevant to your environment. Source
SQL Server is a constantly evolving product, with massive updates, and was even one of the first products to introduce Cumulative Updates. It should come as no surprise that Microsoft has fixed concerning bugs over time. For example, a memory leak issue that presented itself when customers enabled AUTO_UPDATE_STATISTICS_ASYNC existed from versions 2008-2012 of SQL Server. The solution was to install cumulative updates and begin keeping SQL Server patched. Source
Additional Source(s):
https://blog.itsdelivers.com/productive-it-insights/systems-management
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016
https://docs.microsoft.com/en-us/sccm/core/plan-design/configs/support-for-sql-server-versions
6026 Kalamazoo AVE SE, #272
Kentwood, MI 49508
Contact us at (616) 242-5300
