posts   talks   feed   about   search  

Getting Started with In-Memory OLTP: Reducing TempDB Contention

by Mark Wilkinson · June 21, 2017

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:

  • Contention on sysschobjs again, but on index 3. This index leads with the name of the temporary table and is fairly narrow so you can fit a lot of records on a single index page. Because of this, if you are running lots of concurrent procedures that create temporary tables with the same or similar names, it creates a hot spot on a single page, leading to more contention.
  • Temporary table auto-stats. Statistics objects for all tables (including temporary tables) are stored in the sys.sysobjvalues table. If you get enough auto-stats generations on temporary tables you can see contention here.

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:

  • If you are on SQL Server 2014, prior to SP1 CU4, you can run into issues where the XTP checkpoint thread dies and prevents log truncation on the database it belongs to. This can result in your tlogs growing out of control until you take the database offline.

  • You CANNOT remove a memory-optimized filegroup from a database without dropping the entire database.

  • It is highly recommended that you use resource governor to bind any databases that use memory-optimized objects to their own resource pool with memory limits configured. This can prevent a sudden growth of in-memory data from starving the default pool of memory.

  • While implementing memory-optimized table variables is fairly straight-forward, things can get more complicated when you start using memory-optimized tables. The biggest issue you’ll likely see is that you cannot use cross-database transactions if a memory-optimized table is involved. This was one of the main driving factors when we decided to go with memory-optimized table types over memory-optimized tables.

  • If you are on a version of SQL Server 2016 prior to SP1 CU2, or prior to 2016 RTM CU6, the demos above should work as described. If you have applied those updates the results will be less dramatic. Working with the SQLCAT team on this issue, we discovered a code path in SQL Server 2016 that introduced additional latches when dealing with cached temporary tables. This lead to an almost 100% increase in pagelatch wait types when using standard temporary tables (KB4013999).

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


Lets talk! Start a conversation about this post on Twitter