Q&A

Harnessing SQL Server 2022's Power

Microsoft's Abdullah Mamun and Kendal Van Dyke break down how SQL's recent updates are giving database pros new ways to take control of their data.

• By Gladys Rama, Chris Paoli
• 10/02/2023

For those who are in Microsoft's SQL Server ecosystem, it's been a busy few weeks. The database management system recently went a transformative update in September, which added new features, included community tweaks and revised the platform to give database pros more control.

With so much that is new, there's nobody better to break it all down than two of Microsoft's own. Microsoft Senior Program Manager Kendal Van Dyke and Program Manager Abdullah Mamun walk us through some of the highlights of SQL's latest update, what you should be paying attention to and what's next for the platform.

And to dive deeper into all things SQL Server 2022, check out Van Dyke and Mamun this November during their Live!360 presentation: "SQL Server 2022 and the Wheel of Power."

Redmond: What are the features in SQL Server 2022 that make it stand out from its predecessors?
Van Dyke and Mamun: What stands out for us is that SQL 2022 is a BIG release with something new in almost every area of the product. The overarching themes and associated new features are:

• SQL Server 2022 is Azure-enabled for disaster recovery, analytics, and security.
• Managed Instance Link allows you to configure disaster recovery with seamless failover and failback between SQL Server 2022 on-premises and Azure SQL Managed Instance.
• Synapse Link enables near real time analytics over operational data with no ETL.
• Azure Active Directory authentication enables you to log into on premises SQL Servers using AAD credentials.
• Microsoft Purview integration allows you to manage access to on premises SQL Servers in the Azure portal.
• Built-in query intelligence reduces query tuning and gains you performance with no code changes.
• Intelligent Query Processing NextGen adds new capabilities to the IQP to gain consistent performance with no code changes.
• Improvements to Memory Grant Feedback calculations and persistence in Query Store so the engine doesn't have to re-learn the right memory grants after cache eviction and service restarts.
• Cardinality Estimation Feedback improves performance when actual row counts don't match up with initial estimates.
• Degree of Parallelism Feedback adjusts parallelism on a query-by-query basis to optimize CPU utilization.
• Parameter Sensitive Plan Optimization leverages multiple plans for the same query when a single plan isn't always the best choice for all scenarios.
• Query Store Hints allow DBAs coerce query performance without directly touching code.
• Query Store is now on by default for new databases, providing a history of key query metrics to help DBAs and developers understand how their queries are performing over time
• SQL Server 2022 has new innovations in security, scalability, and availability.
• Ledger for SQL Server provides tamper evidence capabilities to assist with compliance and auditing scenarios when you need to prove data hasn't been tampered with.
• There are 25+ new engine features alone in SQL Server 2022 that improve scalability without any code changes required.
• Contained Availability Groups automatically replicate logins and SQL Agent jobs between Availability Group replicas, simplifying management and improving failover reliability.
• Data virtualization and object storage through standard SQL interfaces.
• S3 object storage integration enables backups/restores to/from S3-compatible storage, plus the ability to query parquet files directly with T-SQL.
• Extending the T-SQL language for new developer scenarios.
• New capabilities for working with JSON, Time Series data and T-SQL functions for string, date and bit manipulation enable developers to manipulate data without having to bring it outside of the database.

What kinds of use cases do these new features open up for DBAs and developers?
We think DBAs will really appreciate the scalability and performance improvements in the engine that come just by upgrading to SQL Server 2022 and enabling more efficient use of existing compute and storage resources. Further, the new Intelligent Query Processing capabilities unlocked by compatibility level 160 mean DBAs will be able to spend less time reacting to queries that fall off the performance cliff and more time on proactive work. Plus, the Azure-enabled features connect the modern functionality of the Microsoft cloud to on-premises infrastructure, allowing DBAs to manage their entire SQL Server footprint through the Azure portal.

Developers will love the new T-SQL functions which allow them to manipulate data right in the database. The scenarios where you need to export data out, change it client-side, and push it back to the database because there isn't an easy way to do the same thing in T-SQL are becoming few and far between.

OK, "wheel of power" is a great name. Is this an official feature name in SQL Server 2022? What does it do?
The Wheel of Power refers to the five major categories of features in SQL Server 2022:

• Cloud Connected.
• Built-in Query Intelligence.
• Security, Scalability Availability.
• Data Virtualization & Object Storage.
• T-SQL Enhancements.

How "AI-ified" is this release of SQL Server? When it comes to generative AI capabilities, does Azure SQL have the advantage versus SQL Server?
Because ChatGPT was released shortly after SQL Server 2022 became generally available, the timing didn't quite line up for us to include AI in the core SQL Server engine.

The way we're currently exploring using Generative AI capabilities like ChatGPT to make the lives of DBAs and Developers easier is through integration with our tooling experiences. Using the GitHub Copilot that's available today in Azure Data Studio, a developer can receive suggestions (and even entire code snippets) as they're writing code. For example, as you're writing T-SQL code to join two tables, GIthub Copilot will suggest the join condition for columns in the open editor, other files in the workspace, and common syntax patterns. If you want to see this in action for yourself, check out our demo at the 51:35 mark from our keynote at last year's PASS Summit Day 1 Keynote.

Our tooling isn't tied to an on-premises release cycle, meaning you'll likely see new AI capabilities in developer and DBA experiences without having to wait for a future on-premises version of SQL Server that releases every few years. The same applies to Azure, where we introduce new features at a faster pace. The good news is that Azure SQL DB and on-premises SQL Server share the same core engine, so those future versions of SQL Server will benefit from the innovations that we bring to Azure first.

This topic for this year's SQL Server Live! Keynote, delivered by Buck Woody, is “The Age of AI for the Data Professional”. Buck also has a session about the concepts, tools, and applications of Generative AI for data professionals, so be sure to catch both his talks to learn more about how we're incorporating AI into SQL Server at Microsoft.

Is there a still-to-be-released feature in SQL Server that you're really looking forward to? And what's on your feature wishlist?
Query Store for secondary replicas enables the same Query Store functionality on secondary replica workloads that's available for primary replicas. This was one of the most anticipated (and requested) features from our customers, and while it shipped in SQL Server 2022, it's still considered a preview feature and not intended for production deployments. We're currently working to deploy this to Azure, and are committed to enabling the full functionality in a future SQL 2022 CU. I have an extensive background in query tuning, and I've experienced the challenge of not being able to easily see what's happening on secondary replicas, so I'm really looking forward to unlocking this capability for our customers!
Some other personal wishlist items for SQL Server include improved Availability Groups resiliency, leveraging machine learning for improving query execution efficiency, expanding our Intelligent Query Processing capabilities to include more scenarios and solving implicit conversion performance issues.

Of course, we're always interested in understanding what our customers need to make SQL Server better. If you've got an Idea, I encourage you to come talk to us at SQL Server Live! Or tell us about it at https://aka.ms/sqlfeedback.