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.

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 Barry 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.

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.

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.

  1. Ensure Windows power plan is on high performance
  2. Configure tempdb appropriately. (Google this and do research.  Here are a couple of starting points.  See this and this.
  3. 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)
  4. Enable Query Store (SQL Server 2016 and higher)
  5. 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)
  6. Make sure database backups are occurring as expected.
  7. Consider enabling automatic plan choice correction (SQL Server 2017)

 

 

Setting Min and Max Server Memory Setting

Configuring the min and max server memory settings for SQL Server has a number of things to consider prior to making changes. Before we jump into those considerations, let’s learn what the min and max server memory options do.

 

MIN SERVER MEMORY

The min server memory option specifies the minimum amount of memory utilized by the SQL Server buffer pool. It may seem that this should mean that when SQL Server starts it will automatically grab the memory equal to the min server memory value. However, this is not what happens. Instead, SQL Server acquires system memory as needed to process a workload. Consequently, if the SQL Server workload remains low, it is possible that the min server memory value is never reached. However, once it is reached SQL Server will not release an amount of memory that would take it below that minimum setting.

For example, let’s suppose your server has 128 GB of RAM and the min server memory option were set to 32 GB for SQL Server. SQL Server does not immediately allocate 32 GB of RAM upon serve startup. It will acquire memory as needed for the workload. This means that if there is little activity in the database it may take some time before SQL Server allocates 32 GB of RAM. Once this happens, SQL Server will not release memory that would reduce the allocation below the min serve memory setting.

 

MAX SERVER MEMORY

To a certain point the max server memory setting does do what it sounds like. Keep in mind this setting is applicable only to the buffer pool. SQL Server can allocate some memory beyond this max number as it consumes memory for things outside of the buffer pool. Also, realize that this setting does not account for memory allocated to other components of SQL Server that may be installed and running, such as Reporting Services or Analysis Services.

 

The default value for Max Server Memory is 2,147,483,647 in MB. That number is rather large, 2,047.99 GB to be exact. So what happens if the machine SQL Server is installed on does not have that much memory and the default was not changed? As previously mentioned, SQL Server will continue to acquire additional memory based on workload. As a result, SQL Server could make requests of Windows that puts the box under memory pressure as it essentially attempts to choke the OS by leaving less and less memory for the OS. There are mechanisms in the OS that allow it to page out to disk some information in the SQL Server buffer pool so Windows can recover that memory for itself. This hurts SQL Server performance because disk IO is far slower than keeping operations in memory. Among other places, details on how this process works can be found here, which is a blog post discussing the Lock Pages In Memory feature. I will save that setting for discussion a later post.

 

The point I want to make here is that you can calculate how much memory the Windows OS and other applications may need and then subtract that from the physical memory available. There are recommendations on the internet for such things. Whatever number you arrive at would then be set as the Max Server Memory setting in SQL Server. The Available MB perfmon counter should then be judiciously monitored to ensure that there is adequate free memory left on the box, at least 1-2 GB. That will allow plenty of memory for that person who just insists on using an RDP connection to do his or her work or for a new process to fire off without bringing the server to its knees.

 

For example, let’s say that your server has 128 GB of RAM. You may decide to allocate 12 GB for the OS. Perhaps you have a vendor app that needs 4 GB. You take 128-(12+4) and that leaves 112 GB of RAM. If you are running SQL Server 2008 R2 then the Max Server Memory setting can be set to a max value of 64 GB. That is per SQL instance. SQL Server 2012 Standard supports a maximum of 64 GB and SQL Server 2014 Standard supports 128 GB. If SQL Server 2014 Standard were installed in this scenario there would be a maximum of 112 GB that could potentially be allocated to the SQL Server buffer pool for the machine described above.   Setting this limit at 112 GB, followed by the aforementioned monitoring of the Available MB performance monitor counter, would help ensure that SQL Server does not hog memory from the Windows operating system.

 

Now that there is a foundational understanding of what the Min Server Memory and Max Server memory settings are and how they work, let’s look at how to set these options in SQL Server.

 

First, there is the GUI. Open SQL Server Management Studio and connect to a SQL Server instance. Right click on the SQL Server instance name and then choose Properties. Now select the Memory page in the left pane  Manually change the values in the Minimum and Max Server Memory boxes.  Remember that this is in MB.  Then click OK in the lower right of the dialog box.  Also, remember that when you make changes in SSMS you can click the Script button at the top of the dialog and T-SQL for the particular changes will be generated for you.

 

Min and Max Server Settings

 

Second, these settings can be changed using T-SQL.  Here is an example script that sets the minimum to 12 GB and the Max to 32 GB.

 

EXEC sys.sp_configure N’show advanced options’, N’1′ RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N’min server memory (MB)’, N’12288′

GO

EXEC sys.sp_configure N’max server memory (MB)’, N’32768′

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N’show advanced options’, N’0′ RECONFIGURE WITH OVERRIDE

GO

 

I encourage you to review these settings on your SQL Server instance and consider whether they need to be adjusted.