SQL Server Install Guide Part 3
I’m in part 3 of a series on installing SQL Server. In the previous post I began discussing steps to take after the install is complete. Because there are quite a number of things to consider post-install, this part 3 will continue discussing the post-install configuration items.
Configure SQL Server Alerts
One of the things I always do after installing SQL Server is set up SQL Server Alerts. This is a free, easy way to find out about problems on the SQL instance, like read retry errors or other potential nasty issues. There are probably a number of places to find scripts useful for this task. The one I recommend is from Glenn Berry at SQLSkills.com. You can find his script at this link. One of my favorite things about Glenn’s script is that it has very well defined alert names that allow the DBA to easily know what server is involved when an alert is sent.
How Should MAXDOP be configured?
Next up is setting MAXDOP. The foundational advice from Microsoft concerning Max Degree of Parallelism is in KB2806535, found here. The summary of this article is that if you have a single NUMA node and less than 8 logical processors, then keep MAXDOP at or below the number of logical processors. If you have multiple NUMA nodes and less than 8 logical processors per NUMA node then keep MAXDOP at or below the number of logical processors per NUMA node. If there are more than 8 logical processors per NUMA node, keep MAXDOP at 8.
So, how do you tell how many NUMA nodes there are on the machine? I’m glad you asked! There are several ways to determine this information. I will refer the reader to a post by Denny Cherry on the various methods.
Setting Min/Max Server Memory
MIN/MAX server memory should be reviewed as part of your post install configuration items. The MIN Server Memory setting is the minimum memory your server will allocate to SQL Server. The trick to understanding this is that SQL Server does not automatically grab that minimum value of memory upon start up. The memory used by SQL Server increases gradually after start up, assuming a steady workload on the instance. Once it crosses the value set in this MIN Memory setting then it does not give that memory back to the OS.
Setting the MAX Server Memory setting will prevent SQL Server from taking so much memory that it starves the OS. I typically leave 4-6 GB of RAM free to the OS and have not had problems with memory pressure using that guideline.
I will say though, that the machines I work with are dedicated to SQL Server, and almost always have just the database engine installed on them. If you have other SQL Server components on this same box, or application components from one or more applications then 4-6 GB left to non database engine components probably is not enough. Definitely monitor the Available Megabytes Perfmon counter over time so you can see how much free memory the machine has and adjust the Max Server Memory setting for SQL Server as needed.
To keep this series to a reasonable length, I’m simply going to list the next few items I have on my own personal Install Guide that I use. The series wasn’t meant to be an exhaustive explanation of everything one would put on an install guide anyway.
The guide I use is in Excel format and I go through each item on it as I work through the process. I mark off what I’ve done and then I save the guide. This helps me to ensure that I’ve covered all the steps and it gives me and other people a record of what I did during the process. Some of these steps can be entire blog posts on their own, or even an entire series written on that one step, so summarizing them here and expanding on them in later posts will be my approach. Ok, so here’s the remaining list.
- Ensure Windows power plan is on high performance
- Configure tempdb appropriately. (Google this and do research. Here are a couple of starting points. See this and this.
- Set up a DBA management database. (This is a database on each instance that has key procedures and objects, like sp_WhoIsActive, sp_Blitz, and any other custom scripts you like to use for troubleshooting or diagnostics)
- Enable Query Store (SQL Server 2016 and higher)
- Update compatibility level of databases to newest level. (This assumes that the new cardinality estimator was tested in a Dev and test environment prior to this migration or new install)
- Make sure database backups are occurring as expected.
- Consider enabling automatic plan choice correction (SQL Server 2017)
So, what are some of your steps for installing and configuring SQL Server?