Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

Guideline for SQL Server configuration, installation and database creation

Guideline for SQL Server configuration, installation and database creation

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

To change a power plan:
1. Click on Start and then Control Panel. 
2. From the list of displayed item under Control Panel click on Power Options, which takes you to Select a power plan page. If you do not see Power Options, type the word 'power' in the Search Control Panel box 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 unavailable link. 
4. Choose the High Performance option 
5. Close the Power Option window. 

2.Disk partitions for SQL Server

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

Recommendations and Guidelines on configuring disk partitions for SQL Server

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.


Microsoft DiskPart version 6.0.6001
Copyright (C) 1999-2007 Microsoft Corporation.
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


Disk Partition Alignment Best Practices for SQL Server

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

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留給其他服務使用。


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 1 * calculated max worker threads 2 + -g startup parameter 3 (or 256MB by default if -g is not set). What remains should be the max_server_memory setting for a single instance setup.

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

2 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.

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


Server Memory Server Configuration Options

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

設定SQL Server Startup Account,例如 CONTOSOsqlservice

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:

  1. On the Start menu, click Run. In the Open box, type gpedit.msc.The Group Policy dialog box opens.
  2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
  3. Expand Security Settings, and then expand Local Policies.
  4. Select the User Rights Assignment folder.The policies will be displayed in the details pane.
  5. In the pane, double-click Lock pages in memory.
  6. 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,例如 CONTOSOsqlservice


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.


Server Memory Server Configuration Options

6.Tempdb file

(1)  data file數量

SQL使用的Logical CPU數量 設定tempdb data files數量
若少於等於8 設定等於邏輯CPU數量
若大於8 設定8data file


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

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





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


設定適當的成長大小 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

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


Database Instant File Initialization

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

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.

8.User Database file (使用者資料庫檔案)

(1)   基本設定原則

  1.      Create DataFG1 for Data (set default),不要使用Primary File Group
  2.      Create IndexFG1 for Index,不要使用Primary File Group,建立Non-Clustered Index時需指定此File Group

(2)   如果可以,將交易紀錄檔設定到獨立磁碟機

(3)   如果可以,設定多個資料檔分散到多個獨立磁碟機

(4)   Pre-size (預先設定資料檔大小)

  1.      如果是小型資料庫,且只有一個檔案,預估成長大小並設定上去。例如: 20 GB
  2.      如果是大型資料庫,設定多個檔案,預估成長大小,然後除檔案數量,就是每個檔案的大小。例如: 4個50GB的資料檔。

(5)   Autogrow(自動成長設定),可先設定為每次成長200MB開始

Share the post

Guideline for SQL Server configuration, installation and database creation


Subscribe to Msdn Blogs | Get The Latest Information, Insights, Announcements, And News From Microsoft Experts And Developers In The Msdn Blogs.

Get updates delivered right to your inbox!

Thank you for your subscription