SQL Server & Tempdb, configuration and benchmark

Hi Guys,

Welcome back!

Topic of the Day is the TempDB database.

We have already mentioned TempDB in some other posts such as:

• SQL Server & come spostare il database TEMPDB
• Il TEMPDB e la sua configurazione.. pronti per le Ferie?
• SQL Server 2019 ed il Memory-Optimized TempDB Metadata

Using extended events to track the growth of the physical files that make up the tempDB database I'll show you some details!

Ready?

Create and extended event

In order to show you some aspects of the tempdb database i will intercept the database_file_size_change event.

First step

I need to create an event session:

       

CREATE EVENT SESSION [Whatis_inside_tempdb] ON SERVER

ADD EVENT [sqlserver].[database_file_size_change] (

    ACTION ( [sqlserver].[session_id], [sqlserver].[database_id],

    [sqlserver].[client_hostname], [sqlserver].[sql_text] )

    WHERE ( [database_id] = ( 2 )

            AND [session_id] > ( 50 ) ) ),

ADD EVENT [sqlserver].[databases_log_file_used_size_changed] (

    ACTION ( [sqlserver].[session_id], [sqlserver].[database_id],

    [sqlserver].[client_hostname], [sqlserver].[sql_text] )

    WHERE ( [database_id] = ( 2 )

            AND [session_id] > ( 50 ) ) )

ADD TARGET [package0].[asynchronous_file_target] (  SET filename = N'c:\scambio\Whatis_inside_tempdb.xel' ,

                                                    metadatafile = N'c:\scambio\Whatis_inside_tempdb.xem' ,

                                                    max_file_size = ( 10 ) ,

                                                    max_rollover_files = 10 )

WITH (  MAX_MEMORY = 4096 KB ,

        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,

        MAX_DISPATCH_LATENCY = 1 SECONDS ,

        MAX_EVENT_SIZE = 0 KB ,

        MEMORY_PARTITION_MODE = NONE ,

        TRACK_CAUSALITY = ON ,

        STARTUP_STATE = ON );

GO

ALTER EVENT SESSION [Whatis_inside_tempdb] ON SERVER STATE = START;
  
 

 

Second step

Create a table #test that will be stored into the TempDB database.

       
CREATE TABLE [#test](
[Id] [int] NULL,
[idNaz] [int] NULL,
[FirstDescr] [nvarchar](40) NULL,
[SecondDescr] [nvarchar](max) NULL
) 
      

 

Third step

I wrote a simple script that will fill the #Test table and also run some update on it:

       

DECLARE @i INT = 0;
DECLARE @rc VARCHAR(20) = 0
WHILE @i < 3
BEGIN

INSERT [#test]
(
[Id] [int] NULL,
[idNaz] [int] NULL,
[FirstDescr] [nvarchar](40) NULL,
[SecondDescr] [nvarchar](max) NULL
)
SELECT TOP ( 50000 )
[Id] ,
[idnazioni] ,
[descr1] ,
[descr2]
FROM ;

UPDATE [u1]
  SET [u1].[FirstDescr] = SUBSTRING(CAST(NEWID() AS NVARCHAR(MAX)), 0, 40) , [u1].[idNaz] = ( [u2].[idNaz] * 2 ) ,
[u1].[SecondDescr] = REPLACE([u2].[SecondDescr], 'a', CAST(NEWID() AS NVARCHAR(MAX)))
FROM [#test] [u1]
CROSS JOIN [#test] [u2];

END; 

Configure and benchmarking the Tempdb (and in wich order are mdf files filled?)

Now we will do some test about the tempDB configurations.

We will start using a TempDB with 1 mdf file (and 1 ldf file).

After running the benchmark we will shrink the TempDB and add another mdf file to the TempDB.

We repeat the test from 1 to 8 mdf files since my notebook have a 8 virtual core processor.

One Mdf and one ldf

With a single mdf the duration of the operation is about 7524 milliseconds.

Since the automatic increase is set to 64K the SO need to change the size of the mdf file 4 times.

Ldf file will change it's size 6 times.

Two Mdf

Shrink the TempDB, add an mdf file and run the same test again

Take a look at the duration of the operation. The duration is 3452 milliseconds, less that half the duration of the previous test.

Half data are written inside the first mdf file (tempdev), second half into the second (temp2)

Then data are written inside then ldf file (templog)

Three Mdf

Again shrink the TempDB, add another mdf file and run the our test.

Now the duration is equal to 4130 milliseconds.

Therefore more than the previous test.

First data are written into the first mdf file (tempdev) then into the last created mdf file (temp3)

Finally data are written into Temp2 mdf file

After that also the ldf file is written.

Remember this order.

Four Mdf

Same procedure as above.

Now the duration increase to 6170 milliseconds.

The mdf filling order is the same!

tempDev -> temp4 -> temp3 -> temp2

After the mdf files also ldf is filled.

Five, Six, Seven and Eight mdf. Other discoveries...

In the case of 5 mdf first ldf is written then mdf files are filled in the order already seen (TempDev -> temp5 -> temp4 -> temp3 -> temp2)

Same order also with 6, 7 and 8 mdf files.

Let's sum it up!

 

Results

What tempDB configuration give us more performance?

 

Well in this particular benchmark we have that using 2 mdf we have the lower duration.

Yes but....

It's not a rule.

Why?

because it depends on many factors.

It depends of the configuration (how many virtual cpu)

It depends on how much data i write

....and so on!

But more important...

what did we learn today?

A)  TempDB make the difference on the performances. try different configuration (and never set up a single mdf ...alone)

B) The order in which the physical files (mdf and ldf) of the TempDB are written

Finally ... the Round Robin

What'is round robin?

If you set for each mdf file the same initial size and the same autoincrement then SQL Server will use the round robin technique..

Yes, but what is exactly the Round robin?

Well, we will talk about this soon!
So you just have to follow me!

That’s all for today!


I hope you liked this post and found it useful subscrive and take a look also to the others post!
I am counting on it!

Luca

SQL: READY TO RUN FASTER?

Previous post: SQL Server, where are your job stored? June 20, 2020