SQL Server Health Check

Top Tips for Optimal Performance After SQL Server Installation

Updated
8 min read
Written by
Mark Varnas

SQL Server installation does not repeat often, especially if you are using on-premises servers. You just set it up, change the 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 reason cloud database services are not being used and virtual machine is picked up). However, this configuration still needs to be optimal to get the best possible performance out of SQL Server.

In this article we will review a few settings that are recommended to be reviewed after the SQL Server installation.

Decide who will administrate your SQL Server

Usually, companies use Active Directory to control permissions on servers.

When it is used, we would recommend including a separate DBA administrators group that contains users. Keep in mind that those users may do anything on your SQL Server instance, so choose them with care.

Also be very careful when adding the Domain Administrators group (or better yet, avoid it).

Dedicate storage for tempdb

Starting from SQL Server 2016, the installation can add several files for tempdb.

We recommend dedicating the 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 the query below after adjusting it to 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 the existing tempdb file to T:\ drive.

Pay attention to the FILEGROWTH = 0  part — the idea behind this is to set the initial file size as large as possible so the file will 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 the server has CPU cores. For example, if the server has 8 CPU cores, you can use 8 tempdb files.

The query for adding tempdb data files is below:

ALTER DATABASE [tempdb] ADD FILE (
	NAME = 'tempdev2'
	,FILENAME = N'T:\SQL_DATA\tempdb.mdf' SIZE = 8 GB
	,FILEGROWTH = 0
	);

Change the default database path

Even if databases on a new instance will be restored on specific drives, it is still a good idea to change the default database locations.

To do this, in SQL Server Management Studio right-click on the instance name, choose Properties, and then – Database settings.

You should change those values according to the actual server setup (Figure 1).

Keep in mind that it is recommended not to keep both the database and log files on the system drive, but rather to separate them.

Set SQL Server memory

After SQL installation the default SQL Server maximum RAM memory size is set to 2147483647 MB and the default minimum is set to 0.

That means after SQL Server service starts it will start from the minimum value and grow up to a maximum of one as the workload increases.

It is recommended to set the maximum server memory by subtracting the memory amount for OS and other running services or applications from the 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 the memory size, open Server Properties as on the previous step when setting up the default database paths, and choose the Memory page as in Figure 2.

Keep in mind that sizes are defined in MB (MegaBytes).

Check MAXDOP value

Default value of MAXDOP is set to 0 – means that all processors will be used on parallel running queries.

Changing max degree of parallelism (MAXDOP) option limits the number of processors to use for the parallel execution plan and prevents queries from impacting the SQL Server performance by taking all CPUs on that machine (physical or virtual).

The exact value should be decided depending on the hardware, workload that will be used (transactional vs. analytical), NUMA and other things.

As a starting point it is recommended to set the number equal to the physical cores in a single CPU socket. For example, if the 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 the server memory options above, and choose Advanced page as on Figure 3.

Once this value is changed, click OK to close the Server properties window. No restart is required to confirm this setting change.

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 the compromising of data.

Adjust autogrow settings for all system databases

When SQL Server has to grow a file, all transactions stop. They wait until file growth operation is complete before continuing.

These events can introduce unpredictable hits in performance at random times (especially if the disks are performing slowly).

Check the best practices to set database autogrowth 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 it 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 the settings based on the recommendations above will ensure they are not left as defaults that could 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 depending on your specific environment.

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide 
operational peace of mind

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Leave a Comment

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials