Diagnostics and the optimization of configurations are important for any SQL Server instance. However, it is not only the database server which need attention. The operating system, Windows Server, needs consideration as well. There are various general configurations to consider optimizing on any Windows Server hosting a SQL Server instance.

NOTE: this deals only with Windows Server. I know that Linux is now recently an option but this article will deal only with Windows Server.

This is part 2 of a 3 part series about SQL Server Environmental Diagnostics. Part 1 can be read here.

General Configurations

First of all, the following settings are good to consider:

Windows Update – if this is set to perform automatic updates please turn this off. Otherwise there will be unmanaged downtime to the applications running on this machine
Remote Desktop – make sure this is enabled so that admins can RDP into the host
Windows OS Roles – only install and activate necessary roles
Windows OS Features – only install and activate necessary features
What non-default software is running on a machine running a SQL Instance?

The general principle is that we don’t want to install anything we do not need.

Power Settings

The default power setting in Windows Server is “Balanced”. This is not acceptable for a machine hosting SQL Server as this will throttle system resources globally to all applications and significantly hinder SQL Server performance.

Always set the power option to “High Performance”. This can be found via:

Control Panel –> Hardware –> Power Options

I cannot overstate this enough – balanced power settings will globally throttle performance! Always use the high performance power setting!

As a result of poor power plans, I’ve seen otherwise healthy systems cripple the application performance simply because of this one simple setting. I have no idea why server grade software (I’m looking at you Windows Server) has this as a default. CPU is the main throttle but also network cards and disks can be affected here. It is simply never a good idea for a production server to use a balanced power setting.

Some systems may require a change in the BIOS or via Group Policy.

Page File

Page files are a special kind of file that Windows uses as a temporary workspace for storing modified pages from disk that are still in use by a process. It is used to hold data which is in the process of being swapped in and out of physical memory so as to allow for a larger virtual memory set.

Large page files deserve their own disk just like the SQL data file, transaction log, and tempdb files. PerfMon can be used to track usage and determine problems.

High pagefile usage can indicate a memory stressed database server.

Use the following performance counters:

Memory: Committed Bytes – number of bytes of virtual memory that has been committed
Memory: Commit Limit – number of bytes of virtual memory which can be committed without having to extend the paging files
Paging File: % Usage – % of the paging file committed
Paging File: % Usage Peak – highest % of the paging file committed

Look at these resources:

AV Concerns

If Anti-Virus software is running on the SQL host machine there should be exclusions for the following types of files:

MDF – these file extensions are associated with SQL Server database files
LDF – these file extensions are associated with SQL Server transaction log files
BAK – these file extensions are associated with SQL Server backup files
TRN – these file extensions are associated with SQL Server trace files

All database related files should be white-listed by your anti-virus product!

If your company has some draconian policy that states every machines must have AV running on it then whitelist the database files. Most SQL machines are not outward facing and have no internet access. They cannot be reached directly from outside the network and there is little need to run scans on it.

Windows Server Failover Clustering

If the WSFC feature is installed and running then make sure the best practices are being employed here.

This is a whole topic unto itself. Just know that introducing WSFC involves knowing something about the OS, AD, network, etc.

Windows Firewall

Is the firewall running? If so then make sure there are port exclusions for the port necessary for both SQL Server and the application it hosts to communicate.

Too many times I’ve seen application connectivity issues that are simply the result of a firewall exception never submitted.

Open the following ports at a minimum:

1433 – this is the default port that SQL Server listens on. Unless you specify otherwise or use dynamic ports this port needs to be open
1434 – SQL Browser listens here so it can sort out requests to named instances
443 – necessary if running over an HTTPS endpoint (SSSL)
135 – needed for SSMS debugging, will also need to add ssms.exe too

Don’t forget to open any application specific ports!

More ports can be found here:

Scheduled Jobs

Are there jobs running for maintenance tasks like:

SQL Server backups
SQL Server index maintenance
SQL Server dbcc checkdb
Disk space checks

