Eddie Wuerch - Sunday, March 18, 2018 7:03 PM

Check your email. The links recently went out  🙂

You're correct about the latch contention caused by heavy create/drop #temp table traffic - specifically, adding the first row to those tables, which causes the initial mixed-page allocation from the data file and the "PFS caravan" of threads trying to flip a bit on a PFS page and allocate a page from a data file to the table. This is the problem generally addressed by adding files to spread out the work.

Once enough files are in place to address the PFS caravans, the next barrier is hotspotting the system tables that record table, index, and column metadata. Creating a table inserts records into several small system tables. Indexes are recorded in one place, the list of columns in another, and so on. Rapidly creating and dropping lots of #temp tables on several threads causes latch contention for the few pages in those system tables. The only way to reduce that contention is to reduce the concurrency of #table (and @table) create and drop requests.

-Eddie