March 18, 2018 at 4:24 pm
I went to "Eddie Wuerch presents: Performance Tuning with Waits and Queues: a Full Day of Waits" last Friday at SQL Saturday in Chicago. Unfortunately one of his points i lost because i was trying to fiddle with my works VPN. Anyways, he stated that you should not be dropping your temporary tables at the end of your stored procedures. Instead you should let sql server's deferred thread drop them.I think because if multiple threads try to drop temp tables at once there can be contention on the GAM/SGAM/PFS (only one thread at a time can change those records. Which will cause more PATCHLATCH type of waits.
From what i've read even if you drop temp table in your stored proc sql server won't immediately clean it up (because it tries to cache temp tables). So it seems like it doesn't matter. I've emailed him and unfortunately don't have his scripts or slides. I'm apparently have something mixed up here. Would anyone mind clarifying?
thanks...
March 18, 2018 at 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
Eddie Wuerch
MCM: SQL
March 18, 2018 at 8:02 pm
Eddie Wuerch - Sunday, March 18, 2018 7:03 PMCheck 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
Thanks bunch Eddie. Best person to reply would be you of course :). I'm going to go over the slides again and play with the scripts and see if i have any more questions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply