Press "Enter" to skip to content

Category: Temp Tables

Limitations with Memory-Optimized TempDB Metadata

Milos Radivojevic takes us through a few limitations in memory-optimized TempDB metadata tables in SQL Server 2019:

When we are about to enable a new feature, one of the first things we have to check is whether enabling this feature will break the existing code. Enabling this feature could bring two breaking changes: one is related to columnstore indexes, the other to transactions with memory-optimized tables.

I don’t think these limitations are that game-breaking, but if you’re regularly loading large tables in tempdb and using columnstore indexes on them, you might be in for a nasty surprise.

Comments closed

Memory-Optimized Tempdb Metadata

Niko Neugebauer takes a detailed look at an exciting 2019 feature:

Anyone who has ever seen/done tuning bigger Hardware would instantly be interested, since the CPU is clearly going 100% during the processing, showing APPARENT better focus on the process and hopefully better performance. Since the granularity of the Task Manager for both cases is the same, you can easily notice that overall spent significantly less time churning the same workload and that is very true – we have spent just 11.777 Seconds on the average!

The main benefit to this is an environment where you’re creating and destroying a lot of temp tables concurrently. If you are in that situation, you can realize significant performance improvements. But Niko does have a warning at the end.

Comments closed

Testing Memory-Optimized tempdb

Erin Stellato wants to see how big a benefit using In-Memory OLTP for tempdb metadata objects really is:

I have SQL Server 2019 CTP 3.2 installed on my virtual machine, which has 8GB of memory (max server memory set to 6 GB) and 4 vCPUs. I created four (4) tempdb data files, each sized to 1GB.

I restored a copy of WideWorldImporters and then created three stored procedures (definitions below). Each stored procedure accepts a date input, and pushes all rows from Sales.Order and Sales.OrderLines for that date into the temporary object. In Sales.usp_OrderInfoTV the object is a table variable, in Sales.usp_OrderInfoTT the object is a temporary table defined via SELECT … INTO with a nonclustered added afterwards, and in Sales.usp_OrderInfoTTALT the object is a pre-defined temporary table which is then altered to have an additional column. After the data is added to the temporary object, there is a SELECT statement against the object that joins to the Sales.Customers table.

This isn’t a be-all, end-all performance test, but it does give us an idea of what can work better with memory-optimized tempdb metadata.

Comments closed

Constructing Virtual Tables with VALUES

Kenneth Fisher shows how to use the VALUES clause to construct a virtual table:

This has come up a few times recently, I find it rather fascinating and I can never seem to remember how to do it properly .. so in other words, it’s a perfect subject for a blog post.

Basically, you can use VALUES to create a table within a query. I’ve seen it done in a number of places. Mostly when demoing something and you want some data, but don’t want to actually create a table. I’ve also seen it used to create a numbers table, create test data, etc. Really, any case where you want a short list of values but don’t want to create an actual (or even temp) table to store them.

Click through for examples on how to construct and use this virtual table as a quick replacement for creating a temporary table or table variable.

Comments closed

Memory-Optimized TempDB Metadata Tables

Ned Otter shows how to configure SQL Server to use memory-optimized metadata tables in TempDB:

Like other changes to TempDB, in order for the new memory-optimization to take effect a restart of the SQL Server service is required. Once the service is restarted, system tables in TempDB are now memory-optimized (it should be that way for RTM, but in CTP 3.0, it could be the case that not all system tables have been converted to Hekaton). You can reverse this setting with the following command, and again restarting the SQL Server service:

There are a couple of important notes that Ned gives us around accessing these metadata tables in scripts.

Comments closed

Using Temp Stored Procedures

Bert Wagner shows how you can perform testing with temporary stored procedures:

A while back I learned that it’s possible to create temporary stored procedures in SQL Server.

I never put that knowledge into practice however because I struggled to think of a good use case for when a temporary stored procedure would be preferable to a permanent stored procedure.

Not long ago I encountered a scenario where using a temporary stored procedure was the perfect solution to my problem.

Those scenarios are rare but Bert did hit one of them.

Comments closed

Alleviating tempdb Contention

Pam Lahoud has some advice for those with tempdb-heavy workloads:

TL;DR – Update to the latest CU, create multiple tempdb files, if you’re on SQL 2014 or earlier enable TF 1117 and 1118, if you’re on SQL 2016 enable TF 3427.

And now it’s time for everyone’s favorite SQL Server topic – tempdb! In this article, I’d like to cover some recent changes that you may not be aware of that can help alleviate some common performance issues for systems that have a very heavy tempdb workload. We’re going to cover three different scenarios here:

  1. Object allocation contention

  2. Metadata contention

  3. Auditing overhead (even if you don’t use auditing)

There’s some good information in here so don’t just say tl;dr.

Comments closed

Forwarded Records Without User Table Heaps

Erik Darling unravels a conundrum:

When people think about Heaps and the problems they can cause, they don’t often think of temp tables and table variables as Heaps.

Of course, without a clustered index, any table is a Heap.

This isn’t an argument for or against indexing temp tables, but while working with a client we came across something strange!

sp_BlitzFirst was reporting hundreds of thousands of Forwarded Records, but there were no Heaps in user databases.

When we dug in closer to what queries were doing, we found lots of places where temp tables had an insert/update pattern.

Click through for a demonstration and an explanation of why this can be trouble.

Comments closed