User Guide

Configuring SQL Server’s tempdb
The SQL Server tempdb system database is a global resource that is available to all users connected to an
instance of SQL Server. It is used to hold temporary and internal objects that SQL Server uses to perform many
different operations.
Performance issues
Because tempdb is used by all databases contained in an instance of SQL Server, it can become a bottleneck for
performance. It can also cause degraded performance if a single database continues to grow at a fast pace. In both
of these cases, tempdb automatically grows in size. The result is overhead during the execution of queries,
updates and other operations.
Determining the appropriate size
It is recommended that the initial size of tempdb be set to 25% of the total user database size. For example, if an
instance of SQL Server instance 3 databases of size 250mb, 250mb and 500mb, then the size of tempdb should
be calculated as: (250 + 250 + 500) / 4 = 250. Thus, the initial size of tempdb should be set to 250mb in this
case.
To set the initial size of the tempdb:
The initial size of tempdb can be set in two ways.
1. The first way requires Microsoft SQL Server Management Studio.
a. Connect to the SQL Server instance for which you desire to change tempdb size.
b. Select the instance’s node in the Object Explorer panel.
c. Select the Databases node and the System Databases node under that.
d. Right-click the tempdb node and select Properties.
e. In the dialog box that appears, select the Files tab.
f. Modify the Initial Size (MB) value for “tempdev” in the Database Files table. Set to the value described
in the above Determining the Appropriate Size section.
g. Click OK.
2. Set the initial size of tempdb is by executing the following SQL queries:
a. Get the current size of tempdb:
USE tempdb
GO
EXEC SP_SPACEUSED;
GO
b. Set the desired size of tempdb:
USE master
GO
ALTER DATABASE tempdb
Chapter 2: Software Requirements 9