SQL Server Operating System Checklist

As a part of a new book project, I am putting together a series of checklists that DBAs can use to inventory and audit their servers. Previously, I blogged about my SQL Server Hardware Checklist, in this post, I am writing about my Operating System Checklist. The goal of the checklist is to help DBAs document how the operating system is configured, which in turn could reveal some potential changes that should be made to the operating system in order to better optimize it for SQL Server.

Unlike the Hardware Checklist, this list may seem somewhat unfamiliar to many DBAs. This is because many DBAs aren’t very familiar with the operating system that SQL Server is running on, and in many cases, the operating system was setup and configured by someone other than themselves.

Below is the current version of my SQL Server Operating System Checklist, which is in the form of a spreadsheet that can be used not only to collect and store the information, but it can also be customized as needed by DBAs to better meet their environment.

The Basics    
Real or Virtual Machine:    
Computer/Machine Name:    
OS Version:    
Windows Edition:    
OS Build Number:    
32-Bit or 64-Bit:    
Service Pack Level:    
Are OS Updates Current:    
IP Address:    
What Domain or Workgroup Does the Computer/Machine Belong To:    
Is OS Properly Licensed and Activated:    
Product Key:    
Location of OS Files:    
Is This Computer/Machine Part of a Windows Cluster:    
Cluster Node Name:    
Cluster Node IP Address:    
Does the OS Have the Latest Hardware Drivers    
Display Adapter:    
RAID Controller:    
Network Interface Adapter:    
Are Partitions Properly Aligned:    
Are Partitions Formatted Using NTFS:    
NTFS Cluster Size:    
Is NTFS Compression Being Used:    
Is NTFS File Encryption Being Used:    
How Many Logical Partitions are Available:    
Size of Each Logical Partition (by Drive Letter):    
Percent Free Space Per Logical Partition (by Drive Letter):    
Page File    
Location of Pagefile.sys:    
Size of Pagefile.sys:    
Percentage Free Space in Pagefile.sys (under typical load):    
OS Settings    
Is "Processor scheduling" Set to "Adjust for best performance of Background services":    
Is "Visual Effects" Set to "Adjust for best performance":    
Is "System failure" Set to "Automatically restart":    
Is Windows Update Set for "Automatic Updates":    
Is "Indexing" Running:    
Is the Existing Power Plan Set to "High Performance":    
Is "Remote Desktop" Enabled:    
Has "Audit Policy" Been Enabled:    
Have Only Necessary Network Protocols Been Installed:    
Which OS Roles are Installed (2008 or 2008 R2)    
Active Directory Certificate Services    
Active Directory Domain Services    
Active Directory Federation Services    
Active Directory Lightweight Directory Services    
Active Directory Rights Management Services (AD RMS)    
Application Server    
Dynamic Host Configuration Protocol Server    
DNS Server    
Fax Server    
File Services    
Network Policy and Access Services    
Print and Document Services    
Remote Desktop Services    
Web Server (IIS)    
Windows Deployment Services    
Windows Server Update Services    
Which OS Features are Installed (2008 or 2008 R2)    
.NET Framework 3.5.1 Features Overview    
Background Intelligent File Transfer Service (BITS) Overview    
Biometrics Overview    
BitLocker Active Directory Recovery Password Viewer Overview    
BitLocker Drive Encryption Overview    
BranchCache Overview    
Connection Manager Administration Kit Overview    
Desktop Experience Overview    
DirectAccess Overview    
Failover Cluster Overview    
Group Policy Management Overview    
Ink and Handwriting Services Overview    
Internet Printing Client Overview    
iSNS Server Overview    
Line Printer Remote Port Monitor Overview    
Message Queuing Overview    
Multipath I/O Overview    
Network Load Balancing Overview    
Peer Name Resolution Protocol Overview    
Quality Windows Audio Video Experience Overview    
Remote Assistance Overview    
Remote Differential Compression Overview    
Remote Server Administration Tools Overview    
RPC over HTTP Proxy Overview    
Services for Network File System Overview    
Simple Mail Transfer Protocol Overview    
Simple Network Management Protocol Overview    
Simple TCP/IP Services Overview    
SNMP WMI Provider Overview    
Storage Manager for SANs Overview    
Subsystem for UNIX-based Applications Overview    
Telnet Client Overview    
Telnet Server Overview    
Trivial File Transfer Protocol (TFTP) Overview    
Windows Internal Database Overview    
Windows Internet Name Service (WINS) Overview    
Windows PowerShell Integrated Scripting Environment Overview    
Windows Process Activation Service (WAS) Overview    
Windows Server Backup Overview    
Windows System Resource Manager Overview    
Windows TIFF IFilter Overview    
WinRM IIS Extension Overview    
Wireless LAN Service Overview    
XPS Viewer Overview    
User Accounts & Security    
List Account(s) Used for SQL Server Services:    
What Permissions Have Been Given to the SQL Server Service Accounts:    
List Account Used for Clustering Service:    
What Permissions Have Been Given to the Cluster Service Accounts:    
Does the DBA(s) Have Local Admin Rights:    
Is the Windows Firewall Running?    
Physical File Fragmentation    
What are the Fragmentation Levels of Existing Partitions (by Drive Letter):    
Is Any Active Physical File Defragmentation Being Performed Regularly:    
OS Task Scheduler    
Are There Any Custom OS Jobs Running in the Task Scheduler?    
Is "Customer Experience Improvement Program" Task Running:    
Is "Defrag" Task Running"    
Is Computer/Virtual Machine Dedicated to SQL Server:    
Is Windows Defender Running:    
Is Anti-Virus/Spyware Turned On:    
If Anti-Virus/Spyware is Active, Have mdf, ldf, bak, and trn files been excluded:    
What Non-Default Software is Installed Besides SQL Server:    
What version of PowerShell in installed:    

