“Permanent” Temp Tables

Brent Ozar shows two ways of creating “permanent” temp tables:

The first one disappears when my session is over, but the latter two persist until the SQL Server is restarted.

Why would you ever do the latter two? Say you need to share data between sessions, or between different applications, or staging tables for a data warehouse, or just faster tables that live on local SSDs in a cluster (as opposed to slower shared storage), or you wanna build a really crappy caching tier.

Brent also talks about stored procedures.

Related Posts

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

Read More

Demonstrating Page Latch Waits

Brent Ozar shows us how to generate PAGELATCH_UP and PAGELATCH_EX waits: That’s what it looks like when your system is under heavy contention due to a lot of queries partying in table variables: in each second, each core on this system is spending 48 seconds waiting on PAGELATCH_UP. That’s awesome. What about temp tables instead, you ask? […]

Read More

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031