Operating System Best Practice Configurations for SQL Server

  • Статья

Hi all,

In this article, we are covering few important Operating System (Windows) best practice configurations for SQL Server. SQL Server is an user mode application runs on Windows Operating system and hence these configuration settings are important for SQL Server performance.

 

1. Partition the Disk volumes hosting SQL databases (Data and log) with 64 KB allocation unit size:
A drive can be formatted with different sizes ranging from 512 bytes to 64K sizes with the default being 4KB (4096 bytes). This setting is also called as “Bytes Per Cluster”. The atomic unit of storage in SQL Server is a page which is 8KB in size. Extents are groups of eight 8 KB pages that are physically contiguous to each other for a total of 64 KB. SQL Server uses extents to store data. Hence, on a SQL Server machine the NTFS Allocation unit size hosting SQL database files (Including tempdb files) should be 64K.

 

 

2. Instant file initialization:

SQL database 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 zeroing the files (filling with zeros) when one of the following operations are performed:

    • Create a database.
    • Add data or log files, to an existing database.
    • Increase the size of an existing file (including auto grow operations).
    • Restore a database or file group.

File initialization causes these operations to take longer.

In SQL Server, data files can be initialized instantaneously to avoid zeroing operations. Instant file initialization allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.

Instant file initialization is only available if the SQL Server service startup account has been granted SE_MANAGE_VOLUME_NAME.  Add SQL service startup account/Account used for backup operation to the "Perform Volume Maintenance Tasks" windows security policy.

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.

Starting with SQL Server 2016, instant file Initialization permission can be granted to the SQL service account at install time, during SQL setup.

 

 

3. OS Power Saving setting to High Performance:

In Windows Server 2008 and later OS, the default power saving setting is set to Balanced, which means that components such as CPU and storage will be scaled back if the system is not busy. In some cases, this may result in performance degradation for SQL Server. If the instance of SQL Server is under heavy load and is hosted by Windows Server 2008 or later OS, set the operating system power saving plan to High Performance.

 

 

4. Antivirus exclusion on SQL files:

When you configure antivirus software settings, make sure that you exclude the following files or directories on SQL Server machine from virus scanning. Doing this improves the performance of the files and helps make sure that the files are not locked when the SQL Server service must use them. However, if these files become infected, your antivirus software cannot detect the infection. 

  • SQL Server data files (.mdf, .ndf, .ldf files)
  • SQL Server backup files (.bak, .trn files)
  • Full-Text catalog files
  • Trace files (.trc files )
  • SQL audit files for SQL Server 2008 or later versions (.sqlaudit files)
  • SQL query files (.sql files)
  • The directory that holds Analysis Services data
  • The directory that holds Analysis Services temporary files that are used during Analysis Services processing
  • Analysis Services backup files
  • The directory that holds Analysis Services log files
  • Directories for any Analysis Services 2005 and later-version partitions that are not stored in the default data directory
  • Filestream data files (SQL 2008 and later versions)
  • Remote Blob Storage files (SQL 2008 and later versions)
  • The directory that holds Reporting Services temporary files and Logs (RSTempFiles and LogFiles)

Processes to exclude from virus scanning

SQL Server 2016

  • %ProgramFiles%\Microsoft SQL Server\MSSQL13.<Instance Name>\MSSQL\Binn\SQLServr.exe
  • %ProgramFiles%\Microsoft SQL Server\MSRS13.<Instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
  • %ProgramFiles%\Microsoft SQL Server\MSAS13.<Instance Name>\OLAP\Bin\MSMDSrv.exe

SQL Server 2014

  • %ProgramFiles%\Microsoft SQL Server\MSSQL12.<Instance Name>\MSSQL\Binn\SQLServr.exe
  • %ProgramFiles%\Microsoft SQL Server\MSRS12.<Instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
  • %ProgramFiles%\Microsoft SQL Server\MSAS12.<Instance Name>\OLAP\Bin\MSMDSrv.exe

SQL Server 2012

  • %ProgramFiles%\Microsoft SQL Server\MSSQL11.<Instance Name>\MSSQL\Binn\SQLServr.exe
  • %ProgramFiles%\Microsoft SQL Server\MSRS11.<Instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
  • %ProgramFiles%\Microsoft SQL Server\MSAS11.<Instance Name>\OLAP\Bin\MSMDSrv.exe

SQL Server 2008 R2

  • %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.<Instance Name>\MSSQL\Binn\SQLServr.exe
  • %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.<Instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
  • %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.<Instance Name>\OLAP\Bin\MSMDSrv.exe

SQL Server 2008

  • %ProgramFiles%\Microsoft SQL Server\MSSQL10.<Instance Name>\MSSQL\Binn\SQLServr.exe
  • %ProgramFiles%\Microsoft SQL Server\MSSQL10.<Instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
  • %ProgramFiles%\Microsoft SQL Server\MSSQL10.<Instance Name>\OLAP\Bin\MSMDSrv.exe

