SQL Server Tempdb Configuration During Installation

Tempdb is a very important system database for heavily used systems. If it is not configured correctly, it can cause serious performance problems in the system. With SQL Server 2016, we can configure the Tempdb database during the installation. In my article “SQL Server System Databases” you will find details about how to configure tempdb.

In this article, I will talk about the screen where we can configure tempdb during installation.

When you come to Database Engine Configuration phase during SQL Server 2016 installation, we go to Tempdb tab and we can set tempdb file number, initial size, auto growth data and log files paths from the fields you see below.

It is generally recommended that you set the number of tempdb files as the number of cpu in your system. By creating multiple files, we aim to prevent contention that may occur in tempdb. Creating too many tempdb files can also have a bad effect. Usually, I have created 8 or 16 tempdb files on a machine with 8 cpu and I did not have any problems so far.

One of our clients was experiencing a serious contention in tempdb, and the first thing I did was to increase the number of tempdb files. As a result, the problem was solved.

Below, you can see the screen where you can configure tempdb during installation.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *