I founded SQL Solutions Group 10 years ago (October 2010). In that time, we’ve done more than 100 Health Checks on customer systems. Based on that experience, we’re starting a series of posts to help you avoid pitfalls we often encounter. With this post, we show you how to configure Windows for better SQL Server performance.
Not surprisingly, we see many of the same issues in almost every environment. This is an opportunity for us to share our knowledge in a series of blog posts. What we’ve learned from top offenders should be interesting and useful for our audience.
This post focuses on the Windows operating system and key configuration items that often get missed, which impairs performance.
Power Plan
The default Windows Power Plan in most environments is Balanced. which sounds good for power savings. But it does not work well for production SQL Server and can result in multiple types of CPU-related performance issues.
Set the Power Plan to High on all SQL Server environments. Don’t forget to include the BIOS/UEFI and virtualization layer host operating system if applicable.
Also note that many times this is a setting controlled by Active Directory Group Policy and you may need to override it for your SQL Servers. This is one of the many reasons we recommend you put your SQL Servers in their own organizational unit.
Disk Layout
Years ago, it was standard practice for the DBA to be involved in storage design and configuration. We gave a lot of thought into where to store the data files (in multiple filegroups), log files and backups. Splitting this across multiple volumes meant different physical disks/spindles. So we all did things like spreading data files across as many spindles as possible, separating indexes from data, and putting log files on storage optimized for sequential writes.
However, over the years storage area networks (SANs) have become more sophisticated, with volumes spread across disk pools containing hundreds of disks, tiering, virtualized storage, and so forth. Many in the industry think it no longer matters how we layout the data files. After all, the I/O is spread across all those disks anyway, right? Yes, that’s true at the physical storage level (the SAN). But, there is an entire storage path that the I/O travels to get there. Starting with the disk controller(s) at the OS level. Windows multi-threads and caches I/O at the controller level, so multiple drives on multiple controllers is still important for optimal performance.
NTFS Block Size
In addition to spreading the I/O across multiple disks/controllers, take care how you format the volumes. The default allocation unit size for Windows volumes is 4K. Because of the way SQL Server I/O works, a larger block size is best. We recommend 64K as a starting point best practice, but in some cases even larger is better.
There are many ways to check this, but the easiest is using the fsutil command from an administrative command prompt.
SQL Server Service Accounts
For a variety of reasons, such as providing least privileged access to domain resources as well as managing security on the local system, it is best to use a domain-based service account rather than the default built-in accounts for SQL Server. We recommend a different account for each type of service (SQL Server, SQL Agent, SSRS, etc.) as well as per server. Use a naming convention to make these easy to identify. You can manage security across all SQL Servers by adding all service accounts to a global group. This allows one-at-a-time password changes, easier identification in audit logs as to which server has done something, etc.
In recent years, Group Managed Service Accounts (gMSA) have become available and that is an even better solution. There are some caveats for using these with SQL Server, so be sure to review the details before using.
Windows User Rights
There are two Windows User Rights that you should assign to the SQL Server Service Account. You can manage this via Group Policy or using the Local Security Policy management console on the server.
- Lock Pages in Memory (LPIM) – This allows SQLOS to better manage memory and avoid paging to disk.
- Perform Volume Maintenance Tasks (PVMT) – This enables Instant File Initialization (IFI) so that you don’t block data file creation and growth operations while the new space is zeroed.
Summary
These tips should help you configure Windows for better SQL Server outcomes and performance. True, the Windows OS Configuration might not seem that important when looking at the overall health of a SQL Server. But it (along with physical hardware and virtualization, when applicable) forms the foundation for SQL Server to run on. It’s important!
Getting it right will ensure that SQL Server is stable, secure, and high performance.
Our next post will get into instance-level SQL Server configuration tips.
