Why is my SQL Server so slow? TempDB & enhancements in SQL Server 2022.

Hi Guys,

Welcome back to this blog.

Today we will talk about slowness.

Qne of the most frequently asked questions is almost certainly: why is my SQL Server so slow?

Today we will answer this question.
But not only that.
We will make a nice talk about the tempdb database to finally get to see what improvements SQL Server 2022 brings us on this front.

Fasten your seat belts and enjoy the reading
 

 

Why is my SQL Server so slow? 

 

Let’s start by saying what is the TempDB database.

 

The TempDB database is a temporary system database even if its structure is essentially just like any other user database. 

It is temporary in the sense that it is emptied every time the SQL Server instance is restarted.

What exactly is written in the TempDB database?

Well, first of all goes into the TempDD temp tables and table variables

The creation of a temporary (#) table on a user database "IS A" creation of a table in the TempDB:

Similarly ...the same thing happens when I create a  table variable

But that’s all it gets into the tempDB database?
Absolutely not!

If you are using snapshot isolation, read committed snapshot row versions are stored into the tempDB.

Let’s say that for the TempDB database is a remarkable work because:

• Each time you update a row of your table a new row is stored into the TempDB database.
• A commit or a rollback will delete all the rows stored into the TempDB database 

This is what we say is the side effect of using Optimistic Concurrency.

Even the execution of a Query use the TempDD database.

When you execute a Query an execution plan is created (or retrieved from the plan cache... of course). 

Logical operation resolved by using Hash will use the TempDB.

Goes into the TempDB all objects that use Worktable such as Spools, cursors, sorts, and temporary LOB storage.

Again...

• Online index operations: if you are maintaining your indexes with the ONLINE ON keyword a shadow copy of the index is stored into the TempDB.
  
• DBCC CHECKDB creates shadow copies of the data in the tempdb and then operates on them.

Have you seen how many things pass through the TempDB database?

Now it is also clear why this database must be put on the most performing disk.
It’s also clear that:

The more database users we put on the same instance the more we put the tempdb database under pressure. 

 

Configure the TempDB to get the best performance?

We just said that it will be a good idea to put the TempDB on the most performing disk.
Alternatively we can put this database on a separated and dedicated physical disk.

In addition:

You can create a physical (mdf & ndf) file for each vCPU where all files must be equally sized.  

In this way the SQL Server engine can write to each file in parallel.

 

Finally, it is also important to set an Appropriate value for the Autogrowth parameter.

Every time SQL Server has to increase the size of a physical file (mdf, ndf or ldf) it is forced to stop and wait for the operating system to perform this operation and this can cause blocks.

 

Tempdb and bottlenecks

Microsoft over time and over the course of new versions of SQL Server has always tried to improve the performance of this temporary database also because over time, we have moved to larger machines with larger workloads and always increasing.
At one point concurrency problems began to emerge in the tempdb.

 

Object allocation contention
 

A first type of concurrency issues emerged is related to object allocation contention.

Yes, but what is the contenction of the object allocation?

 

Follow me!

 

From what we mentioned earlier the typical workload of the TempDB is the continue creation and destruction of objects like tables, indexes, etc etc.

 

You can notice this phenomenon when the server is experiencing a heavy load and see severe lock appears.

 

In this case the workload is running slow and the CPU is underutilized.

 

In this case the best practice is to create multiple mdf file, each one with the same size and same growth rate. 

This works because the allocation process is distribued  across multiple partition.

 

In the picture below we can see the structure of an mdf (and ndf) file:

 

Looking at the picture above for each mdf (or ndf file) we have:

One header page (also called page 0).

Then we have a PFS (free space page contains) page.
This page contains information on how pages are full. We have one PFS page per 8088 pages.

Then we have the GAM page (global allocation map) where SQL Server write when it has to allocate a uniform extension .

The GAM is a bitmap: if a bit is set to "1" then that extent is available to be allocated, and if it is 0 then it is not available to be allocated.

Then we have the  SGAM (Shared global allocation map) page. It works as the GAM but for the mixed extent.

I tell you this because it is necessary to understand what happens when an object is created.

 

Firstly,  each time an object is created SQL server access to the PFS page to know which pages are free.

Then if  SQL Server needs to allocate a uniform extent it will go to the GAM page and check the availability. 

Once SQL Server has allocated the extent, it just flips the bit for that GAM page from 1 to 0 to show that it is no longer available.

 

The same happens if SQL Server needs to allocate a mixed extent. SGAM pages instead of GAM pages are used.

 

All this to say that:

If you have multiple files in the tempdb database, SQL Server is able to immediately get another header, PFS, GAM and SGAM thus sharing the workload on these files.

 

Metadata contention

Over the years, we have seen the continuous growth of workloads. These are supported by more powerful servers, more powerful disks and memories and more and more processors available.

We saw another kind of contention arise.

This type of Contention is called "metadata Contention" and it is not related to the I/O subsystem.

It is instead related to the memory.

In this case there is a contention  when there are two or more threads that try at the same time to edit the same page in memory.

To address this issue Microsoft has introduced in SQL Server 2019 a feature called  memory-optimized tempdb metadata

This feature is basically a combination of the in-memory OLTP and the temp table metadata features. All system tables and the tempdb system tables have become memory optimized non-durable tables.

 

SQL Server 2019 and memory optimized tempdb metadata. A benchmark!

What performance improvements does this feature bring? 

Let’s see some results.

For this test we used a tool called RMLUtils that allows you to run in parallel and on multiple threads T-SQL commands.

To stress the tempdb database we create a script that deletes and creates a temporary table.

IF OBJECT_ID('TempDB..#TEMPTABLE','U') IS NOT NULL
  DROP TABLE #TEMPTABLE
CREATE TABLE #TEMPTABLE (ID int indentity(1,1), code VarChar(20), Descr VarChar(80))

The program will run this script on 400 threads at once.

The first time we run the script the memory optimized tempdb metadata is not enabled.

While script is running we observe a list of suspended task. The wait type is PAGELATCH.

 

The running time is 3 minutes and 54 seconds

Let’s see what happens if we enable the memory-optimized tempdb metadata feature.

To enable this feature you must execute this command:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Then restart the instance of SQL Server.

If the feature is active the following command will return the value "1!

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

We now do another test...

First of all no more locks!

This time the test ends in 2 minutes and 23 seconds.

Wow! the time is dropped of 40% ...

For completeness I must say that this feature also has some limits that we will see soon. So before activating it.

 

Temp table cache contention

Every time we create or drop a global temporary tables, SQL Server insert or remove metadata from the TempDB system catalog. 

When we have a workload where creation and dropping occur very frequently, a Data Definition Language (DDL) contention in the TempDB system catalog can occur and cause throttle the workload throughput.

To solve this behavior, starting from SQL Server 2005, Microsoft introduced the caching of the temp table.

If you drop a cached temp table physically SQL Server does not delete the metadata. SQL Server in fact try to reuse Metadata (for example when you call the same stored procedure and that create a temp table with the same name)

Temp table are cached only under some conditions.

In the following cases table are not cached:

• Named constraints are not created
• DDL statements that affect the table are not run after the temporary table has been created.  for example the CREATE INDEX or CREATE STATISTICS statements
• Temp table is not created by using dynamic SQL
• Temp table is created inside another object, such as a stored procedure or trigger

So, we must therefore be very careful!

In the next post  I will present you a benchmark to show the difference of performance when we use cached temporary table or not!

And now we will talk about what SQL Server 2022 bring us:

SQL Server 2022 TempDB improvements

In SQL Server 2022 Microsoft have finally addressed the last common areas of contention by introducing concurrent GAM and SGAM updates, as it had already done with SQL Server 2019
for the Concurrent PFS updates.

Before SQL Server 2022 under higher concurrent workloads we may experiment GAM contention if many different threads attempt to allocate extents on the same GAM page.

In this case, since only one thread can modify the GAM page at a time, each thread must first wait for another thread to release their UPDATE latch.

With SQL Server 2022 we can do concurrent updates to the GAM and SGAM under a shared latch rather than using the update latch.

 

That'all for today mates!

I hope you enjoyed this post, Luke!

 

 

Previous post: SQL Server 2022 RC0, new features and capabilities. The great improvement of the TRIM function, Integrated acceleration and other features