Looking Forward to SQL Server vNext

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.

Kathi Kellenberger

Join the debate, and respond to the editorial on the forums

With both personal experience and the real world evidence to back it up, Grant Fritchey says, unequivocally, a well-implemented DevOps process helps the organization. But it's not always easy to bridge a siloed organization, and sometimes organizations adopt DevOps for the wrong reasons.

How to use a SQL random data generator to fill SQL Server tables with realistic test data, to shift left database unit testing, integration testing and performance testing so that it is performed during the early stages of database development.

How to detect database drift prior to running a database migration, so that you can be certain that a database hasn't been subject to any 'uncontrolled' changes that could affect the migration or result in untested changes being deployed to production.

Take the 2021 State of Database Monitoring survey and tell us about how you monitor your databases, instances and servers. By taking the survey, you’ll contribute to (and get early access to) the leading industry-wide report on database monitoring, and be entered to a prize draw for a $500 Amazon gift card.

In this article, Chandra Kudumula shows how to use...

During the first weeks of February, we asked recipients of our Data and AI Newsletters to participate in a survey...

I realised the other week, that despite a bunch of posts on indexes over the years, I’ve never written a blog post on Columnstore indexes....

System-versioned temporal tables were introduced in SQL Server 2016...

In the early days of Azure SQL Database (ne SQL Azure) we had size limits that were minuscule–I vaguely remember 5 and 10 GB...

Azure Synapse Analytics provides multiple query runtimes that you can use to query in-database or external data...

Knowledge of how your system behaves is vital to better control, maintain, and grow the system. While Azure provides all sorts of wonderful assistance within Azure SQL Database, you’re...

I'm executing a Powershell Script . When I started...

Question: I have an SQL Server Instance - currently configured with a Dynamic Port. I'd like to change the setting from a Dynamic port configuration to a Static port...

So I’ve been kicking the tires and trying to do more with the Secrets Management modules from Microsoft, now that they are out of pre-release status...

Click to learn more about author Ian Pitt. 2020 presented some of the most unexpected challenges as the pandemic pushed many organizations to advance their digital transformation at an...

In this post I want to share how to deploy from one source to multiple SQL Server database types using GitHub Actions.

If you read my T-SQL Tuesday post from this month, I mentioned that I’ve been using Azure Data Studio on daily basis.

If you "do" DevOps you likely have a basic set of CI/CD processes in place, but there's almost certainly room for improvement.

Redgate released the 2021 State of Database DevOps Report in February, and I wrote a short article talking about the key insights from the report. More recently, I decided...

Communication is at the heart of DevOps, but it can be difficult to achieve. Mike Cuppett explains how to improve DevOps communication clarity.

Click to learn more about author Brian Platz. Within the Data Management industry, it’s becoming clear that the old model of rounding up massive amounts of data, dumping it...

Total: 1 Average: 5Availability Groups are fantastic for High Availability/Disaster Recovery solutions, and I’m sure that fellow DBAs will agree with me. However, there will be times when we...

Organizations have many choices when it comes to databases. In this article, Robert Sheldon explains how to choose between SQL and NoSQL databases.

Cost threshold for parallelism and the max degree of parallelism are 2 important setting for your SQL Server. Find out how to set these and what they should be...

It’s time! Time to formally announce the release...

Pantsuit Most people see a lookup and think “add a covering index”, regardless of any further details. Then there they go, adding an index with 40 included columns to...

Last week we built a Manufacturing Yield dashboard that showed first and final pass yield numbers...

Let's keep the big data discussion going with Powe...

Total: 1 Average: 5The time intelligence functions...

This is going to be a very busy week for presentat...

Over the years I have written a lot about Power BI...

We recently kicked off a new 10-week course, which...

We moved to Iceland in January to work remotely, and there’s a lot about it that feels like we’re living in the future. It’s an easy transition for Americans....

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. The other day I saw an article on... The...

System-versioned temporal tables were introduced in SQL Server 2016. They provide information about data stored in the table at any point in time by storing an effective dated version...

Fairly quick one today, just talking about the QUOTENAME() function...

One of the most upvoted requests on feedback.azure.com is for Microsoft to add MAX/MIN as non-aggregate functions in SQL Server...

Total: 2 Average: 5 The TRIM function of SQL Server is designed to remove leading and trailing spaces from a character string. A leading space is a space that...