Esat Erkec
Analyze Memory Usage By Memory Optimized Objects

Overview and Performance Tips of Temp Tables in SQL Server

March 31, 2021 by

In this article, we will learn the essentials of the temp tables in SQL Server and we will also discuss some performance tips about temporary tables.

What is a temp table?

As its name indicates, temporary tables are used to store data temporarily and they can perform CRUD (Create, Read, Update, and Delete), join, and some other operations like the persistent database tables. Temporary tables are dropped when the session that creates the table has closed, or can also be explicitly dropped by users. At the same time, temporary tables can act like physical tables in many ways, which gives us more flexibility. Such as, we can create constraints, indexes, or statistics in these tables. SQL Server provides two types of temporary tables according to their scope:

  • Local Temporary Table
  • Global Temporary Table

How to create a local temp table

The accessible scope of local temporary tables is limited by the connection in which they were created. In other words, the local temporary tables are visible for the duration of the connection and when the session is closed the local temporary table are dropped automatically. Local temporary tables can be created using the “CREATE TABLE” syntax but a single hashtag (#) sign must be added in front of their names. The following example script will create a new local temporary table named TempPersonTable.

When we query the TempPersonTable, it will return an empty result set.

Creating a temporary table in SQL Server

After creating the table, we can insert data into it as the persisted tables.

Inserting data into a table

At the same time, we can create a temporary table using the SQL SELECT INTO statement command. The following query will create a local temporary copy of the Orders table.

Querying data from a table

The local temporary tables are created in the tempdb database with a unique name because they can be created with the same name by the other connections. In this way, SQL Server prevents the same name conflict.

Show tables of the tempdb database

How to create a global temp table

The global temporary tables are created using the CREATE TABLE statement and their names must be prefixed with the double hashtag (##) sign. These tables can be accessed by all other sessions, unlike local ones. The following code will create a global temporary table.

After creating the temporary table, we can populate and query it.

Global temp tables

Global table names are not transformed into a unique name.

Show tables of the tempdb database under the folder

Global temporary tables can be dropped explicitly with the DROP TABLE statement on any connection or automatically dropped when the session is closed that creates the table. However, this global table should not be actively in use by the other sessions. If it is actively in use by the other sessions, SQL Server waits until the completion of the last SQL statement activities that use the global temporary table, and then the global table is dropped. Let’s illustrate this interesting characteristic of the global temporary tables using a very simple example:

Firstly, we will create a global temporary table and populate it with some rows.

Populating data into a table

As a second step, we will open a new query window and start a new transaction to delete all the data. However, we do not commit or rollback the transaction so, the table will be actively in use by the connection.

We close the first connection that has created the table and then we check the existence of the global temporary table.

Starting a new transaction

As we can see, SQL Server does not drop the temporary table if it is actively used by any session. After committing or rollbacking the transaction, the global table will be dropped and invisible for the same connection.

Ending a new transaction

Temp table performance tuning tips

The temporary tables might be very useful when we are required to transform or modify the big datasets. In SQL Server, nothing comes without a price therefore we need to take into account performance considerations about the temporary tables when we decide to use it.

  • Tune the tempdb database: All temp tables are created in the tempdb database, for this reason, we have to ensure all performance settings are properly applied to the tempdb database
  • Only insert the required columns and data: After creating the temporary table, we need to insert the necessary data so that we can increase the data insert performance
  • SELECT INTO vs INSERT INTO: In order to populate data into the temporary tables we can use the SELECT INTO and INSERT INTO statements and these two statements offer similar functionalities. In the context of performance, these two statements may have some differences. For this reason, making a test in your database is the best solution to decide which one gains a performance advantage over another one
  • Drop temp tables quickly: As possible we need to drop temporary tables explicitly and we should not wait for automatic deletion when the connection is ended. So that, we can free up the tempdb resources more quickly
  • Use indexes if you are sure they are needed: Creating an index is a resource-consuming process for SQL Server so we must make sure there will be performance improvements when we decide to create them. Related to this issue, creating a clustered index can be the first option, and then potential nonclustered indexes can be created

The tough opponent

The main purpose of the temporary tables is to store data temporarily. On the other hand, in-memory optimized tables have been entered our data life with SQL Server 2014, and schema only optimized tables can store the data until the database restart. In this context, the temp tables and schema only memory-optimized tables have similar functionalities in terms of storing data temporarily. The main advantage of using the memory-optimized tables instead of the temporary tables is to remove I/O activities so it has more performance but we have to notice that they consume memory. In the following example, we will compare the temp table performance against the memory-optimized table. At first, we will create the required filegroup and files for the memory-optimized tables.

Now we will create a memory-optimized table and insert 10 million rows into it.

This insert operation has completed about 11 seconds when we use a schema only memory-optimized table.

Performance test about a temporary table

As we mentioned, the memory-optimized tables consume memory, we can analyze the memory consumption for this table with help of the Memory Usage By Memory Optimized Objects report.

Launch Memory Usage By Memory Optimized Objects

In this report, we can see that TestMemOptTable consumes 762 MB of memory.

Analyze Memory Usage By Memory Optimized Objects

Now we will repeat the same insert operation for a local temp table.

The insert operation has completed about 35 seconds for the temporary table.

Analyzing time statistics of a query

In this small test, we saw that there is a dramatic performance difference between the memory-optimized and temporary tables. As the last test, we will not index the temporary table and use the TABLOCKX hint for the temporary table.

Analyzing time statistics of a query which creates a parallel execution plan

The result of this test is very interesting because the insert operation was completed in only 6 seconds and this time is smaller than the memory-optimized table. This performance gain explanation is hidden in the execution plan of the insert query. When we analyze the execution plan of the query, we can see a parallel execution plan.

Parallel execution plan

SQL Server has generated a parallel execution plan so it has performed the insert operations in a parallel manner. The parallel insert operations can be used by the database engine when the destination table is a heap and TABLOCKX is used in the insert statement.

Conclusion

In this article, we learned how we can create temp tables and also explored some interesting performance tips about them.

Esat Erkec
2,513 Views