SQL Server Tempdb Initial Size

Question

Referring from this blog, it is suggested that set size each data file at 1GB to start with a 512MB growth rate.

And I have also seen in some other materials that it need to be sized based on actual use.

Any suggestions?

Answer 1

need to be sized based on actual use.

Indeed, the size of the TempDB heavily depends on your SQL Server workload (which we don't know).

In common a DWH/reporting DB requires sometimes more size for sorting operations etc. then a small OLTP.

But 1GB + 500 MB growth is a common and good value for most SQL Server.

Answer 2

Hi @Riley

It is recommended that you analyze your existing workload by performing the following tasks in a SQL Server test environment:

(1) Set the autogrowth of tempdb.

(2) Simulate individual queries or work tasks while monitoring tempdb space usage.

(3) Simulate performing some system maintenance operations, such as rebuilding indexes, while monitoring tempdb space.

(4) Use the tempdb space usage values in the previous steps 2 and 3 to predict how much space will be used under the total workload; and adjust this value for the planned concurrency. For example, if a task uses 10 GB of tempdb space, and in a production environment there may be up to 4 such tasks running at the same time, reserve at least 40 GB of space.

(5) According to the value obtained in step 4, set the initial size of tempdb in the production environment. Autogrowth is also turned on.

The number of tempdb files and the size settings should not only meet the needs of user tasks, but also consider performance optimization.

Best regards,

Cosmog Hong

Answer 3

When you install a recent version of SQL Server, Setup will suggest an initial configuration that will make sense for a smaller workload. But if you are going to put a multi-terabyte database on the instance, you should probably increase the size to 200 GB in total directly.

What actual size you need? As Olaf says, that depends on your workload.

And don't forget to put tempdb on your fastest disk(s)!

Answer 4

@Riley

Just to add the information from Microsoft document about “Optimize tempdb performance in SQL Server” which talks about sizing of tempdb

Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. Preallocation prevents tempdb from expanding too often, which affects performance. The tempdb database should be set to autogrow to increase disk space for unplanned exceptions.

Data files should be of equal size within each filegroup, because SQL Server uses a proportional-fill algorithm that favors allocations in files with more free space. Dividing tempdb into multiple data files of equal size provides a high degree of parallel efficiency in operations that use tempdb.

Set the file growth increment to a reasonable size and set it to the same increment in all data files, to prevent the tempdb database files from growing by too small a value. If the file growth is too small compared to the amount of data that's being written to tempdb, tempdb might have to frequently expand via autogrowth events. Autogrowth events negatively affect performance.

Thank you!