You upvoted questions at https://pollgab.com/room/brento, and today I’m at the harbor in Heimaey to cover ’em.
Questions we talked through:
- 00:00 Introductions
- 01:00 Zach: What happens when I change a VARCHAR(MAX) to VARCHAR(2500)?
- 04:26 Scott: What tools do you recommend for tracking down locks involved in blocking?
- 07:23 Pointing out the beluga whale sanctuary
- 07:58 Ice Horse Rider: Why aren’t you a Microsoft MVP?
- 10:20 Kirby: What does changing MAXDOP do?
- 12:44 Mr SQL Seeks: Should I upgrade to 2017 or 2019? What compat level do I use?
- 14:19 Farooq: How do I get notified when SQL Server needs additional CPUs?
- 15:45 Recap
On the MaxDOP explanation, SQL does not allocate work to CPU cores because the OS does it. It does not matter to which core, the kernel scheduler is smarter than just looking at CPU utilization (which is a tricky measurement anyway, you can see values over 100% on modern CPUs and the number is not wrong, it is about turbo-boost).
Interesting, do you have a resource for how the OS allocates which SQL Server query threads run on which cores?
I think Mark Rusinovich had a great blog post about it about 10 years ago, but it disappeared from the Microsoft site; the kernel scheduler was fine tuned since for more modern architectures (ex: AMD CCX blocks), but the basic principles did not change. I will return with a link as soon as I find it.
OK, cool, let me know when you find that – I think you might have misunderstood what he wrote, or it may have involved other (non-SQL-Server) applications.