This is my third in a series of checklists that I am putting together for a new book designed for DBAs to help audit their SQL Server instances. Previously I blogged about my SQL Server Hardware Checklist and my SQL Server Operating System Checklist. The goal of this checklist is to help DBAs document how their SQL Server instances are configured, which in turn may reveal some potential changes that could be made in order to better optimize their performance and availability.
Below is my current version of the SQL Server Instance Checklist, which is in the form of a spreadsheet that can be used to collect and store the information. As you review this checklist, you may notice that I have excluded some instance-related information, such as that relating to databases, security, database maintenance, SQL Server agent jobs, and high availability. Because each of these topics are large, I will devote separate checklists for each of these topics. In essence, this checklist covers generic instance configuration settings.
| The Basics | ||||
| Real or Virtual Machine: | ||||
| Default or Named Instance: | ||||
| Single or Multiple Instances: | ||||
| Instance Name: | ||||
| Port Number: | ||||
| SQL Server Version: | ||||
| SQL Server Edition: | ||||
| 32-Bit or 64-Bit: | ||||
| Service Pack Level: | ||||
| Version (Build) Number: | ||||
| Server Language: | ||||
| Server Collation: | ||||
| Latest Updates Applied: | ||||
| List Hotfixes Added: | ||||
| License Type: | ||||
| Is Properly Licensed? | ||||
| Product Key: | ||||
| Aliases | ||||
| Server Aliases & Settings: | ||||
| Network Protocols | ||||
| Shared Memory: | ||||
| Named Pipes: | ||||
| TCP/IP: | ||||
| VIA: | ||||
| Protocol Encryption Enabled: | ||||
| Clustering | ||||
| Is This Instance Part of a Windows Cluster: | ||||
| Virtual Server Name: | ||||
| Virtual Server IP Address: | ||||
| Type of Workload | ||||
| OLTP: | ||||
| OLAP: | ||||
| Combination: | ||||
| File Locations | ||||
| SQL Server Executables & Related Files: | ||||
| Location of System Databases: | ||||
| Production MDF/NDF Files: | ||||
| Production LDF Files: | ||||
| Tempdb: | ||||
| BAK/TRN Files: | ||||
| Tempdb Configuration | ||||
| Is Tempdb Pre-Sized to Optimal Size: | ||||
| Current Size of Tempdb: | ||||
| Is Tempdb Located on Its Own Array: | ||||
| Is Tempdb Divided Into Multiple Files: | ||||
| If Multiple Files, How Many Physical Files: | ||||
| If Multiple Files, Does Each File Have Identical Size: | ||||
| Services Installed/Running On This Instance | ||||
| SQL Server Database Services (MSSQLSERVER): | ||||
| SQL Server Agent: | ||||
| Integration Services: | ||||
| Analysis Services: | ||||
| Reporting Services: | ||||
| Full-Text Search: | ||||
| SQL Server Activity Directory Helper: | ||||
| SQL Server Browser: | ||||
| SQL Server VSS Writer: | ||||
| OS-Related Settings | ||||
| Is Instant File Initialization On: | ||||
| Is the "Lock Pages in Memory" Setting On (64-bit only): | ||||
| 32-Bit Memory Configuration (If 32-Bit Memory) | ||||
| How Much 32-Bit Memory is Available to the Instance: | ||||
| Does Boot.ini File Have the /3GB Switch: | ||||
| Does Boot.ini File Have the /PAE Switch: | ||||
| Is the "awe enabled" Server Setting On: | ||||
| The "max server memory" Server Setting Is: | ||||
| SP_Configure Settings | Minimum Value | Maximum Value | Default | Current Setting |
| access check cache bucket count (2) (3) | 0 | 16384 | 0 | |
| access check cache quota (2) (3) | 0 | 2147483647 | 0 | |
| ad hoc distributed queries (1) (2) | 0 | 1 | 0 | |
| affinity I/O mask (1) (2) (3) | -2147483648 | 2147483647 | 0 | |
| affinity64 I/O mask (1) (2) (4) | -2147483648 | 2147483647 | 0 | |
| affinity mask (1) (2) (3) | -2147483648 | 2147483647 | 0 | |
| affinity64 mask (1) (2) (3) (4) | -2147483648 | 2147483647 | 0 | |
| Agent XPs (1) (2) (3) | 0 | 1 | 1 | |
| allow updates (5) | 0 | 1 | 0 | |
| awe enabled (1) (2) (3) | 0 | 1 | 0 | |
| backup compression default (2) (3) | 0 | 1 | 0 | |
| blocked process threshold (1) (2) (3) | 0 | 86400 | 0 | |
| c2 audit mode (1) (2) (3) | 0 | 1 | 0 | |
| clr enabled (1) (2) (3) | 0 | 1 | 0 | |
| common criteria compliance enabled (1) (2) (3) | 0 | 1 | 0 | |
| cost threshold for parallelism (1) (2) (3) | 0 | 32767 | 5 | |
| cross db ownership chaining (1) (2) (3) | 0 | 1 | 0 | |
| cursor threshold (1) (2) (3) | -1 | 2147483647 | -1 | |
| Database Mail XPs (1) (2) (3) | 0 | 1 | 0 | |
| default full-text language (1) (2) (3) | 0 | 2147483647 | 1033 | |
| default language (1) (2) (3) | 0 | 9999 | 0 | |
| default trace enabled (1) (2) (3) | 0 | 1 | 1 | |
| disallow results from triggers (1) (2) (3) | 0 | 1 | 0 | |
| EKM provider enabled (2) (3) | 0 | 1 | 0 | |
| filestream_access_level (2) (3) | 0 | 2 | 0 | |
| fill factor (1) (2) (3) | 0 | 100 | 0 | |
| ft crawl bandwidth (max): (1) (2) (3) | 0 | 32767 | 100 | |
| ft crawl bandwidth (min): (1) (2) (3) | 0 | 32767 | 0 | |
| ft notify bandwidth (max): (1) (2) (3) | 0 | 32767 | 100 | |
| ft notify bandwidth (min): (1) (2) (3) | 0 | 32767 | 0 | |
| index create memory (1) (2) (3) | 704 | 2147483647 | 0 | |
| in-doubt xact resolution (1) (2) (3) | 0 | 2 | 0 | |
| lightweight pooling (1) (2) (3) | 0 | 1 | 0 | |
| locks: (1) (2) (3) | 5000 | 2147483647 | 0 | |
| max degree of parallelism (1) (2) (3) | 0 | 64 | 0 | |
| max full-text crawl range (1) (2) (3) | 0 | 256 | 4 | |
| max server memory (1) (2) (3) | 16 | 2147483647 | 2147483647 | |
| max text repl size (1) (2) (3) | 0 | 2147483647 | 65536 | |
| max worker threads (1) (2) (3) | 128 | 32767 | 0 | |
| media retention (1) (2) (3) | 0 | 365 | 0 | |
| min memory per query (1) (2) (3) | 512 | 2147483647 | 1024 | |
| min server memory (1) (2) (3) | 0 | 2147483647 | 0 | |
| nested triggers (1) (2) (3) | 0 | 1 | 1 | |
| network packet size (1) (2) (3) | 512 | 32767 | 4096 | |
| Ole Automation Procedures (1) (2) (3) | 0 | 1 | 0 | |
| open objects (5) | 0 | 2147483647 | 0 | |
| optimize for ad hoc workloads (2) (3) | 0 | 1 | 0 | |
| PH_timeout (1) (2) (3) | 1 | 3600 | 60 | |
| precompute rank (1) (2) (3) | 0 | 1 | 0 | |
| priority boost (1) (2) (3) | 0 | 1 | 0 | |
| query governor cost limit (1) (2) (3) | 0 | 2147483647 | 0 | |
| query wait (1) (2) (3) | -1 | 2147483647 | -1 | |
| recovery interval (1) (2) (3) | 0 | 32767 | 0 | |
| remote access (1) (2) (3) | 0 | 1 | 1 | |
| remote admin connections (1) (2) (3) | 0 | 1 | 0 | |
| remote login timeout (1) (2) (3) | 0 | 2147483647 | 20 | |
| remote proc trans (1) (2) (3) | 0 | 1 | 0 | |
| remote query timeout (1) (2) (3) | 0 | 2147483647 | 600 | |
| Replication XPs Option (1) (2) (3) | 0 | 1 | 0 | |
| scan for startup procs (1) (2) (3) | 0 | 1 | 0 | |
| server trigger recursion (1) (2) (3) | 0 | 1 | 1 | |
| set working set size (5) | 0 | 1 | 0 | |
| show advanced options (1) (2) (3) | 0 | 1 | 0 | |
| SMO and DMO XPs (1) (2) (3) | 0 | 1 | 1 | |
| SQL Mail XPs (1) (2) (3) | 0 | 1 | 0 | |
| transform noise words (1) (2) (3) | 0 | 1 | 0 | |
| two digit year cutoff (1) (2) (3) | 1753 | 9999 | 2049 | |
| user connections: (1) (2) (3) | 0 | 32767 | 0 | |
| User Instance Timeout (6) | 5 | 65535 | 60 | |
| user instances enabled (6) | 0 | 1 | 0 | |
| user options (1) (2) (3) | 0 | 32767 | 0 | |
| Web Assistant Procedures (1) | 0 | 1 | 0 | |
| xp_cmdshell (1) (2) (3) | 0 | 1 | 0 | |
| Key for Above | ||||
| (1) 2005 | ||||
| (2) 2008 | ||||
| (3) 2008 R2 | ||||
| (4) 64-bit only | ||||
| (5) deprecated | ||||
| (6) SQL Server 2008 Express only | ||||
| Linked Servers | ||||
| Is This Instance Linked to Other Instances: | ||||
| Described How Instances are Linked: | ||||
| Instance Endpoints | ||||
| Database Mirroring: | ||||
| Service Broker: | ||||
| SOAP: | ||||
| TSQL: | ||||
| Replication | ||||
| Is Replication Used: | ||||
| Type of Replication Used: | ||||
| Replication Role(s): | ||||
| Names of Instances Involved in Replication: | ||||
| SQL Server 2008 Policy-Based Management | ||||
| Is Policy-Based Management Being Used: | ||||
| Is This Instance Used to Manage Policies: | ||||
| List All Instances Managed by This Instance: | ||||
| List Policies: | ||||
| SQL Server 2008 Resource Governor | ||||
| Is the Resource Governor Being Used: | ||||
| List the Workload Groups: | ||||
| List the Resource Pools & Their Settings: | ||||
| Has the Classification Function Been Fully Tested: | ||||
| Has the Resource Governor Been Evaluated for Effectiveness: | ||||
| SQL Server 2008 Data Collector | ||||
| Is the Data Collector Used: | ||||
| Does This Instance Have the MDW: | ||||
| Is the Instance With the MDW Dedicated: | ||||
| Location of the MDW: | ||||
| List Instances Monitored by the Data Collector: | ||||
| Has the Data Collector Overhead Been Reviewed: | ||||
| Has the Data Collector Retention Policy Been Reviewed: | ||||
| SQL Server 2008 R2 Features Used | ||||
| PowerPivot: | ||||
| Master Data Services: | ||||
| Multi-Server Administration & Data-Tier Application: |
I would like your feedback on my SQL Server Instance Checklist. For example, I would like your input on:
–What is missing from the list? Keep in mind that I will be creating additional lists to cover more specific topics, and that I can’t include every possible instance 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!
