Everyone loves the temporary table. temporary tables can be very useful when trying to execute complex operations, or get around optimizer quirks. But what happens if you love them a little too much? In this post we'll go through some common tempdb latch contention scenarios you might only see under extremely heavy load, and how you can use memory-optimized table variables to remove or reduce this contention.

In-Memory OLTP is a new technology that requires some research before implementation, but hopefully this post gets you interested enough to give it a try.

Tempdb Latch Contention

In my environment we have a highly concurrent workload and make heavy use of tempdb. In the past we have run into the common tempdb contention issues most people run into, PFS/SGAM contention. This was easy to fix by adding more tempdb files, but after a change in workload patterns we started seeing contention in a new place: the base system tables in tempdb.

For more information on troubleshooting PFS/SGAM contention in tempdb, please refer to this post by Jonathan Kehayias: Optimizing tempdb configuration with SQL Server 2012 Extended Events

Understanding Whats Happening

Before you can understand why this contention occurs, it is important to get a basic understanding of what happens in SQL Server when you create a temporary table. Whenever you create a temporary table, entries are made in various system tables in tempdb just as if you were creating a normal table in a user database, and in many cases the temporary table definition is cached.

Cached tables are stored in tempdb.sys.tables with a hex value in place of the name they were given when they were created. When you create a table that is already in cache, a simple rename operation occurs instead of an insert into the system tables. This will be important to understand later, and this is about all we are going to cover in this post regarding temporary table caching.

Temporary table caching is a complex topic that is outside the scope of this post, but is covered beautifully by Paul White in his post Temporary Table Caching Explained

If you create a temporary table then query tempdb.sys.tables you will see a list of cached objects (named with a hex value) as well as an entry for the temporary table you just created:

--== Create a table
CREATE TABLE #Test ( ID INT );

--== See all the tables
SELECT  *
FROM    tempdb.sys.tables;

--== See just our table
SELECT  *
FROM    tempdb.sys.tables
WHERE   name LIKE '#Test%';

If you also query tempdb.sys.columns for the associated object_id, you will see what you would expect in a standard user database, a list of columns for your temporary table. Where we get into trouble is when we are creating these temporary tables at an extremely high rate. A quick query will show you why:

USE tempdb;
GO

EXEC sp_help 'sys.sysschobjs';
GO

sp_help is quite possibly the most under-appreciated system stored procedure on your instances. I highly recommend playing around with it to see what you can find.

Running the above query will give you valuable details about the system base table sys.sysschobjs, the base table for sys.tables. An interesting thing to note is that there are 4 indexes on this table, one of which is a non-clustered index that leads with the name column, and another that leads with a TINYINT column named nsclass. In situations where you have potentially hundreds of client requests attempting to create a temporary table it is very possible to start seeing contention on this table. Below I will show you how to reproduce this contention, and how to investigate any live contention you might see on your own instances.

Creating Latch Contention

To recreate this sort of contention we are going to use the OSTRESS command line utility (Get it here: https://support.microsoft.com/en-us/kb/944837). ostress is a simple command line utility you can use to execute SQL queries using multiple threads. This will need to be installed before continuing on with any of the demos below.

To prepare for our contention test we need to create a test database and define a stored procedure that simply creates a temporary table. 

USE master;
GO

CREATE DATABASE ContentionTest;
GO

USE ContentionTest;
GO

CREATE PROCEDURE dbo.Test
AS 
CREATE TABLE #Test (
    Id INT,
    Col1 NVARCHAR(128)
);

INSERT INTO #Test
SELECT 1,'Test';

With our procedure defined we can fire up the RML Cmd Prompt ( now found in your start menu ) and run ostress. Once at the RML prompt, type the following command ( in our example we are running this against a local instance ):

ostress -Q"EXEC ContentionTest.dbo.Test;" -n500 -r500 -S"localhost"

WARNING: Do not run this test on a production instance. Depending on the configuration of the instance you could quickly exhaust the worker thread pool, making the instance unresponsive.

This will fire off 500 threads (-n), each executing our stored procedure 500 times in succession (-r). This test will take a minute to execute, even on a fairly capable machine. While this is happening, lets see if we have any contention. If you need to extend the duration of this command, increasing the -r value should do the trick.

Open SSMS and connect to the instance in question and run the following:

USE master;
GO

SELECT  es.session_id,
        es.login_time,
        er.wait_type,
        er.wait_resource,
        er.command,
        DB_NAME(er.database_id) AS dbname
FROM    sys.dm_exec_requests AS er
        INNER JOIN sys.dm_exec_sessions AS es
            ON er.session_id = es.session_id
WHERE   es.is_user_process = 1;