If so make sure these are setup right and are set to run during off-peak hours. Make sure to monitor the output and errors.

Here I’m not saying don’t do backup but rather plan your maintenance activities around the application. Be mindful of the applications running on SQL and their care.

I’ve seen many cases where a DBA gets overzealous about index maintenance and ends up reindexing the tables too much and causes a lot of blocking and deadlocks.

Disk Partition Alignment

In the pursuit of optimal disk configuration one consideration disk partition alignment. Windows default is 1,024 kb (Windows Server 2008) whereas a more common sizing would start at 64 kb. Basically it has a greater chance of collaborating more efficiently with various disks, controllers, and cache.

Formatting a disk to 64 kb cluster size can be an important factor when trying to remediate suboptimal disk I/O performance.

Run PerfMon before and after the change to analyze the effectiveness of the change.

Disk Partition Alignment Best Practices for SQL Server

PerfMon

There are tons of Performance Monitor counters to choose from. It can be difficult to determine what to include and what to dismiss. Other times it can be a case of TMI (too much information) and the results are hard to decipher. Because there are so many to choose from I provide a list of some key counters below.

Here is a sample list of counters to assess system performance:

CPU
- Process(*)\%% Processor Time
- Process(*)\Private Bytes
- Process(*)\Virtual Bytes
- Processor(*)\%% Processor Time
- Processor(_Total)\%% Processor Time
Memory
- Memory\Available MBytes
- Memory\Committed Bytes
- Memory\Page Faults/sec
- Memory\Pages/sec
- Memory\Pool Nonpaged Bytes
- Memory\Pool Paged Bytes
Disk
- LogicalDisk(*)\Disk Reads/sec
- LogicalDisk(*)\Disk Writes/sec
- LogicalDisk(_Total)\Disk Reads/sec
- LogicalDisk(_Total)\Disk Writes/sec
- PhysicalDisk(*)\Avg. Disk sec/Read
- PhysicalDisk(*)\Avg. Disk sec/Transfer
- PhysicalDisk(*)\Avg. Disk sec/Write
- PhysicalDisk(*)\Current Disk Queue Length
- PhysicalDisk(*)\Disk Bytes/sec
- PhysicalDisk(*)\Disk Read Bytes/sec
- PhysicalDisk(*)\Disk Reads/sec
- PhysicalDisk(*)\Disk Transfers/sec
- PhysicalDisk(*)\Disk Write Bytes/sec
- PhysicalDisk(*)\Disk Writes/sec
- PhysicalDisk(_Total)\Avg. Disk Queue Length
Network
- Network Adapter(*)\Bytes Received/sec
- Network Adapter(*)\Bytes Sent/sec
- Network Adapter(*)\Bytes Total/sec
- TCPv4\Connections Active
- TCPv4\Connections Established
- TCPv6\Connections Active
- TCPv6\Connections Established
SQL Server
- %sqlname%:Buffer Manager\Buffer cache hit ratio
- %sqlname%:Buffer Manager\Checkpoint pages/sec
- %sqlname%:Buffer Manager\Page life expectancy
- %sqlname%:Databases(*)\Active Transactions
- %sqlname%:Databases(*)\Log Bytes Flushed/sec
- %sqlname%:Databases(*)\Log Flushes/sec
- %sqlname%:Databases(*)\Transactions/sec
- %sqlname%:Databases(_Total)\Log Bytes Flushed/sec
- %sqlname%:General Statistics\User Connections
- %sqlname%:Memory Manager\Total Server Memory (KB)
- %sqlname%:SQL Statistics\Batch Requests/sec
- %sqlname%:SQL Statistics\SQL Compilations/sec
- %sqlname%:SQL Statistics\SQL Re-Compilations/sec

Conclusion

Similar to the conclusions reached in the previous article for my SQL Server Environmental Diagnostics Guide:

Defaults are often poor!
If you take anything away from this article please let it be changing power settings to high performance!
Know when to step outside the guidelines – they are not rules without exceptions – learn them!

Like what you are reading? Please subscribe!