Proven SQL Server Installation Checklist and Settings to Increase SQL Server Performance!

How’d you like to see my secret stash of untapped SQL Server tuning items I personally use when setting up new SQL Servers to make them go faster and be more reliable?

Well, consider yourself very lucky…

…because today I’m going to pull back the curtain and share some of my absolute BEST SQL setup tips with you.

By the way, this check is a part of our SQL Server Health Check.

Warning: you are NOT going to find anything about theoretical examples. This is strictly a Server Setup checklist. The checklist below works for 99% of cases. So unless you’ve got something super special about your SQL Server, you have to do these items to squeeze the most performance and reliability out of your SQL Server.

There are two methods of tuning SQL Server:

  1. Shotgun method and
  2. Sniper method

This is my Shotgun Tuning Method.

The Sniper Tuning Method – is a lot more expensive. Here I’d be tuning ONE query at a time. And those can easily take anywhere from a few minutes to hours or days.

Below is a complete checklist for MS SQL Server installation on a new server.

The goal? Tweak all the knobs possible to help the majority of the workload.

Even though cloud providers have a couple of extra items that need to happen, this checklist will work on Amazon AWS EC2 or Azure servers.

Enough said. Here is my step by step approach for sql server installation best practices.

Hardware Checklist

  1. Validate hardware compatibility with SQL Server prerequisites.
  2. Validate BIOS is using the latest version.
  3. Validate BIOS power management is disabled or set to OS control.
  4. Install the latest drivers: Networking, HBA, Virtualization, Storage

Operating System

  1. Start with a freshly installed OS.

Windows Checklist

  1. Set Window clock time zone.
  2. Set Windows Power Plan to “High Performance”.
  3. Change Windows Page file location and size.
    1. Set min & max to 8,192MB.
    2. Put file on fastest drive (in Amazon AWS case, place it on ephemeral drive if there is one).
    3. If page files are setup on other drivers, delete them.
  4. Install all Windows Updates.
  5. Change how Windows Updates are delivered:
    1. “Check for updates but let me choose whether to download and install them”. On the most important servers, set this to not to check anything.
    2. Check “Give me recommended updates the same way I receive important updates”.
    3. Check “Give me updates for the other Microsoft products when I update Windows”.
  6. Install Windows Features / Roles. Remove if any are not needed. (not needed in Amazon or Azure setup).
    1. Install Enhanced Storage (not needed in Amazon or Azure)
    2. Install Multipath I/O(not needed in Amazon or Azure
    3. Install .NET 3.5 (must have for database mail to work, even in SQL 2016!)
  7. Format all storage where SQL files will live to 64KB allocation unit size.
    1. When storage volume is on-premise SAN volume, do not use “Perform Fast Formatting”.
    2. When volume is larger than 2TB, format at GPT (not MBR). I just format them all as 2TB.
  8. Set Optimized Visualization “Optimized for Performance”. <!!link to below item>
  9. Disable Services that are not needed.
  10. Configure Antivirus and exclusions.
  11. Activate Windows OS license.
  12. Disable “Server Manager” from autostarting (ideally this should be done for all users, not just yours).

Optional steps – I do these to make server management easier later on:

  1. Set Recycle Bin to confirm before delete (“Display delete confirmation dialog”).
  2. Run CrystalDiskMark for each disk, save to a doc. Name doc <servername_yyyy_mm_dd>. Make sure performance is around what you should be getting.<!!link to below item here is how to run CrystalDiskMark correctly>. This is a sanity check. How fast is your disk is performing is a good thing to know.
  3. Pin ‘cmd.exe’ to taskbar
    1. set to run as “Run as administrator”
    2. open cmd window, right click on top of the window, ‘properties’ -> ‘layout’, in “Screen Buffer Size” change “Height” to “9999”
  4. Pin Windows PowerShell ISE to taskbar, set to run as “Run as administrator”.

SQL Server setup best practices checklist (SQL installation checklist) 

  1. Browse for SQL Server installation media (Enterprise, Standard, Developer, Express, etc.).
  2. Using SQL Server Server installation center, install SQL.
  3. Configuration items while installing SQL
    1. Insert your SQL Server installation step by step list
  4. Install latest SQL Service Pack.
    1. First find latest SQL Service Pack and Cumulative Updates. Do not install yet. Only install if update has been out for 30-60days. Then Google Service Pack name to see if there are any known problems. If no issues – install.
  5. Install latest SQL Cumulative Update. Just as above
  6. Download and install latest SSMS. I try to avoid installing SSMS on the server itself, but to this day I will install SSMS on local (when important server) and won’t – when its not. Why do this on important servers? Because when there is a problem, I may have to be local on the box to solve it (happens a lot in Amazon AWS) and networking or routing is having issues.

Instance changes – Post SQL Server Installation Checklist

  1. msc – set “Lock Pages in Memory” and “Perform Volume Maintenance Tasks
    1. Add login that runs SQLEngine Service to have these two security rights.
  2. SQL Configuration Manager
    1. Disable all services you won’t need. If you did SQL install right, you won’t need to do anything here. But if server was built with Amazon AWS AMI – then you will get ALL SQL Server features. That’s not good. But worse is to leave them all running. So disable
    2. Turn on TCP/IP protocol
  3. When on Amazon AWS fix hostname
  4. SQL Instance changes
    1. Add TraceFlags (here is a script setups up trace flags)
      1. 3226 – stop logging successful backup messages to SQLError log
      2. 1222 – Capture deadlock info to SQLError log
  5. When SQL is below SQL2016, I add:
    1. 1118 – use full extents only
    2. 1117 – grow all files in a filegroup
    3. 2371 – contro when AUTO_UPDATE_STATISTICS kicks in for large tables
  6. Set max mem
  7. Sys db changes
  8. SQL Agent changes
  9. ERRORLOG config
  10. Change default dir
  11. Tempdb setup
  12. DBAToolsdb creation
  13. Create maintenance jobs
  14. Add security
  15. Setup email
  16. Setup alerts
  17. Deploy open source SPs

Monitoring setup

Below are the scripts I deploy to all SQL Servers. This checklist is already too big, so create a new post with all of them

  1. SQL Agent restart notification – when SQL Agent restarts, send an email out.
  2. SQL Engine restart notification – when SQL Engine service restarts, send an email out.
  3. HDD space monitoring – this is configurable HDD space monitoring, when threshold is hit, it emails Warnings and Critical alerts out.
  4. DDL events monitoring. If any tables, stored procedures, views, or objects change, I keep a log everything I can capture.
  5. Sp_whoisactive monitoring – run the best stored procedure to figure out what is happening on server every few minutes, capture into a table, delete irrelevant and old data, so I can go back and answer a question, of why server was slow at 3PM on Tue last week.
  6. SQLAgent job change monitoring – capture and log all job and job step changes into a log table.
  7. SQL Config Change Track and Notify – capture everything I can like SQL instance configuration changes and send out an email once per day, so I know if someone is messing with server settings they forgot to tell me about.
  8. Cache single plan cleanup – if single use plans are a problem, this will run and get rid of useless plans from cache making more RAM available to the rest of the server. If problem doesn’t exist on your server, then this won’t do any harm.
  9. DB and table growth monitoring – periodically log database sizes, how big tables are, so after a while, I can easily estimate what growth patterns will look like.

That’s it. Your SQL Server is now in a better shape than 90% of SQL Servers out there. And it will now run by itself much longer.

What do you do different for your environment?

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *