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: | ||
| Clustering | ||
| Is This Computer/Machine Part of a Windows Cluster: | ||
| Cluster Node Name: | ||
| Cluster Node IP Address: | ||
| Does the OS Have the Latest Hardware Drivers | ||
| System: | ||
| Display Adapter: | ||
| DVD/CD-ROM: | ||
| RAID Controller: | ||
| HBA: | ||
| Network Interface Adapter: | ||
| Other: | ||
| Partitions | ||
| 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 | ||
| Hyper-V | ||
| 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" | ||
| Other | ||
| 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!
Brad M McGehee 
Pingback: Tweets that mention SQL Server Operating System Checklist | SQL Aloha -- Topsy.com
Pingback: Something for the Weekend – SQL Server Links for the Week 25/06/10 | John Sansom - SQL Server DBA in the UK
Hi Brad,
Great series. How about coming up with the powershell scripts to return this data.
Cheers,
Bill
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?
Sven.
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.
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.
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.
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.
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?