Releases of on-premises SQL Server arrived quite rapidly for a few years with seven releases during the years spanning 2008 and 2019. While it has technically been less than two years since SQL Server 2019 reached general availability in November 2019, it feels like ages since we have heard any news about what’s coming next. At least for me, time has moved slowly over the past year due to the pandemic, so maybe it's not been so long since 2019 was released.
Many DBAs are probably fine with waiting a bit and would like a break from upgrading so frequently. I’ve heard lots of “we are skipping version X and upgrading straight to version Y” from customers over the years. Of course, many also have wish lists of features they would like to see to make their SQL Servers run better or provide more value.
Over the past five years or so, I’ve noticed that Microsoft is really listening to customers about where the issues are and functionality we’d like to see. One example is tempdb configuration to avoid contention. Starting with 2016, the installation automatically sets up multiple files, and you can specify size and autogrowth settings during installation. The startup flags (1117 and 1118) recommended for tempdb are no longer needed as that functionality is now baked in. There’s also a new recommended min and max memory configuration during installation. While it is easy enough to configure tempdb and memory after installation, there are a lot of shops that don’t have SQL Server expertise in-house so why not configure these settings according to best practices up front?
In my case, I am most interested in Microsoft’s investment in Intelligent Query Processing. I’m excited about the possibility of queries running faster with minimal or no code changes.
Here are a few of items on my wish list:
- Expand Adaptive Joins to Row Mode. The Adaptive Joins feature means that the cached plan can switch between Hash Join and Nested Loops Joins depending on the number of rows. Right now, this feature requires that a columnstore index be part of the query, but I hope that someday this functionality will be expanded to Row Mode and that requirement will be eliminated.
- Chip away at the restrictions on Scalar UDF Inlining. This feature inlines the code from some UDFs so that it looks like the code is just part of the query, drastically improving performance in some cases. There is a long list of restrictions that keep a UDF from being inlined. I would like to see Microsoft work on some of these. What a game changer this would be!
- Add Batch Mode on Rowstore to Standard Edition. Batch Mode is a part of columnstore to improve the performance of large analytic queries by working on batches of rows. Batch Mode can now work on queries without a columnstore index. This is especially exciting for some of the window functions where scaling is a problem – window aggregates, percentile_cont, percentile_disc, percent_rank, and cume_dist. I’m happy with how this works, but I would like to see more shops be able to take advantage of it.
- Materialize common table expressions. I love how readable Common Table Expressions (CTEs) make a complex query, but the performance is not always great. If the query uses the CTE in multiple places, the tables in the CTE are touched multiple times. I would love to see the results of the CTE saved in memory so that the data could be reused in the query.
I can’t wait to see what the SQL Server teams at Microsoft are planning for SQL Server vNext, and, hopefully, some of my wish list items will be included.