Press "Enter" to skip to content

Category: Temp Tables

Queries Using tempdb: a Whodunit

Published 2020-05-05 by Kevin Feasel

Dave Bland shares a database detective story with us:

To find the culprit, we tracked down an old informant named sp_who2.  At first he had no comment, but we knew he had some information that could help us, so we kept after him.  Finally, he grew tired of us and gave us something just to make us go away. He said we needed to talk to his associate sysproccesses.  At first this guy was hard to find, but we located him sitting at a table.  He wasn’t really doing anything, just sitting there. So we approached him and sat down to see if we can get what we are looking for.  He also had no comment and referred us to his lawyer, sys.dm_exec_sessions.  After some searching, we were able to catch up with him at the DMV and he was not in a good mood.  He gave up some information, just not exactly what we are looking for.

Dave shows how we can figure out who created a specific temp table (a global temp table, in this case), the query that account used to create the temp table, and the time the temp table was created.

Comments closed

The Problems with Shrinking tempdb Data Files

Published 2020-04-29 by Kevin Feasel

Andy Mallon explains why you should never shrink tempdb data files:

I recently wrote about growing, shrinking, and removing tempdb files. In that article I explained that SQL Server won’t move a page that contains an internal worktable object, and thus trying to shrink tempdb files can be futile. Today, I’m going to explain how attempting to shrink tempdb files can actually be harmful.

Andy has good advice for tempdb here. Shrinking other database is generally bad but sometimes necessary; shrinking tempdb can lead to all kinds of problems.

Comments closed

Fun with tempdb

Published 2020-04-22 by Kevin Feasel

Andy Mallon walks us through setting up tempdb:

There are three problems I’ve got to fix. I need to (1) remove those two extra files, (2) grow the tempdb log file, and (3) even out the size of the data files (and shrink them a little to make room for the larger log file. We’re going to tackle these in the reverse order than I listed them–partially out of necessity, and partially because it’s going to be easier.

Click through to see how Andy sets up tempdb. This is a good way to set up tempdb.

Comments closed

Tempdb Issues You Might Have

Published 2020-04-22 by Kevin Feasel

Fabiano Amorim walks through a few of the tricky issues around tempdb:

One of many performance improvements that came with SQL Server 2014 is that it doesn’t flush dirty pages created in a minimally logged operation on tempdb. This gives you the benefit of having faster (compared to prior versions) inserts, but it caused another problem as those allocated pages may take a lot of time to be removed from the buffer pool data cache. Before discussing the problem, quickly look at the benefit and then understand some important concepts of flush dirty pages on tempdb.

Click through to learn more about the fix, and then a fix to the fix…which opened up a new avenue to fix. This kind of thing is why operating at scale is so difficult: the solution to one problem often becomes the avenue to a new problem.

Comments closed

Combining User-Defined Types and Temp Tables

Published 2020-04-03 by Kevin Feasel

Andy Levy tries to make cats and dogs live together:


This tripped me up a few weeks ago, but once I stopped and thought about for a moment it made total sense. I was trying to copy some data into a temp table and got an error I’d never encountered before.

Column, parameter, or variable #1: Cannot find data type MyStringType.

What’s that all about? Let’s find out.

I don’t think it spoils things to say that Andy’s story is a tragedy and not a comedy. But in fairness, the number of shops using user-defined types (as opposed to user-defined table types) is probably not enormous.

Comments closed

What Uses tempdb?

Published 2020-01-30 by Kevin Feasel

Jason Hall takes us through several SQL Server features which use tempdb behind the scenes:

Since SQL Server 2005, triggers use the version store, which is kept in tempdb. So, if you’re using triggers, they are implicitly using tempdb. Remember that triggers are set-based, and you’ll get version data for every affected row. For really big batches, this can turn out to be quite a bit of version data.

Click through for several more examples.

Comments closed

Limitations with Memory-Optimized TempDB Metadata

Published 2019-11-18 by Kevin Feasel

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

Published 2019-11-01 by Kevin Feasel

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

Published 2019-08-06 by Kevin Feasel

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

Published 2019-05-28 by Kevin Feasel

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