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.
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.
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 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:
- Best Practices for Page File and Minimum Drive Size for OS Partition on Windows Server
- What is the Page File for anyway
- How to Determine the Appropriate Page File Size for 64-bit Versions of Windows
- Page File – The Definitive Guide
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.
- Microsoft Windows Multi-Site Failover Cluster Best Practices (2012)
- Windows Server 2008 R2 Failover Clustering – Best Practices Guide (2008 R2)
This is a whole topic unto itself. Just know that introducing WSFC involves knowing something about the OS, AD, network, etc.
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:
- Configure the Windows Firewall to Allow SQL Server Access
- Configure a Windows Firewall for Database Engine Access
- How to open the firewall port for SQL Server on Windows Server
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.
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:
- Process(*)\%% Processor Time
- Process(*)\Private Bytes
- Process(*)\Virtual Bytes
- Processor(*)\%% Processor Time
- Processor(_Total)\%% Processor Time
- Memory\Available MBytes
- Memory\Committed Bytes
- Memory\Page Faults/sec
- Memory\Pool Nonpaged Bytes
- Memory\Pool Paged Bytes
- 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 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(_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
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!