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:
- Shotgun method and
- 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 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.
- Validate BIOS is using the latest version.
- Validate BIOS power management is disabled or set to OS control.
- Install latest drivers.
- Start with a freshly installed OS.
- Set Window clock time zone.
- Set Windows Power Plan to “High Performance”.
- Change Windows Page file location and size.
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.
- Install all Windows Updates.
- Change how Windows Updates are delivered:
“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 Windows Features / Roles. Remove if any are not needed. (not needed in Amazon or Azure setup).
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!)
- Format all storage where SQL files will live to 64KB allocation unit size.<!!link to below item>
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.
- Set Optimized Visualization “Optimized for Performance”. <!!link to below item>
- Disable Services that are not needed.<!!link to below item>
- Configure Antivirus and exclusions.<!!link to below item>
- Activate Windows OS license.
- 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:
- Set Recycle Bin to confirm before delete (“Display delete confirmation dialog”).
- 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.
- Pin ‘cmd.exe’ to taskbar
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”
- Pin Windows PowerShell ISE to taskbar, set to run as “Run as administrator”.
SQL installation checklist
- Download correct SQL installation (Enterprise, Standard, Developer, Express, etc.).
- Install SQL.
- Configuration items while installing SQL
<!! Insert many steps here>
- Install latest SQL Service Pack.
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.
- Install latest SQL Cumulative Update. Just as above
- 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.
SQL Instance changes (Post-install checklist)
- msc – set “Lock Pages in Memory” and “Perform Volume Maintenance Tasks”
Add login that runs SQLEngine Service to have these two security rights.
<!! Here is how to do it>
- SQL Configuration Manager
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
- When on Amazon AWS fix hostname
<!! Here is how to do it>
- SQL Instance changes
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
- When SQL is below SQL2016, I add:
1118 – use full extents only
1117 – grow all files in a filegroup
2371 – contro when AUTO_UPDATE_STATISTICS kicks in for large tables
- Set max mem – <!!insert a link>)
- Sys db changes<!!insert a link>)
- SQL Agent changes
- ERRORLOG config
- Change default dir
- Tempdb setup
- DBAToolsdb creation
- Create maintenance jobs
- Add security
- Setup email
- Setup alerts
- Deploy open source SPs
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
- SQL Agent restart notification – when SQL Agent restarts, send an email out.
- SQL Engine restart notification – when SQL Engine service restarts, send an email out.
- HDD space monitoring – this is configurable HDD space monitoring, when threshold is hit, it emails Warnings and Critical alerts out.
- DDL events monitoring. If any tables, stored procedures, views, or objects change, I keep a log everything I can capture.
- 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.
- SQLAgent job change monitoring – capture and log all job and job step changes into a log table.
- 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.
- 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.
- 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? Anything you’d change? I’d like to know how you do things differently.