Temp Table Usage

Paul Randal discusses common temp table anti-patterns:

It’s quite common for there to be a latching bottleneck in tempdb that can be traced back to temporary table usage. If there are lots of concurrent connections running code that creates and drops temporary tables, access to the database’s allocation bitmaps in memory can become a significant bottleneck.

This is because only one thread at a time can be changing an allocation bitmap to mark pages (from the temp table) as allocated or deallocated, and so all the other threads have to wait, decreasing the workload throughput. Even though there has been a temporary table cache since SQL Server 2005, it’s not very large, and there are restrictions on when the temporary table can be cached (e.g. only when it’s less than 8MB in size).

This is great advice; read the whole post.

Related Posts

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 […]

Read More

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 […]

Read More

Categories

April 2016
MTWTFSS
« Mar May »
 123
45678910
11121314151617
18192021222324
252627282930