If you back up the database to a disk or if you back up the transaction log to a disk, you can exclude the backup files from the virus scanning.

You can run antivirus software on a SQL Server cluster. However, you must make sure that the antivirus software is a cluster-aware version. If you are running antivirus software on a cluster, make sure that you also exclude these locations from virus scanning:

  • Q:\ (Quorum drive)
  • C:\Windows\Cluster

Please refer the article for more information: https://support.microsoft.com/en-us/help/309422/how-to-choose-antivirus-software-to-run-on-computers-that-are-running

 

 

5. Lock Pages In memory:
Lock pages in memory privilege to SQL is recommended in Windows Server 2008 or above only when there are signs of paging. Locking pages in memory may boost performance when paging memory to disk is expected.

To enable the lock pages in memory option, add the SQL Service account to "Lock pages in memory" security policy and restart the SQL database engine service.

Before enabling LPIM for SQL server, review the Performance monitor data for other applications running on the server and check if check working set memory of SQL is trimmed and accordingly setup LPIM.

 

 

6. Setting appropriate Page file size:  
Windows uses the paging file (pagefile.sys) as secondary random-access memory (RAM). The paging file and physical memory make up virtual memory. By default, Windows stores the paging file on the boot partition. When Windows 2008 and later page file is configured to "Automatically manage paging file for all drives", page file is managed by the system according to how much virtual memory and disk space you have.
By default, "Automatically manage paging file size for all drives" setting is selected so that Windows 2008 or later system can manage the paging file without users interruption and configures roughly to two times to the size of physical RAM. If you want to change the paging file size, move the pagefile.sys to another drive, or disable virtual memory paging, uncheck the check box of Automatically manage paging file size for all drives.
To set Paging file to Custom Size:
1. Click Start, right-click Computer and select Properties
2. Select the Advanced System Settings
3. Under Performance, Click Settings button.
4. Click the Advanced tab
5. Under Virtual Memory, click Change button
6. If "Automatically manage paging file size for all drives" is selected, uncheck the option
7. Select the appropriate drives
8. Choose Custom size
9. Set Initial Size (MB) and Maximum Size (MB) as appropriate.

Follow the guidelines as discussed in the KB below to determine the appropriate page file size for 64-bit versions of Windows 2008 and above:
https://support.microsoft.com/en-us/help/2860880/how-to-determine-the-appropriate-page-file-size-for-64-bit-versions-of

 

 

7. Windows security policy and permissions

The account assigned to start SQL service needs the Start, stop and pause permission for the service. The SQL Server Setup program automatically assigns this.

SQL Setup account adds the SQL service account as part of below security policies:

  • Log on as a service
  • Replace a process-level token
  • Bypass traverse checking
  • Adjust memory quotas for a process
  • Permission to start SQL Writer
  • Permission to read the Event Log service
  • Permission to read the Remote Procedure Call service

If SQL Service account need to be changed, always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings. To change Reporting Services options, use the Reporting Services Configuration Tool.

SQL Server Setup does not open ports in the Windows firewall. Connections from other computers may not be possible until the Database Engine is configured to listen on a TCP port, and the appropriate port is opened for connections in the Windows firewall. To access an instance of the SQL Server through a firewall, configure appropriate inbound and outbound firewall rules on the SQL Server machine.

File System Permissions Granted to SQL Server service SID's or Local Windows Groups and registry permissions are documented here: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions

 

 

8. Dynamic Quorum Configuration in Windows 2012 and 2012R2 cluster:  
In Windows Server 2012 clusters and later versions, the Dynamic Quorum gives the administrator the ability to automatically manage the quorum vote assignment for a node, based on the state of the node. When a node shuts down or crashes, the node loses its quorum vote. When a node successfully rejoins the cluster, it regains its quorum vote. By dynamically adjusting the assignment of quorum votes, the cluster can increase or decrease the number of quorum votes that are required to keep running. This enables the cluster to maintain availability during sequential node failures or shutdowns.

In Windows Server 2012 R2 clusters, if the cluster is configured to use dynamic quorum (the default), the witness vote is also dynamically adjusted based on the number of voting nodes in current cluster membership. If there are an odd number of votes, the quorum witness does not have a vote. If there is an even number of votes, the quorum witness has a vote. The quorum witness vote is also dynamically adjusted based on the state of the witness resource. If the witness resource is offline or failed, the cluster sets the witness vote to 0.

Ensure Dynamic Quorum is enabled.  In addition, on Windows Server 2012 R2 cluster, use Node and Disk Majority or Node and File Share Majority as the quorum model.

 

Please share your feedback, questions and/or suggestions.

Thanks,

Don Castelino | Premier Field Engineer | Microsoft

 

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.