Guideline for SQL Server configuration, installation and database creation

Article
04/25/2018

1.考慮將Windows電源選項(Power Options)調整為高性能(High Performance)

電源選項預設為平衡，系統會動態調整CPU性能，負載大的時候也會自動提高CPU性能，依照KB的描述若有遇到性能問題，首先考慮更新BIOS與Windows hotfix，另一個選擇，則是直接將電源選項改為高性能(High Performance)，但此時當系統平常使用時，會增加不必要耗電量。

Slow Performance on Windows Server when using the “Balanced” Power Plan

https://support.microsoft.com/en-us/help/2207548/slow-performance-on-windows-server-when-using-the-balanced-power-plan

To change a power plan:
1. Click on Start and then Control Panel.
2. From the list of displayed item under Control Panelclick on Power Options, which takes you to Select a power planpage. If you do not see Power Options, type the word 'power' in the Search Control Panelbox and then select Choose a power plan.
3. By default, the option to change power plans is disabled. To enable this, click the Change settings that are currently unavailablelink.
4. Choose theHigh Performanceoption
5. Close the Power Optionwindow.

2.Disk partitions for SQL Server (SSD也一樣建議設定為64KB)

格式化磁碟機時Allocation Unit Size選擇64KB

檢查方法

D:\>fsutil fsinfo ntfsinfo d:

有3選項需要注意

1.Bytes Per Cluster : 65536

Bytes Per Cluster就是Allocation Unit Size，建議format時就指定64KB

2.Bytes Per Sector : 512 (邏輯大小)
3.Bytes Per Physical Sector : 512 (實際大小)

因為磁碟的容量成長，所以有3種磁碟機

1.最原始的512-byte native

2.下項相容的Advanced Format (also known as 512E) 或稱(4K Sector Disk with 512-byte Emulation)

3.最新的4K native (Windows 2012才支援)

Only Windows Server 2012 or higher supports Native 4K format. Previous versions of Windows do not support Native 4K

Use the values for "Bytes Per Sector" and "Bytes per Physical Sector" to determine the kind of drive that you have. To do this, use the following table: (用以下表格來確認你使用的是哪一種磁碟)

“Bytes Per Sector" value	"Bytes per Physical Sector" value	Drive type
4096	4096	4K native (Windows 2012 or higher)
512	4096	Advanced Format (also known as 512E)
512	512	512-byte native

Bytes Per Physical Sector透過Storage管理工具可以修改

Recommendations and Guidelines on configuring disk partitions for SQL Server https://support.microsoft.com/zh-tw/kb/2023571

OS	Partition alignment defaults
Windows Server 2003 and Earlier	by default are not aligned. Partition alignment must be explicitly performed.default alignment is 32,256 bytes
Windows 2008	New partitions on Windows Server 2008 are likely to be aligned.Default alignment is 1024 KB (1,048,576 bytes)This value works well with commonly used stripe unit sizes of 64 KB, 128 KB and 256 KB as well as the less frequently used values of 512 KB and 1024 KB.

 檢查D磁碟目前Allocation Unit Size設定，Bytes Per Cluster就是Allocation Unit Size
D:\>fsutil fsinfo ntfsinfo d:

NTFS Volume Serial Number :       0xa2060a7f060a54a
Version : 3.1
Number Sectors : 0x00000000043c3f5f
Total Clusters :                  0x000000000008787e
Free Clusters : 0x000000000008746e
Total Reserved : 0x0000000000000000
Bytes Per Sector : 512
Bytes Per Cluster :            65536
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :       0x0000000000010000
Mft Start Lcn : 0x000000000000c000
Mft2 Start Lcn :                  0x0000000000043c3f
Mft Zone Start :                  0x000000000000c000
Mft Zone End   : 0x000000000001cf20

An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside. In many cases, this is the same size for Analysis Services data or log files, but there are times where 32 KB provides better performance. To determine the right size, you will need to do performance testing with your workload comparing the two different block sizes.

以下是範例用命令列來format並指定allocation unit(cluster size)

