Thanks for coming today. I apologize for baiting you in here with a picture of toilet paper. That's not even in the US - that's from my Iceland trip, before I flew back here to the US. Look, we all make mistakes. That's not the point of this post. You see, I've been reading this…
We must be getting pretty close to the official release of Microsoft SQL Server 2019. There are less than 80 days left in the year. (Interestingly, it's been almost 60 days since Release Candidate 1 shipped - that's unusually long for a delay between builds. Hmm.)
With the clock ticking down, now's a good time to take a 40-minute look at some of my favorite new features: deferred compilation for table variables, adaptive memory grants, adaptive joins, and air_quote_actual plans:
This week, I've been writing about how SQL Server 2019's bringing a few new features to mitigate parameter sniffing, but they're more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let's talk about another common cause of wildly varying durations for a single query: user-defined functions.
A while back, we talked you through a public whitepaper about how Microsoft was working on making user-defined functions go faster. Now that the preview of SQL Server 2019 is out, you can start getting your hands on Froid, the performance-boosting feature. Here's the documentation on it - let's see how it works. Using the…
Scalar functions and multi-statement table-valued functions are notorious performance killers. They hide in execution plans, their cost is under-estimated, the row estimates are way off, they cause queries to go single-threaded, I could go on and on.
Microsoft is bringing a fix in SQL Server 2019, and thanks to a newly published paper, we know more about how they're doing it. Folks from Microsoft and the Gray Systems Lab wrote Froid: Optimization of Imperative Programs in a Relational Database (17-page PDF, and slightly easier-to-digest 12-page PDF).
