Getting Started with In-Memory OLTP: Reducing TempDB Contention
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:
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:
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.
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 ):
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:
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:
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:
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:
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
sysschobjsagain, 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.sysobjvaluestable. 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:
Create the container:
Create a schema and table type:
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:
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:
Now lets run our query to see what’s happening on the instance:
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.
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
defaultpool 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.
- Optimizing tempdb configuration with SQL Server 2012 Extended Events - Jonathan Kehayias
- Temporary Table Caching Explained - Paul White
- Bind a Database with Memory-Optimized Tables to a Resource Pool - MSDN
- Estimate Memory Requirements for Memory-Optimized Tables - MSDN
- In-Memory OLTP (In-Memory Optimization)