Here is an example in which the F: drive is created on disk 3, aligned with an offset of 1,024 KB, and formatted with a file allocation unit (cluster) size of 64 KB.

 C:\>diskpart

Microsoft DiskPart version 6.0.6001
Copyright (C) 1999-2007 Microsoft Corporation.
On computer: ASPIRINGGEEK
DISKPART> list disk
Disk ### Status Size Free Dyn GPT
-------- ---------- ------- ------- --- ---
Disk 0 Online       186 GB 0 B
Disk 1 Online       100 GB 0 B
Disk 2 Online       120 GB 0 B
Disk 3 Online       150 GB 150 GB
DISKPART> select disk 3
Disk 3 is now the selected disk.
DISKPART> create partition primary align=1024
DiskPart succeeded in creating the specified partition.
DISKPART> assign letter=F
DiskPart successfully assigned the drive letter or mount point.
DISKPART> format fs=ntfs unit=64K label="MyFastDisk" nowait

Reference:

Disk Partition Alignment Best Practices for SQL Server https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

Disk Partition Alignment: It Still Matters–DPA for Windows Server 2012, SQL Server 2012, and SQL Server 2014

https://blogs.msdn.microsoft.com/jimmymay/2014/03/14/disk-partition-alignment-it-still-matters-dpa-for-windows-server-2012-sql-server-2012-and-sql-server-2014/

Message misaligned log IOs which required falling back to synchronous IO in SQL Server Error Log (跟SSD有關的文件)
October 1, 2014 by Cameron - MSFT SAP Program Manager

https://blogs.msdn.microsoft.com/saponsqlserver/2014/10/01/message-misaligned-log-ios-which-required-falling-back-to-synchronous-io-in-sql-server-error-log/

What About Windows NTFS Format Size?

Any disk containing SQL Server datafiles, log files or tempdb files should always be formatted 64K at the Windows NTFS layer regardless of the underlying sector size.

To check this run command:

fsutil fsinfo ntfsinfo <drive letter>

The “Bytes per cluster” should be 65536. If this is not the case it is recommended to reformat the disk in Disk Management as per the screenshot below.

3.Max Server Memory(設定SQL Server Max Server Memory)

SQL Server Database Engine專用的主機基本原則

Windows 2008以上，最少保留2GB，其他設定為max server memory

Windows 2003以上，最少保留1GB，其他設定為max server memory

PS.若還有其他SQL Server元件(SSAS, SSRS...等)或其他服務或程式(防毒,備份...等)，則視狀況減少max server memory留給其他服務使用。

Performance Tuning Guidelines for Windows Server 2012 R2

https://www.microsoft.com/en-us/download/details.aspx?id=51960

Memory

The amount of memory required by the deduplication optimization job is directly related to the number of optimization jobs that are running. During the optimization process, approximately 1 to 2 GB of RAM is necessary to process 1 TB of data per volume at maximum speed.

For example, a file server running concurrent optimization jobs on 3 volumes of 1 TB, 1 TB, and 2 TB of data respectively would need the following amount of memory, assuming a normal amount of file data changes:

Volume	Volume size	Memory used
Volume 1	1 TB	1-2 GB
Volume 2	1 TB	1-2 GB
Volume 3	2 TB	2-4 GB
Total for all volumes	1+1+2 * 1GB up to 2GB	4 – 8 GB RAM

精確的計算方式:

Use max_server_memory to guarantee the OS does not experience detrimental memory pressure. To set max server memory configuration, monitor overall consumption of the SQL Server process in order to determine memory requirements. To be more accurate with these calculations for a single instance:

From the total OS memory, reserve 1GB-4GB to the OS itself.
Then subtract the equivalent of potential SQL Server memory allocations outside the max server memory control, which is comprised of stack size¹* calculated max worker threads²+ -g startup parameter³ (or 256MB by default if -g is not set). What remains should be the max_server_memory setting for a single instance setup.

¹ Refer to the Memory Management Architecture guide for information on thread stack sizes per architecture.