In the above list, there are three sections that many DBAs may not be familiar with. They include:

Which OS Roles are Installed: When the Windows Operating System is installed, the installer can choose if this particular server has one or more roles. In some cases, combining some of these roles with SQL Server can significantly hurt SQL Server’s performance. In other cases, one or more of these roles may be required. If you install a standard instance of SQL Server, none of these OS roles are required.

Which OS Features are Installed: Features are similar to roles, but they are smaller in scope. As with roles, some features can hurt SQL Server’s performance, while others might be needed, depending on how SQL Server is configured. If you install a standard instance of SQL Server, none of the OS features are required.

OS Task Scheduler: Lists the jobs that the OS might be running in the background. For example, by default, every Wednesday at 1:00 the Windows “defrag” program is executed, assuming that the server has been idle for at least 3 minutes. Did you know that?

Which of the above roles, features, and tasks should be on or off is beyond the scope of this blog post, but this topic will be covered in the new book I am working on.

As with the SQL Server Hardware Checklist, I would like your input on my SQL Server Operating System Checklist. For example, I would like your input on:

–What is missing from the list? What OS configuration information should I add? I am especially interested in tracking any OS configuration information that could affect SQL Serve
r’s performance and availability. Keep in mind that I can’t include every possible variation.

–What on the list could be removed because it is not very important? Keep in mind that the list is designed to be generic, so there will be items on the list that will not be applicable to all SQL Server environments.

–Does my wording make sense, or should I change any of the wording so that it is more understandable or more accurate?

Please add your comments below, and as I get feedback, I will update the checklist. Thanks!

9 thoughts on “SQL Server Operating System Checklist

  1. Pingback: Tweets that mention SQL Server Operating System Checklist | SQL Aloha -- Topsy.com

  2. Pingback: Something for the Weekend – SQL Server Links for the Week 25/06/10 | John Sansom - SQL Server DBA in the UK

  3. Brad,

    Great work. How about just clarifiying under “The Basics” that Product Key is the OS product key?

    In addition, under the hardware driver section, I am not sure if I would state “latest.” I would focus more on WHQL-listed or “stable” drivers. Frequently, driver updates are not performed on servers if there is no need, so for many servers, the answer would be “no” to the “latest” question — which may very well be OK.

    Other than that, the list looks very useful. I am not sure about the use of the word “checklist” though… it seems more like collecting and storing configuration data for later use, rather than actually a list of best practice settings.

    Which brings me to ask if you plan to include anything about running the SQL BPA and documenting exceptions to those best practices?


  4. Bill, a PowerShell script to collect all this data would be great. What are you doing in your spare time? I have a project for you. 😉

    Sven, thanks for you suggestions. I do plan to talk about the BPA tool in my book, but have yet to include it in any of my checklists. In addition, the book I am writing will focus on the best practices, while the spreadsheets focus more on collecting data. Hopefully it will all make sense when the project is done.

  5. Hi Brad,

    I would add System Performance options check. I inherited several servers where “Memory Usage” was set to “Programs” instead of “System cache”. The same is for the “Processor scheduling” – check that it is set to the “Background services” option.

  6. Svetlana, one of your suggestions is already on the list, it is called, “Is “Processor scheduling” Set to “Adjust for best performance of Background services”, which is the term used in Windows 2008.

    The other suggestion you have for “Memory Usage” does not apply to Windows 2008 (but is available in Windows 2003). You might also want to check out this article, http://technet.microsoft.com/en-us/magazine/ff723772.aspx, which describes how this setting should be set for Windows 2003 and SQL Server 2008. Because of the complications of keeping track of both Windows 2003 and Windows 2008, I have focused the list on Windows 2008 only.

  7. Brad,

    Checklist idea is a great one. More than once I have felt a little naked about my windows server OS knowledge. Can you suggest a publication or website that might have requisite windows OS info as it relates to SQL Server?

    Thank you.

  8. John–That’s a great question. I have written about this in the past on another website, but the information is out of date. When I write my new book, based on these checklists, I will be discussing a lot of best practices in regards to configuring the OS for optimum SQL Server performance.

    Until I get my book finished, does anyone else have any good suggestions for John’s question?

Comments are closed.