SQL Server數(shù)據(jù)庫(kù)配置改進(jìn)

針對(duì)SQL Server數(shù)據(jù)庫(kù)的配置，遵從最佳實(shí)踐，有如下幾項(xiàng)，看能否修改生產(chǎn)環(huán)境實(shí)例配置。

以下為調(diào)整方法和依據(jù)：

啟動(dòng)賬號(hào)內(nèi)存鎖定

保證SQL Server提交內(nèi)存不會(huì)被交換或空間被操作系統(tǒng)收縮。

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.

參考：

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

限制最大內(nèi)存大小

保留一定內(nèi)存供SQL Server連接內(nèi)存、其它應(yīng)用、系統(tǒng)內(nèi)存等使用。

參考：

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

啟動(dòng)帳號(hào)即時(shí)文件初始化

快速初始化數(shù)據(jù)文件空間分配，減少等待。

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

參考：

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根據(jù)CPU核數(shù)配置多個(gè)文件

根據(jù)CPU核數(shù)配置多個(gè)相同配置的tempdb文件，提高tempdb的訪問(wèn)性能，已成為SQL Server 2016的標(biāo)配。

參考：

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放到啟動(dòng)參數(shù)中

跟蹤標(biāo)志用于開(kāi)啟特定功能提高數(shù)據(jù)庫(kù)性能。

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

參考：

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

參考：

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

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

參考：

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.

參考：

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

optimize for ad hoc workloads

優(yōu)化即系查詢的內(nèi)存占用。

參考：

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

數(shù)據(jù)庫(kù)有性能瓶頸時(shí)提供遠(yuǎn)程管理訪問(wèn)連接。

參考：

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/