SQL Server installation does not repeat often, especially if you are using on premises servers. You just set it up, change configuration to optimal, and let it run.
It may be a different use case if SQL Server instance is running on the cloud and is being recreated every time (if, for any reasons, cloud database services are not being used and virtual machine is picked up). However, this configuration still needs to be optimal to get best possible performance of SQL Server.
In this article, we will review a few settings that are recommended to be reviewed after SQL Server installation.
Decide who will administrate your SQL Server
Usually companies are using Active Directory to control permissions on servers.
When it is used, we would recommend to include separate DBA administrators group that contains users.
Keep in mind that those users may do anything on your SQL Server instance, so choose with care. Also be very careful adding Domain Administrators group (or better avoid it).
Dedicate storage for TempDB
Starting from SQL Server 2016 installation has the ability to add several files for TempDB.
We recommend to dedicate fastest storage drive on the server for TempDB that supports parallel read\write operations. If there is no such a drive, consider adding it. After adding it, put TempDB files there. To do this, you can use query below after adjusting by your needs:
ALTER DATABASE [tempdb] MODIFY FILE (NAME = 'tempdev',
FILENAME = N'T:\SQL_DATA\tempdb.mdf' SIZE = 8 GB, FILEGROWTH = 0);
This query helps to move existing tempdb file to T:\ drive. Pay attention to Filegrowth = 0 part—the idea behind this is to set initial file size as large as possible so file does not need to be initialized later and slow down processes.
Another recommendation is to create several tempdb files. If there no other requirements, the recommendation is to have the same number of tempdb files as server has CPU cores. For example, if the server has 8 CPU cores, you can use 8 tempdb files. Query for adding tempdb data files is below:
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2',
FILENAME = N'T:\SQL_DATA\tempdb.mdf SIZE = 8 GB, FILEGROWTH = 0);
Change Default Database Path
Even if databases on a new instance will be restored on specific drives, it is still a good idea to change default database locations.
To do this, on SQL Server Management Studio right-click on instance name, choose properties, and then – Database settings.
You should change those values by actual server setup (Figure 1).
Keep in mind that it is recommended to not keep database and log files on system drive and put them separately.
Set SQL Server Memory
Just after SQL installation, default SQL Server maximum RAM memory size is set to 2147483647 MB and default minimum is set to 0.
It means after SQL Server Service starts, it will start from minimum value and grows up to maximum one while workload increases.
Recommendation is to set maximum server memory by subtracting memory amount for OS and other running services or applications from total physical memory.
If you don‘t know what size it should be and no other services or applications are running on the server, use 90% available memory for SQL Server. If SQL Server is not alone, use 50% and later adjust this number.
To set memory size, open Server Properties as on previous step where setting up default database paths, just choose Memory page like in Figure 2.
Keep in mind that sizes are defined in MB (MegaBytes).
Check MAXDOP value
Default value of MAXDOP is set to 0 – it means that all processors will be used on parallel running queries.
Changing max degree of parallelism (MAXDOP) option limits number of processors to use for parallel execution plan and prevents queries from impacting SQL Server performance by taking all CPUs on that machine (physical or virtual).
Exact value should be decided depending on hardware, workload that will be used (transactional vs analytical), NUMA and other things. However, as the beginning recommendation is to set number equal to physical cores in a single CPU socket. If, for example, server has two CPU sockets with 8 core CPUs on them without hyper-threading, set it to 8.
To do this, open Server properties like you set server memory options, just choose Advanced page as on Figure 3.
Once this value is changed, click OK to close Server properties window. No restart required to take this setting changed.
Patch the SQL server up to the latest SP/CU
Your SQL Server will receive updates that include fixes for discovered errors and security issues that could lead to potential system damage and compromising of data.
Adjust auto grow settings for all system databases
When SQL Server has to grow a file, all transactions stop. They wait until file growth operation is complete to continue.
These events can introduce unpredictable hits in performance at random times (especially if disks are performing slow
Check the Best practices to set database auto growth size on SQL server
Verify and enable TCP IP SQL protocol
All network protocols are installed by SQL Server Setup, but may not be enabled.
You can enable or disable a server network protocol in SQL Server by using SQL Server Configuration Manager:
SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for [Instance] → TCP/IP must be Enabled
Another way to enable is to execute the following statements using PowerShell:
$smo = 'Microsoft.SqlServer.Management.Smo.'
$wmi = new-object ($smo + 'Wmi.ManagedComputer').
# List the object properties, including the instance names.
$Wmi
# Enable the TCP protocol on the default instance.
$uri = "ManagedComputer[@Name='']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)
$Tcp.IsEnabled = $true
$Tcp.Alter()
$Tcp
Replace <computer_name> with the name of the computer that is running SQL Server. If you are configuring a named instance, replace MSSQLSERVER with the instance name.
The Database Engine must be stopped and restarted for the change to take effect.
Summary
Changing settings based on above paragraphs should help you know that they were not left as defaults that may hurt performance. However, pushing SQL Server to the limits and taking everything it can give, requires deeper investigation and those settings may have different values specifically on your environment.
