Office Hours: Extended Edition

Y'all posted so many good questions at https://pollgab.com/room/brento that I went even longer today. Here's what we covered:

00:00 Start
02:17 Tanchenglai: Hi Brent, recently we always ran into thread pool issue. I checked Performance Dashboard and found that CPU spiked over 80% occasionally. I suspect apps can't connect after CPU over 80%.
03:55 Janis : I'm facing a puzzling issue with a simple join query, warns excessive 1/2 GB memory grant when joining the final table. I wonder where to dig deeper for a solution.
05:02 Novice Consultant: Hey Brent! How do you handle those clients who said they don't know the how-to when you suggest some non-SQL Server solution
08:03 Eli: Hi Brent, hope you're well. In your training, you make use of CTEs, but not correlated subqueries. Reusability aside, are there other reasons to prefer CTEs over correlated subqueries? Also, does subquery optimization behave differently if nested deeply?
08:53 Sonakshi Sinha: Is it beneficial to periodically delete the auto generated stats and let SQL re-generate them in hopes of culling the stats that are no longer used?
09:44 FroztDbz: Should an sql server service account password have expiry or can it be added on exemption list. Also how should I justify to them that this should be added to exemption list since it will create unnecessary downtime when not tracked properly
10:28 Dimez: In DBD do you play killer more or survivor more and who do you main as killer/survivor?
11:45 i_write_tsql_in_lower_case: Hi Brent, with this writing SQL Server 2022 has 6 monthly CUs. What's wrong with SQL Server 2022?
12:23 Chris: Have you ever used any tools that can assist in comparing execution plans that is better than the built in one for SSMS?
13:29 DBA with Anxiety: Hi Brent!. Thanks For Response My Last Question, Appreciate it very much. How to overcome FOMO in the world of databases
15:19 Dansa: We have a newly created table with a daily-partition aligned clustered column-store index. The partition function has 4800 partitions. Attempting to drop the empty table did not succeed after 5 hours.
16:36 Aamir Khan: What is your opinion of Microsoft SQL Server slow performance troubleshooting documentation?
19:01 Vangelis Protopappas: What are your pros/cons of MSTeams vs Slack?
21:06 Vishnu: What tools (external to source control) do you like to use when comparing two TSQL scripts for differences?
21:29 Dimitris Horn: We have many power users that can setup new / modify existing SQL agent jobs whenever they feel like it. Do you have any tips for how to track all the SQL Agent Job changes?
24:50 Sonakshi Sinha: What are your thoughts about using schemas to segregate multi tenant tables within a single database (MyDB.Co1.Sales, MyDB.Co2.Sales, etc)? Is this hard on plan cache / query store?
25:35 Eli: Hi Brent - heard you got a new gaming PC! If you ever get tired of Dead by Daylight, have you ever considered Deep Rock Galactic?
26:50 Double Bad Ass: What is your favorite cloud data warehouse and why?
27:37 gserdijn: Hello Brent, I noticed something I've never seen before. Some databases had OLDEST_PAGE as Log Reuse Wait Description. What does that mean?
28:33 Eduardo: Prior to SQL 2022, which SQL version was the previous poorly adoption version?
29:56 Kerem Bürsin: Does SQL page/row compression reduce the need for db normalization?
30:32 Bernard Tai: What was your favorite DBA class / subject as a student?
33:11 pete: An SP with a 3 table join takes 30 seconds with the results into a #temp, the second part has a 3 table join using the result set and 2 of the original tables, but it takes 2+ minutes. The only thing that is way out of kilter is the estimates of rows to be operated on. help pls
34:52 Miles: Hi Brent, What metrics to measure and what threshold values to consider to determine how busy is my SQL Server? I want to check the load on the SQL Server. Thank you.
36:48 PretendsToKnowSQL: We have a few maintenance plans we would like to "copy" to another instance. Are there any easy ways to do this or is it better to just take the time to rebuild them on the other instance?
37:30 Jessica: I am currently crying in standard edition for indexed views. Have you ever seen issues wrapping an indexed view in a second view to add the noexpand hint on the first view? (create view View2 as Select * from ix_View1 with (NOEXPAND))
38:08 Ozan: Hi Brent, at least two times i heard you saying that you get up at 3 AM. But so far i didnt hear you telling us when you go to bed!? I'm curious because 3 AM is damn early. Best regards from Berlin
39:02 Miles: Hi Brent, We have 4TB vendor specific DB. We see a lot of open spids idle for over 8hrs. By killing long running head blockers, what kind of impact will it have on other AG replicas?
40:02 Eduardo: Do you know what the tipping point is in SSMS where it will crash from showing too many query plans from a stored proc that has cursors?