Because it’s so easy to create & use temp tables, some applications grow to depend on ’em. If your workload creates, loads, and drops thousands of temp tables per second, you can hit a SQL Server bottleneck involving how SQL Server tracks where objects live in TempDB. It’s called latch contention, also known as PFS/GAM/SGAM…
In the last module, we finished up by showing how Microsoft used In-Memory OLTP (Hekaton) technology in SQL Server 2019 to let TempDB’s system tables work faster. That’s an easy way to reduce latch wait times, but it’s not the only way: you can directly adopt In-Memory OLTP yourself by changing your temp tables to…
This lab is left as an extra exercise for the reader if you’d like to do it later on your own. So far in the class, we’ve been using 2017 compatibility mode. If you’d like to see how 2019 performs differently, you can turn on all of the cutting edge features on your version of…
Now, it’s your turn: we’re going to run a workload in your lab while sp_BlitzFirst collects data and Query Store runs. You’ll query the plan cache history in DBAtools.dbo.BlitzCache and BlitzWho, find queries whose plans are changing, and gather parameters & plans to use that will help you design a better plan tomorrow. You don’t…
The phones are ringing, the users are screaming, and your manager is dropping four-letter words. The server’s performance has tanked, and you’re the one on call. Start up a workload, find the query who suddenly got a terrible plan, and do the needful. Part 1: Setting Up the Lab Part 2: Your Mission Part 3:…
