The tempdb database, introduction and recommendations

Introduction

Many times the people focus on the user databases to improve the performance, but sometimes the problem is not the user database itself. Sometimes the problem is the tempdb.

In this article, we will give a brief introduction about the tempdb database, show how to create some temporary objects there and show how to improve and monitor it.

Requirements

SQL Server 2005 or later (in this example, we are using SQL Server 2014)

Getting started

The temdb is a special system database used to store temporary objects and data like tables, views table variables, tables returned in functions, temporary objects and indexes.

Figure 1. The tempdb

The temdb can also be used for internal operations like rebuilding indexes (when the SORT_IN_TEMPDB is ON), queries using UNION, DBCC checks, GROUP BY, ORDER BY. Hash join and Hash aggregate operations.

The tempdb is in simple recovery model because the information stored is temporary.

Figure 2. The simple recovery mode.

You cannot backup the tempdb database because the data is temporary.

Figure 3. There is no backup option

Let’s start with a brief introduction creating temporary tables. If you already know how to create temporary tables, you can jump to the tempdb recommendations.

Creating temporary objects

There are several temporary objects in SQL Server. Let’s start with the local temporary table.

Local temporary tables

The following example creates a temporary local table in the tempdb database with the information of the Person.Address table.

SELECT [AddressID]

,[AddressLine1]

,[AddressLine2]

,[City]

,[StateProvinceID]

,[PostalCode]

,[SpatialLocation]

,[rowguid]

,[ModifiedDate]

into #address

FROM [Person].[Address]

If you check the tempdb, you will find the table in the temporary table folder:

Figure 4. The local temporary table

The prefix # is used to indicate that it is a local temporary table. You can access to the temporary tables from any database. These tables or objects created are visible only in the session where they were created. They are very secure. As you can see in the image above, there is an internal left-padded numeric suffix in the table. This is used to differentiate from other local tables created by different procedures or processes.

If you try to create a view, a function or a trigger based on a local temporary table you will receive an error message.

See the following example:

create view v_address

select addressid

from #address

The error message displayed would be the following:

Msg 4508, Level 16, State 1, Procedure v_address, Line 22
Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.

Global temporary tables

These tables are global and can be accessed from other sessions. This option is not secure and consumes many resources. It is not recommended to use this option, but it is good to know it in order to correct it if someone is using it.

The syntax is similar than the local temporary tables, but with double ##:

SELECT [AddressID]

,[AddressLine1]

,[AddressLine2]

,[City]

,[StateProvinceID]

,[PostalCode]

,[SpatialLocation]

,[rowguid]

,[ModifiedDate]

into ##address

FROM [Person].[Address]

The global tables do not require suffixes.

Figure 5. The global temporary table

The table variables

The table variables can be used instead of the global and local temporary tables. They are easier to handle and to dispose the resources, which makes the use more efficient. The following example will show how to create the table variable, how to insert data on it and how to show the variable values. The use is similar to a simple table. The following example shows how to declare a table variable, how to insert data and how to do a select:

DECLARE @myTableVariable TABLE (id INT, col1 varchar(20))

insert into @myTableVariable values(1,'First value'),(2,'Second value')

select * from @myTableVariable

As you can see, it is very easy to use them like any other variable. In general, the local temporary tables are more efficient. The table variables are also stored in the tempdb. However, depending on the scenarios you can only use table variables or local temporary tables. For example, if you need indexes, you can only do it with a local temporary table. By the other hand, in a function, you can only use a table variable. Note that the statistics are not maintained in table variables. In routines, table variables require fewer compiles.

A special type of table variable is the Table-value parameter. This is very useful for client applications.

The following example shows how to create and use Table-value parameters:

CREATE TYPE Table_value AS TABLE

(id int

, name varchar(30) );

We will also create a simple table to fill with the Table-value parameter in a stored procedure:

CREATE table product

(id int

, productname varchar(30))

The stored procedure will load the data from the Table-value parameter into the product table created.

CREATE PROCEDURE insertdata

@myTable table_value READONLY

SET NOCOUNT ON

INSERT INTO product

([id]

,productname)

SELECT id,name

FROM @myTable;

Now, we are going to declare the Table-value parameter insert data there and call the stored procedure and do a select in the table to make sure that the data was inserted:

DECLARE @TVP

AS Table_value;

INSERT INTO @TVP (id, name) values

(1,'Camera'),

(2,'iPad');

EXEC insertdata @TVP;

select * from product

If everything is OK, you should be able to see the new data inserted in the products table using the stored procedure:

Figure 6. The Product table data

Tempdb recommendations

By default, the tempdb size is 8 MB approximately. If your databases handle a big amount of data, you may need a bigger Tempdb database. It is not a good idea to have a small size if the tempdb database has to growth very often.

To check the tempdb size, in the SQL Server Management Studio (SSMS), go to Databases>System Databases and right click on the tempdb database. Click on the files page:

Figure 7. The initial size of the tempdb

If possible, move your tempdb to different disks than the user databases. That will increase the performance.

If possible, use RAID 0 to improve the performance.

Make sure that the autogrowth is enabled (by default it is in 10%, which is usually OK).

Monitor the tempdb size with the Performance Monitor or other tools of your preference. To do this open the performance monitor in your Windows Operative System:

Figure 8. The system monitor

In the system monitory, to the SQLServer:Databases>Data File(s) Size and select the tempdb database.

Figure 9. The counter to check the tempdb size

In addition, it is important to check the SQL Error Log in SSMS:

Common errors in the SQL error Log file related to the tempdb space are the errors 1101, 1105, 3959, 3967, 3966, 3958.

Also, make sure to maintain your indexes in your user databases. Rebuild or Reorganize your indexes in frequency to reduce the tempdb use.

Conclusion

In this chapter, we introduce the tempdb and explained how to create local, global and table variables in the tempdb.

We also introduced some recommendations to monitor the tempdb and improve the performance.

Author
Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

PostgreSQL tutorial to create a user - November 12, 2023
PostgreSQL Tutorial for beginners - April 6, 2023
PSQL stored procedures overview and examples - February 14, 2023