Shulou(Shulou.com)06/01 Report--

For the configuration of the SQL Server database, follow the best practices and see if you can modify the instance configuration of the production environment.

The following is the adjustment method and basis:

Start account memory locking

Ensure that SQL Server commit memory will not be swapped or space will not be shrunk by the operating system.

The SQL Server 64-bit version uses "locked pages" to prevent the process working set (committed memory) from being paged out or trimmed by the operating system.

Reference:

Https://support.microsoft.com/en-us/kb/2659143

Limit the maximum memory size

Reserve a certain amount of memory for SQL Server connection memory, other applications, system memory, etc.

Reference:

Https://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/

Start the account and initialize the file immediately

Quickly initialize data file space allocation and reduce waiting.

This should be enabled in the vast majority of cases. SQL Server 2016 lets you enable this during the SQL server installation process.

Reference:

Database Instant File Initialization

Https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization

Misconceptions around instant file initialization

Https://www.sqlskills.com/blogs/paul/misconceptions-around-instant-file-initialization/

Tempdb configures multiple files based on CPU audit

Configuring multiple tempdb files with the same configuration according to the number of CPU cores to improve the access performance of tempdb has become the standard configuration of SQL Server 2016.

Reference:

Https://www.derekseaman.com/2014/09/sql-2014-always-ag-pt-7-tempdb.html

Https://blogs.msdn.microsoft.com/psssql/2016/03/17/sql-2016-it-just-runs-faster-automatic-tempdb-configuration/

Https://blogs.sentryone.com/aaronbertrand/sql-server-2016-tempdb-fixes/

Trace Flag is put into startup parameters

Trace flags are used to turn on specific functions to improve database performance.

Common trace flags that should be enabled in most cases

TF 1117-When growing a data file, grow all files at the same time so they remain the same size, reducing allocation contention points

Reference:

Http://support2.microsoft.com/kb/2154845

TF 1118-Helps alleviate allocation contention in tempdb, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages (more important with older versions of SQL Server). Recommendations to reduce allocation contention in SQL Server tempdb database

Reference:

Http://support2.microsoft.com/kb/2154845

TF 2371-Lowers auto update statistics threshold for large tables.

Reference:

Http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

TF 3226-Supresses logging of successful database backup messages to the SQL Server Error Log.

Reference:

Https://www.sqlskills.com/blogs/paul/fed-up-with-backup-success-messages-bloating-your-error-logs/

Optimize for ad hoc workloads

Optimization is the memory footprint of the query.

Reference:

Https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option

Https://www.red-gate.com/simple-talk/blogs/using-optimize-for-ad-hoc-workloads/

Remote admin connections

Provide remote management access connections when the database has performance bottlenecks.

Reference:

Https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/remote-admin-connections-server-configuration-option

Https://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.