Faster INSERTs in Tempdb – Optimized/Minimal Tempdb Logging

This blog post first appeared on SQLMaestros

In this blog post, we will see how SQL Server optimises Tempdb logging for faster CRUD operations. For the purpose of this tutorial, we will perform the same operations in a regular user database versus Tempdb, and note down execution time (performance comparison).

Thanks to Hugo Kornelis, Thomas Grohser, Uwe Ricken, Simon Sabin & Pam Lahoud who answered my questions helping me demystify this.

Let’s create a test table in user database (AdventureWorks2016) and perform an INSERT of 10000 records in a loop.

-- change database context
use AdventureWorks2016
GO

-- Create table testtable
IF OBJECT_ID('dbo.testtable', 'U') IS NOT NULL DROP TABLE dbo.testtable;
GO

-- creating a table
-- 1 record per page (just for the test)
create table testtable
(name char(8000) default 'Hi')

-- Insert 10000 records (note: this is user database)
-- note the total execution time
SET NOCOUNT ON;
declare @cnt int = 0
while @cnt<10000
begin
INSERT INTO dbo.testtable DEFAULT VALUES;
set @cnt=@cnt+1
end
GO

-- Total execution time in user database?

The total execution time of INSERT loop on my slow VM is 8 seconds. Your’s may be a bit faster 🙂

Now let’s do the same in Tempdb.

-- Lets do the same in Tempdb database
-- change database context
use tempdb
GO

-- Create table testtable
IF OBJECT_ID('dbo.testtable', 'U') IS NOT NULL DROP TABLE dbo.testtable;
GO

-- creating a table
-- 1 record per page (just for the test)
create table testtable
(name char(8000) default 'Hi')

-- Insert 10000 records (note: this is Tempdb database)
-- note the total execution time
SET NOCOUNT ON;
declare @cnt int = 0
while @cnt<10000
begin
INSERT INTO dbo.testtable DEFAULT VALUES;
set @cnt=@cnt+1
end
GO

-- Total execution time in Tempdb?

In Tempdb, the INSERT operation takes less than 1 second. 8 seconds vs less than a second -That is quite a difference.

This happens because of minimal logging in Tempdb. In other words, Optimized Tempdb logging. SQL Server generates far less log bytes for Tempdb log file because unlike a user database, Tempdb only has take care of UNDO, not REDO. Tempdb  is a throwaway database. In case of crash recovery, SQL Server has to perform UNDO & REDO for a user database. But for Tempdb, since it is always recreated on instance start, there is no redo information needed.

Let’s explore the internals.

We will recreate the tables and just insert one record and check the log records.

-- Lets explore some internals
-- This time let's insert only 1 record

-- change database context to user database
use AdventureWorks2016
GO

-- DROP testtable
DROP TABLE dbo.testtable;
GO

-- creating the table again
create table testtable
(name char(8000) default 'Hi')
GO

-- clean starting point
checkpoint
GO

-- Insert 1 record
INSERT INTO dbo.testtable DEFAULT VALUES;
GO

-- Lets do the same in Tempdb
-- change database context to Temdb

use tempdb
GO

-- DROP testtable
DROP TABLE dbo.testtable;
GO

-- creating the table again
create table testtable
(name char(8000) default 'Hi')
GO

-- clean starting point
checkpoint
GO

-- Insert 1 record
INSERT INTO dbo.testtable DEFAULT VALUES;

Now let’s check the log records for both the databases.

-- Lets compare the Log Records
-- run from Start to finish as a single batch
--Start
use AdventureWorks2016
GO

select Operation, [Log Record Fixed Length], [Log Record Length], [RowLog Contents 0], [RowLog Contents 1], [RowLog Contents 2], [RowLog Contents 3], [RowLog Contents 4], [RowLog Contents 5] from fn_dblog (NULL, NULL)
where Operation = 'LOP_INSERT_ROWS'

use tempdb
GO

select Operation, [Log Record Fixed Length], [Log Record Length], [RowLog Contents 0], [RowLog Contents 1], [RowLog Contents 2], [RowLog Contents 3], [RowLog Contents 4], [RowLog Contents 5] from fn_dblog (NULL, NULL)
where Operation = 'LOP_INSERT_ROWS'
--FInish

-- Observe: The differences in columns Log Record Length, RowLog Contents 0 & RowLog Contents 2

So, the key thing to observe is the Log Record Length. 8100 bytes versus 72 bytes. Why 8100 bytes? Well, remember our table definition? We had a fixed length character column ‘name’ 8000 bytes.

You can also compare the content in RowLog Contents 0 and 2 between user database and Tempdb.

This was an INSERT example. What will be the UNDO of an INSERT? Well ‘no existence’. If this was an update operation then SQL Server has to store the before image and after image of the record, and numbers would have been different.

Let’s do an update of this record and observe a few more internals.

-- Now, let's do an update on both the tables (databases)

-- First, user database update

use AdventureWorks2016
go

checkpoint
GO

-- update 1 record
update testtable
set name = 'www.DPS10.com'


-- Next Tempdb database update

use tempdb
go

checkpoint
GO

-- update 1 record
update testtable
set name = 'www.DPS10.com'

Now, lets compare the log records again. This time, we are filtering on ‘LOP_MODIFY_ROW’.

-- Lets compare the Log Records
-- run from Start to finish as a single batch
--Start
use AdventureWorks2016
GO

select Operation, [Log Record Fixed Length], [Log Record Length], [RowLog Contents 0], [RowLog Contents 1], [RowLog Contents 2], [RowLog Contents 3], [RowLog Contents 4], [RowLog Contents 5] from fn_dblog (NULL, NULL)
where Operation = 'LOP_MODIFY_ROW'

use tempdb
GO

select Operation, [Log Record Fixed Length], [Log Record Length], [RowLog Contents 0], [RowLog Contents 1], [RowLog Contents 2], [RowLog Contents 3], [RowLog Contents 4], [RowLog Contents 5] from fn_dblog (NULL, NULL)
where Operation = 'LOP_MODIFY_ROW'
--FInish

-- Observe: The differences in columns Log Record Length, RowLog Contents

This time the difference in bytes is quite less. The update is only for 13 characters (https://www.DPS10.com) (26 bytes) + some overhead.

Also note the undo content for Tempdb now in RowLog Contents 0.

You can try a few more things. Eg; increase the length of the character data and fire an update and observe the difference. Perform a delete and observe the difference, so on.

Pro Tip #1: Yes, use Tempdb for all temporary data, ETL, etc.
Pro Tip #2: The age old best practice – focus on the choice of data types !

Next, I will be covering some stuff on Eager Writes.

Thanks for reading. If you liked the content, do share it. I am on Twitter if you wish to connect: https://www.twitter.com/A_Bansal
You can also subscribe to my exclusive newsletter ConnectWithAB – https://sqlmaestros.com/stay-connected-with-amitbansal/