² Refer to the documentation page on how to Configure the max worker threads Server Configuration Option, for information on the calculated default worker threads for a given number of affinitized CPUs in the current host.

³ Refer to the documentation page on Database Engine Service Startup Options for information on the -g startup parameter.

Reference:

Server Memory Server Configuration Options
/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-2017

4.SQL Server startup account (SQL Server啟動帳戶)

設定SQL Server Startup Account，例如 CONTOSO\sqlservice

5.Lock Page in Memory (鎖定記憶體分頁)

Local Security Policy>Local Policy>User Rights Assignment> Lock pages in memory

To enable Lock Pages in Memory

To enable the lock pages in memory option:

On the Start menu, click Run. In the Open box, type gpedit.msc.The Group Policy dialog box opens.
On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, add the account with privileges to run sqlservr.exe (the SQL Server startup account).加入SQL Server Startup account，例如 CONTOSO\sqlservice

如果從本機安全性原則，此選項是灰階，表示此設定由AD上面的GPO控制，請從GPO設定

Lock Pages in Memory (LPIM)

This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. Locking pages in memory may keep the server responsive when paging memory to disk occurs. The Lock Pages in Memory option is set to ON in instances of SQL Server Standard edition and higher when the account with privileges to run sqlservr.exe has been granted the Windows Lock Pages in Memory (LPIM) user right.

To disable the Lock Pages In Memory option for SQL Server, remove the Lock Pages in Memory user right for the account with privileges to run sqlservr.exe (the SQL Server startup account) startup account.

Setting this option does not affect SQL Server dynamic memory management, allowing it to expand or shrink at the request of other memory clerks. When using the Lock Pages in Memory user right it is recommended to set an upper limit for max server memory as detailed above.

Reference:

Server Memory Server Configuration Options
/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-2017

6.Tempdb file

(1) data file數量

SQL使用的Logical CPU數量	設定tempdb data files數量
若少於等於8	設定等於邏輯CPU數量
若大於8	設定8個data file然後觀察如果持續有發現contention的話，一次加4個，來增加數量，最大等於邏輯CPU數。

觀察contention(例如 metadata contention (waitresource = 2:1:1 or 2:1:3))，再來決定是否還要增加數量

(2) Pre-size (資料檔大小)

如果8個，先每一個大小先設定為1024MB，作為初始值。

如果4個，先每一個大小先設定為1024MB，作為初始值。

如果2個，先每一個大小先設定為2048MB，作為初始值。

PS.監控觀察之後再來調整。

(3) Autogrow(自動成長設定)

先設定為每次成長200MB開始

設定適當的成長大小 general guidelines

tempdb file size	FILEGROWTH increment
0 to 100 MB	10 MB
100 to 200 MB	20 MB
200 MB or more	10%* (這個值，請參考下面的說明)

* You may have to adjust this percentage based on the speed of the I/O subsystem on which the tempdb files are located. To avoid potential latch time-outs, we recommend limiting the autogrow operation to approximately two minutes. For example, if the I/O subsystem can initialize a file at 50 MB per second, the FILEGROWTH increment should be set to a maximum of 6 GB, regardless of the tempdb file size. If possible, use instant database file initialization to improve the performance of autogrow operations

7.設定Instant data file initialization可改善自動成長時的效率

Database File Initialization https://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

設定Instant data file initialization，改善自動成長時的效率

(初始化檔案或成長檔案時不填入0)

Database Instant File Initialization https://msdn.microsoft.com/en-us/library/ms175935.aspx

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:

Create a database.
Add files, log or data, to an existing database.
Increase the size of an existing file (including autogrow operations).
Restore a database or filegroup.

File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros.

Database File Initialization https://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

To grant an account the Perform volume maintenance tasks permission:

(1) On the computer where the backup file will be created, open the Local Security Policy application (secpol.msc).

(2) In the left pane, expand Local Policies, and then click User Rights Assignment.

(3) In the right pane, double-click Perform volume maintenance tasks.

(4) Click Add User or Group and add any user accounts that are used for backups.

(5) Click Apply, and then close all Local Security Policy dialog boxes.