If the test is running properly you will likely see a lot of sessions with a wait type of PAGELATCH_EX, waiting on some random database page. In my test for example, waits primarily occurred on a wait resource identified as: 2:1:14469

This can be decoded to mean that we have PAGELATCH_EX waits on page 14469 of file 1 in database 2 (tempdb). So what's on this page? Let's take a look:

DBCC PAGE(2,1,14469,3) WITH TABLERESULTS;

Notice we just took the wait resource from the results of the query above, traded out our colons for commas, and added a 3

What you'll typically get back are two result sets depending on the type of page you are looking at. The first result set contains, among other information, the page header. The second is a dump of the actual data on the page. For this example we are interested in the first result set, we are looking for two rows with the following field names: Metadata: ObjectId and Metadata: IndexId. In my case I am seeing 34 and 2 respectively.

Now that we know the object id of the table we are seeing contention on we can use sys.objects to look up the table name, and use the index id to see which index this contention is on:

USE tempdb;
GO

SELECT  name
FROM    sys.objects
WHERE   object_id = 34;

If all worked, you should now see that we have contention on the sysschobjs table. Earlier we discussed using sp_help to get index details on system tables, if we do that now and look at index 2, we will see the lead column is nsclass which is a tinyint field. Using a tinyint as a lead column is typically a terrible idea since there is little selectivity on such a narrow field, and this is no exception.

This isn't the only case of contention you might see with system objects related to temporary tables. We ran into a few different contention scenarios with tempdb:

So now you know where your contention is, what can you do about it?

Reducing Contention with In-Memory OLTP

SQL Server In-Memory OLTP (or Hekaton) is a new database engine that runs along side of the classic database engine you are used to using. You can query in-memory/memory-optimized objects just like their disk-based counterparts. The major difference is that memory-optimized objects operate using truely optimistic concurrency, allowing you extremely fast, lock-free access to your data. There is a lot to understand when you start out using memory-optimized objects, but it's fairly easy to get your feet wet with memory-optimized table variables.

Memory-Optimized Table Variables

Memory-optimized table variables are just standard table variables that use a user-defined memory-optimized table type. Creating the table type is easy, but in order to do so you will need a memory-optimized filegroup and container in your database.

Create the filegroup:

ALTER DATABASE ContentionTest
ADD FILEGROUP imoltp CONTAINS MEMORY_OPTIMIZED_DATA;

Create the container:

ALTER DATABASE ContentionTest ADD FILE
    ( name='imoltp01', filename='c:\data\imoltp' )
TO FILEGROUP imoltp;

Create a schema and table type:

USE ContentionTest;
GO

CREATE SCHEMA MemoryOptimized;
GO

CREATE TYPE MemoryOptimized.IdTable AS TABLE  
(  
    Id INT,
    Col1 NVARCHAR(128),
    PRIMARY KEY NONCLUSTERED (Id)  
) WITH (MEMORY_OPTIMIZED=ON);  -- <== The magic happens here.
GO

I created a schema called 'MemoryOptimized' to create my table type under. How you organize your objects is your business, but as you start using memory-optimized objects, I highly recommend placing these objects in their own schema just for the sake of clarity.

Now that we have our new table type created, we can use it in a procedure:

USE ContentionTest;
GO

CREATE PROCEDURE dbo.Test2
AS 
DECLARE @Test MemoryOptimized.IdTable;

INSERT INTO @Test
SELECT 1,'Test';

And that's it! Nothing too exciting around the implementation of memory-optimized table variables, but the results are pretty amazing. Let's run ostress again, using our new procedure, and see what happens to our instance:

ostress -Q"EXEC ContentionTest.dbo.Test2;" -n500 -r500 -S"localhost"

Now lets run our query to see what's happening on the instance:

USE master;
GO

SELECT  es.session_id,
        es.login_time,
        er.wait_type,
        er.wait_resource,
        er.command,
        DB_NAME(er.database_id) AS dbname
FROM    sys.dm_exec_requests AS er
        INNER JOIN sys.dm_exec_sessions AS es
            ON er.session_id = es.session_id
WHERE   es.is_user_process = 1;

You should now notice that it's hard to even catch an active session. All pagelatch contention is gone, you'll even notice that your ostress session finishes MUCH faster. So why did this work? It's simple: we moved our temporary table operation out of tempdb and into the user database, and on top of that we moved it into a memory-optimized object.

Final Thoughts

The results of this simple change can be dramatic, but before you get carried away and try to convert all of your temporary tables over to memory optimized table types there are a few things to keep in mind about in-memory OLTP, and tempdb contention:

The above list can be a little scary, but it's all worth it in the end. In-Memory OLTP is here to stay, and the performance gains you can see from it can be very impressive. Make sure you check out the resources below for more details on everything we discussed.

Resources