One of the most popular posts on this site of all time is a SQL Server Setup Checklist that Brent started way back in March 2008. Over the years, we’ve updated the post and kept it fresh for installing and configuring SQL Server 2005, 2008, 2008R2, 2012, and 2014.
Now we’ve taken it one step further: we’ve created a free PDF of the setup guide for you. Download it, print it, and check items off as you work your way through your next SQL Server installation. We’ll be your co-pilots making sure you don’t miss a critical step.
The guide helps you:
- Validate you’ve got the right basic redundancy in your hardware
- Configure and test HBAs and multi-pathing
- Configure your system drive, and page file for SQL Server
- Provision storage for backups
- Create Service Accounts and grant permissions for settings like Instant File Initialization
- Format drives for the best performance
- Set up Anti-Virus with Microsoft recommended best practices
- Install and configure SQL Server
- Set up maintenance to run backups, manage index fragmentation, and check for corruption
- Get basic monitoring and alerting from the SQL Server Agent
- Design the right number of files and layout for tempdb
- Copy SQL Server logins from other servers
- Install free tools and run a health check against SQL Server
16 Comments. Leave new
In the section on configuring tempdb, you state that rather than relying on auto-grow to manage the multiple data files, they are “pre-grown”. I am wondering how one would forecast the expected size of tempDB data files? Easy if you’re cloning an existing server and can see their current size, but if it’s a new installation, there’s nothing to light the way! Do you have some suggestions on how to forecast the size?
As well, on several of the lunch webinars (btw, AWESOME!), Brent has stated a “rule of thumb” is to start with 4 data files for tempDB, no matter the number of logical processors. This is not mentioned in the guide. I was wondering if that guideline had been disproved or if it was just not mentioned.
Thanks again for the great resources…
Hi CJ,
Glad that you like the resources!
Forecasting tempdb size on a new instance is tricky. If you have a development or non-production version of the application, sometimes you can get an idea of how much space is needed. Sometimes there are vendor or product guidelines that can give you an idea. The amount of space needed varies so widely by application pattern that the basic rule is to test. If you can’t test, then you may want to err on the side of over-allocating since things get bad very quickly if you run out of space.
For the exact number of files, there is no real “right” answer here. Brent says go with four, the Microsoft KB we link to says “One per processor up to 8 logical procs, then see if you need more and add in units of 4.”
Brent’s formula is certainly easier to remember. The Microsoft formula has an official KB published.
On most systems both of these formulas will work just fine. To tell if you need more files, you can check for waits on a production system, I have an example of what those look like here: https://www.brentozar.com/archive/2014/05/tell-need-tempdb-files/
My question is more geared towards drives, VMWare and SANs.
If my underlying SAN and/or VMWare drive are all on the same spindle or LUN then is it really beneficial to separate MDF and LDF files out?
From a performance perspective, no. For manageability reasons, it can be useful to separate them as their growth patterns tend to be different. Often people decide to use different RAID levels for data files vs log files, also, or want to have the option to storage vMotion the log files to a different class of storage down the road. (Memory can help compensate quite a bit for data file performance if it’s on slower drives.)
I have to say its a great document; easy to read and easy to follow the steps traced out. I went through and I am about 90% on par with what is suggested in the documentation.
One item I see that wasn’t really touched on was trace flags, are there no trace flags you use by default? I know that 2371 is pretty fashionable these days. Any advice on trace flags?
Thanks
Glad you like the setup guide!
Any trace flag you enable can complicate a support ticket. Trace Flag 2371 is a great example– it’s documented in KB 2754171, but that says specifically that they only want you to turn it on by default for SAP. For other cases, they want you to have observed a need for the flag before enabling it. And the trace flag is NOT documented in Books Online here: http://msdn.microsoft.com/en-us/library/ms188396.aspx
So we have mixed messages on the trace flag. Let’s say you run into a critical performance situation and need to open a ticket with Microsoft. They’re going to look at what trace flags you’re running and ask you about them.
Do you know if that trace flag is helping you, and what will happen if you turn it off? Does that make your support ticket take longer? Was it worth it to just turn the trace flag on in the hopes that it would help? Have you been monitoring how many time it causes statistics to update and … could it actually be part of your problem in some situations? (This isn’t pure paranoia. Previous trace flags introduced to alter stats update behavior have caused problems, see http://support.microsoft.com/kb/929278/en-us)
Whether or not you should turn it on depends on the answers to those questions– so I just wouldn’t do it by default.
Thanks for the great SQL setup document. I have a question regarding the guidance around page files. I see section 3.3 stating to set a 2 GB page file, but I don’t see any guidance on making sure SQL doesn’t try to use the page file. Do you advocate setting up the “lock pages in memory” right? Or another method?
Again, thank you very much!!
Craig
Brent wrote a great post on the LPIM topic answering your question – https://www.brentozar.com/archive/2011/12/consulting-lines-pilot-dog/
Hi
I was wondering for best performance SQL setup, what your recommendation would be. for PCIE NVME SSD
I have to install SQL 2014 on Dell PE, Processor is Xeon V4 generation, Mem 64GB that has 3 SSd drives, 2 10K SAS, and 1 PCIE NVME 1TB SSD., I was wondering how you would recommend splitting the TEmpDB, DB Logs, and Databases for best performance given the mixed drives I have. Also, this requirement is for NON Vitual SQL, I have been asked NOT to virtualize this SQL server., OS is Win2012R2. This server will be AON, so even if I have to put the database on PCIE NVME I am fine, as it will be replicating DBs on secondary.
For random questions, head on over to http://dba.stackexchange.com/. Thanks!
Hi folks, the link above “Download the free SQL Server Setup Checklist e-Book here.” that points to https://www.brentozar.com/first-aid/sql-server-downloads/ looks dead. Or am I doing something wrong?
Jeffrey – click on Tools at the top of the site. Enjoy!
Gosh, that means I already have it downloaded (insert embarrassed blushing emoticon here). Sorry Brent, and thanks!
I went to Tools at the top of the screen and only saw paste the plan and links for sp_blitz. Would love to get the document.
Click on the word Tools. Enjoy!
I am an idiot. thank you