The Guide: SQL Server Installation Checklist (settings that increase SQL Server Performance)
Last updated July 31, 2017
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.
Warning: you are NOT going to find anything about theoretical examples. This is strictly a Server Setup checklist. 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:
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 even days.
Below is a complete checklist of SQL Server settings for 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.
Set min & max to 8192MB.
Put file on fastest drive (in Amazon AWS case, place it on ephemeral drive if there is one).
If page files are setup on other drivers, delete them.
“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.
Check “Give me recommended updates the same way I receive important updates”.
Check “Give me updates for the other Microsoft products when I update Windows”.
Install Enhanced Storage (not needed in Amazon or Azure)
Install Multipath I/O(not needed in Amazon or Azure)
Install .NET 3.5 (must have for database mail to work, even in SQL 2016!)
When storage volume is on-premise SAN volume, do not use “Perform Fast Formatting”.
When volume is larger than 2TB, format at GPT (not MBR). I just format them all as 2TB.
Optional steps – I do these to make server management easier later on:
set to run as “Run as administrator”
open cmd window, right click on top of the window, ‘properties’ -> ‘layout’, in “Screen Buffer Size” change “Height” to “9999”
<!! Insert many steps here>
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.
Add login that runs SQLEngine Service to have these two security rights.
<!! Here is how to do it>
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.
Turn on TCP/IP protocol
<!! Here is how to do it>
Add TraceFlags (here is a script setups up trace flags <!!insert a link>)
3226 – stop logging successful backup messages toSQLError log
1222 – Capture deadlock info to SQLError log
1118 – use full extents only
1117 – grow all files in a filegroup
2371 – contro when AUTO_UPDATE_STATISTICS kicks in for large tables
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
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? Anything you’d change? I’d like to know how you do things differently.
Get Exclusive SQL Performance Tips
Learn how to get more speed from your SQL Server with exclusive tips and insights that we only share with our best clients.
Please enter your e-mail address to get the best tips