Memory-Optimized Table Types

Randolph West has a three-part series in which he looks at using memory-optimized table types and table valued parameters to increase application performance.  Part 1 introduces the concept:

In other words, for these short-lived temp tables, there’s not only an added benefit of no longer worrying about referring to tempdb..#table, but you also get a massive performance improvement as well.

Part 2 specifies the scale of performance improvements:

The test is very simple and makes use of a technique that one of my customers uses extensively: writing some rows to a temp table, so that another process can reuse those values as parameters, and perform an action, after which it destroys the temp table.

Unfortunately, the method my customer uses to populate a temp table, does not work on Azure SQL Database, because they explicitly refer to the temp tables with three-part naming (tempdb..#temptable), which is not permitted.

For the sake of the exercise, I will have a process that writes to a data structure and compare the times.

Part 3 repeats the test in Azure SQL Database:

I’m going to use the same WHILE loop again, but instead of a million runs, I’ll do 1000, 10,000 and 100,000, because I’m paying for this instance of Azure SQL Database (I picked a Premium P1, with 125 DTUs) and I’m a cheapskate. I doubt the 125 DTUs is even enough to run a million times for the fourth option.

Even in SQL Server 2014, this was a good use of In-Memory OLTP.  With the improvements in 2016, this becomes a viable option for a lot more workloads.

Related Posts

T-SQL Tuesday 117 Roundup

Steve Jones has the roundup for this month’s T-SQL Tuesday: The summary from my fourth T-SQL Tuesday hosting for #117. This time I was scrambling a bit, but since I’ve worked with a few customers in the last year that use MOT tables, I thought this might be a good topic. Either everyone is on vacation […]

Read More

Memory-Optimized Table Types

Rob Farley hates spelling “optimized” the best way: Let me start by saying that if you really want to get the most out of this feature, you will dive deep into questions like durability and natively-compiled stored procedures, which can really make your database fly if the conditions are right. Arguably, any process you’re doing […]

Read More

Categories

November 2016
MTWTFSS
« Oct Dec »
 123456
78910111213
14151617181920
21222324252627
282930