Part of the SQL Server 2022 blog series. 

We are excited to share that there are several Accelerated Database Recovery (ADR) enhancements in SQL Server 2022 that further improve the overall availability and scalability of the database, primarily around persistent version store (PVS) cleanup and management.

Overview of Accelerated Database Recovery (ADR) 

ADR improves database availability, especially in the presence of long running transactions, by redesigning the SQL database engine recovery process. ADR is introduced in SQL Server 2019 (15.x) and improved in SQL Server 2022 (16.x). 

ADR is also available for databases in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse SQL. ADR is enabled by default in SQL Database and SQL Managed Instance and cannot be disabled. 

The primary benefits of Accelerated Database Recovery (ADR) are

Fast and consistent database recovery 

With ADR, long running transactions do not impact the overall recovery time, enabling fast and consistent database recovery irrespective of the number of active transactions in the system or their sizes. 

Instantaneous transaction rollback 

With ADR, transaction rollback is instantaneous, irrespective of the time that the transaction has been active or the number of updates that has performed. 

Aggressive log truncation 

With ADR, the transaction log is aggressively truncated, even in the presence of active long running transactions, which prevents it from growing out of control. 

Note: For more details about ADR, please visit this page: and this video for a high-level overview of ADR and its components.

SQL Server 2022

Learn about the new features on security, platform, management, and more.

Read what's new

New ADR improvements in SQL Server 2022

Multi-threaded version cleanup

In SQL Server 2019 (15.x), the ADR cleanup process is single threaded within a SQL Server instance. Beginning with SQL Server 2022 (16.x), this process uses multi-threaded version cleanup (MTVC), that allows multiple databases under the same SQL Server instance to be cleaned in parallel.

MTVC is enabled by default in SQL Server 2022 and uses one thread per SQL instance. To adjust the number of threads for version cleanup, set ADR Cleaner Thread Count with sp_configure.

USE master;GO-- Enable show advanced option to see ADR Cleaner Thread CountEXEC sp_configure 'show advanced option', '1';-- List all advanced optionsRECONFIGURE;EXEC sp_configure; -- The following example sets the ADR Cleaner Thread Count to 4EXEC sp_configure 'ADR Cleaner Thread Count', '4';RECONFIGURE WITH OVERRIDE; -- Run RECONFIGURE to verify the number of threads allocated to ADR Version Cleaner.RECONFIGURE;EXEC sp_configure;

In the above example, if you configure the ADR Cleaner Count to be four on a sql instance with two databases, the ADR cleaner will allocate only one thread per database, leaving the remaining two threads idle.

Note: The maximum number of ADR Cleaner threads is capped at the number of cores used by the SQL Server instance. For example, if you are running SQL Server on an eight core machine, the maximum number of ADR cleaner threads that the engine can use will be eight, even if the value in the sp_configure is set to a greater value.

User transaction cleanup

This improvement allows user transactions to run cleanup on pages that could not be addressed by the regular cleanup process due to lock conflicts. This helps ensure that the ADR cleanup process works more efficiently.

Reducing memory footprint for PVS page tracker

This improvement tracks persisted version store (PVS) pages at the extent level, in order to reduce the memory footprint needed to maintain versioned pages.

Accelerated Data Recovery cleaner improvements

ADR cleaner has improved version cleanup efficiencies to improve how SQL Server tracks and records aborted versions of a page leading to improvements in memory and capacity.

Transaction-level persisted version store

This improvement allows ADR to clean up versions belonging to committed transactions independent of whether there are aborted transactions in the system. With this improvement PVS pages can be deallocated, even if the cleanup cannot complete a successful sweep to trim the aborted transaction map.

The result of this improvement is reduced PVS growth even if ADR cleanup is slow or fails.

New extended event

A new extended event, tx_mtvc2_sweep_stats, has been added for telemetry on the ADR PVS multi-threaded version cleaner.

Summary

In this blog post, we covered all the exciting ADR improvements that we are including with SQL Server 2022 that further improve the overall availability and scalability of your databases.

Stay tuned as we are currently working on further improvements of the multi-threaded version cleaner that will enable parallelizing version cleanup within databases.

Learn more

For more information, and to get started with SQL Server 2022, check out the following references: 

• Manage accelerated database recovery
• Troubleshoot accelerated database recovery
• SQL Server Accelerated Database Recovery

Read What's new in SQL Server 2022 for all the new features on security, platform, management, and more. 

The post Accelerated Database Recovery enhancements in SQL Server 2022  appeared first on Microsoft SQL Server Blog.

Microsoft Azure knows SQL Server best and will support your cloud migration at no cost for qualifying scenarios.

The SQL Server platform has been successfully driving transformative business results with Microsoft customers across industries for over 25 years, enabling breakthrough innovation through unique capabilities like industry-leading performance¹ and security built-in.

Microsoft's continuous investments in SQL Server enable more flexibility with options for companies looking to simply save on costs, or to really transform their current SQL Serverbased applications and data estate.

These options include:

• Azure SQL Managed Instance, a fully managed cloud database that is compatible with SQL Server back to version 2008.
• A serverless compute version that stops, starts, and scales with your workloads.
• Elastic pools for managing groups of databases with ease.
• Hyperscale, the flexible, high-performance service tier that scales to meet demandall unique to Azure.

Reasons for choosing Azure over other clouds for your SQL Server applications go beyond having access to more options to meet your business and technical needs. SQL Server running in Azure also enables higher savings than AWS with faster performance¹, lower downtime^2, and AI-based, automatic tuning that continuously maintains peak database performance. Azure SQL is based on the SQL Server database platform, the least vulnerable commercial database in the NIST National Vulnerability Database for over 10 years³.

With Microsoft you can also decide where you want to run your SQL Server databases in your own datacenter, the cloud of your choice, or hybrid and multicloud deployments with Azure Arcenabled SQL Managed Instance and the recently launched SQL Server 2022.

SQL Server 2022

The most Azure-enabled release yet, with continued performance, security, and availability innovation.

Learn more

If you're considering moving to the cloud, besides getting the most out of a cloud-based SQL Server, you can take the first step with Azure free of charge. I'm pleased to share that the newMicrosoft SQL + Apps Migration Factoryinitiative can support your low-friction migrations of SQL Server and Windows Serverbased applications at no cost.

The Microsoft SQL + Apps Migration Factory is a delivery model offered through the Unified/Customer Success resources that gathers skilled professionals, comprehensive processes, and tools to help you plan, execute, and migrate qualifying SQL Server workloads and associated application virtual machines (VMs) to Azure with near-zero code changes. In other words, it blends the technical components of cloud migration with the business and human componentsso critical to helping you achieve your goals.

With this offer, you can get specialized assistance to help choose the migration approach that suits your business, such as moving a single workload or having a migration in phases, as well as guided savings and application assessments. All with the main goal of having a seamless and efficient workload migration without business disruption. Once your environment is migrated, our team can also help ensure the SQL Server workload is properly secured and performs adequately.

Hundreds of customers have already signed up to leverage this Customer Success Factory delivery model to accelerate migrations of known low-friction scenarios that do not require refactoring. Typical use cases favored by customers have included out-of-support SQL Server and Windows Servers, dev-test environments, disaster recovery (DR) environments, and applications with a compelling business need to migrate to the cloud in the near term that do not require refactoring or rewriting. This approach also minimizes the risk of business disruption as the migration journey is jumpstarted with these low-friction scenarios and advancing your digital transformation priorities in a phased manner. We like to think of this approach as eating your cake one slice at a time and we can support slices as small or as big as you need. You can even come back for seconds and thirds at any time to advance your migration and modernization journey in an iterative and controlled manner.

So, if your SQL Server estate, including associated Windows Serverbased apps, has these or other scenarios that are ready to migrate in the near term without refactoring to optimized Azure SQL and Azure VM destinations, talk to your Microsoft account team about the Factory delivery model or apply now to get started. 

Learn more

• Azure SQL | Microsoft Azure
• Extended Security Updates for SQL Server and Windows Server 2008 and 2008 R2 | Microsoft
• Azure Database Migration Guides | Microsoft Learn

¹Microsoft Azure SQL Managed Instance benchmark vs. AWS RDS for SQL Server using Azure Hybrid Benefit pricing, May 2022

²Service Level Agreements Summary | Microsoft Azure

³National Vulnerability Database (NVD), National Institute of Standards and Technology

⁴Subject to the limitations described in the full SQL + Apps Migration Factory program specifications here, and provided that the SQL Server workloads are low complexity with no code changes, Microsoft agrees to assess and migrate SQL Server databases and SQL Server-associated applications from your datacenter or AWS EC2 to Azure at no cost to customer. Migrations must be completed by June 30, 2023. 

The post Easily migrate SQL Server applications to the cloud with Azure appeared first on Microsoft SQL Server Blog.

Part of the SQL Server 2022 blog series.

Time series data is a set of values organized in the order in which they occur and arrive for processing. Unlike transactional data in SQL Server, which is not time-based and may be updated often, time series data is typically written once and rarely, if ever, updated.

Some examples of time series data include stock prices, telemetry from equipment sensors on a manufacturing floor, and performance metrics from SQL Server, such as CPU, memory, I/O, and network utilization.

Time series data is often used for historical comparisons, anomaly detection and alerting, predictive analysis, and reporting, where time is a meaningful axis for viewing or analyzing data.

Time series capabilities in SQL Server were introduced in Azure SQL Edge, Microsoft's version of SQL Server for the Internet of Things (IoT) which combines capabilities such as data streaming and time series with built-in machine learning and graph features.

SQL Server 2022

The most Azure-enabled release yet, with continued performance, security, and availability innovation.

Learn more

With SQL Server 2022 and Azure SQL, we've brought time series capabilities to the entire SQL Server family. Time series capabilities in SQL Server consist of enhancements to existing T-SQL functions for handling NULL values, plus two new functions that make working with temporal-based data easier than ever.

Create contiguous ranges with GENERATE_SERIES

When analyzing time series data, it's common to create a contiguous set of datetime values in evenly spaced intervals (for example, every second) within a specific range. One way to accomplish this is by creating a numbers table, also known as a tally table, which contains a set of consecutive numbers between a lower and upper bound. The numbers in the table can then be used in combination with the DATEADD function to create the range of datetime values.

Prior to SQL Server 2022, creating a numbers table usually involved some form of common table expressions, CROSS JOIN of system objects, looping, or some other creative T-SQL. These solutions are neither elegant nor efficient at scale, with additional complexity when the step between interval values is larger than 1.

The GENERATE_SERIES relational operator in SQL Server 2022 makes creating a numbers table simple by returning a single-column table of numbers between a start and stop value, with an optional parameter defining the number of values to increment/decrement between steps in the series:

GENERATE_SERIES (start, stop [, step ])

This example creates a series of numbers between 1 and 100 in steps of 5:

SELECT valueFROM GENERATE_SERIES(1, 100, 5);

Taking this concept one step further, the next example shows how GENERATE_SERIES is used with DATEADD to create a set of values between 1:00 PM and 2:00 PM in 1-minute intervals:

SELECT DATEADD(minute, s.value, 'Dec 10, 2022 1:00 PM') AS [Interval]FROM GENERATE_SERIES(0, 59, 1) AS s;

If the step argument is omitted, a default value of 1 is used when computing interval values. GENEATE_SERIES also works with decimal values, with a requirement that the start, stop, and step arguments must all be the same data type. If start is greater than stop and the step is a negative value, then the resulting series will be a decrementing set of values. If start is greater than stop and the step is positive, an empty table will be returned.

Finally, GENERATE_SERIES requires a compatibility level of 160 or higher.

Group data in intervals with DATE_BUCKET

Time series data is often grouped into fixed intervals, or buckets, for analytical purposes. For example, sensor measurements taken every minute may be averaged over 15-minute or 1-hour intervals. While GENERATE_SERIES and DATEADD are used to create the buckets, we need a way to determine which bucket/interval a measurement belongs to.

The DATE_BUCKET function returns the datetime value corresponding to the start of each datetime bucket for an arbitrary bucket size, with an optional parameter to define the origin from which to calculate each bucket. If no origin is provided, the default value of Jan 1, 1900, will be used as the origin date:

DATE_BUCKET (datepart, number, date, origin)

The following example shows the buckets for Dec 10, 2022, for several date parts with a bucket size of 1 and an origin date of Jan 1, 2022:

DECLARE @date DATETIME = 'Dec 10, 2022 12:05 PM';DECLARE @origin DATETIME = 'Jan 1, 2022 12:00 AM';SELECT 'Now' AS [BucketName], @date AS [DateBucketValue]UNION ALLSELECT 'Year', DATE_BUCKET (YEAR, 1, @date, @origin)UNION ALLSELECT 'Quarter', DATE_BUCKET (QUARTER, 1, @date, @origin)UNION ALLSELECT 'Month', DATE_BUCKET (MONTH, 1, @date, @origin)UNION ALLSELECT 'Week', DATE_BUCKET (WEEK, 1, @date, @origin)UNION ALLSELECT 'Day', DATE_BUCKET (DAY, 1, @date, @origin)UNION ALLSELECT 'Hour', DATE_BUCKET (HOUR, 1, @date, @origin)UNION ALLSELECT 'Minutes', DATE_BUCKET (MINUTE, 1, @date, @origin)UNION ALLSELECT 'Seconds', DATE_BUCKET (SECOND, 1, @date, @origin)

Notice how the date bucket value for the Week date part is Dec 10, 2022, which is a Saturday. That's because the provided origin date (Jan 1, 2022) is also a Saturday. (Note the default origin date of Jan 1, 1900, is a Monday). Therefore, when working with the Week date part, if you want your Week bucket to begin on a Sunday then be sure to use a known origin that falls on a Sunday.

Where DATE_BUCKET becomes especially useful is for bucket sizes larger than 1, for example when grouping data in 5-minute or 15-minute buckets.

SELECT 'Now' AS [BucketName], GETDATE() AS [BucketDate]UNION ALLSELECT '5 Minute Buckets', DATE_BUCKET (MINUTE, 5, GETDATE())UNION ALLSELECT 'Quarter Hour', DATE_BUCKET (MINUTE, 15, GETDATE());

DATE_BUCKET provides an easy way to determine which time-based interval a timestamped measurement belongs to using any arbitrary-sized interval.

Gap analysis with FIRST_VALUE and LAST_VALUE

FIRST_VALUE and LAST_VALUE are not new functions to SQL Server 2022; what is new is how NULL values are handled. In previous versions of SQL Server, NULL values are preserved.

When working with time series data, it's possible to have gaps between measurements. Ideally, gaps are filled in with an imputed value. When using FIRST_VALUE and LAST_VALUE to compute the value corresponding to an interval, preserving NULL values isn't ideal.

In the following example, a series of sensor readings taken at 15-second intervals has some gaps:

If analyzing the data in 1-minute intervals (using DATE_BUCKET), the default value returned by FIRST_VALUE will include the null values:

SELECT [timestamp] , DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket] , SensorReading , FIRST_VALUE (SensorReading) OVER ( PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) ORDER BY [timestamp] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [Default (RESPECT NULLS)]FROM MachineTelemetryORDER BY [timestamp];

FIRST_VALUE and LAST_VALUE include new syntax (IGNORE NULLS or RESPECT NULLS) in SQL Server 2022 which allows you to decide how NULL values should be handled:

FIRST_VALUE ( [scalar_expression ] ) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

RESPECT NULLS is the default behavior and will include null values in the result when computing the first or last value within a partition. Specifying IGNORE NULLS will cause NULL values to be excluded when computing the first or last value over a partition.

SELECT [timestamp] , DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket] , SensorReading , FIRST_VALUE (SensorReading) IGNORE NULLS OVER ( PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) ORDER BY [timestamp] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [Default (RESPECT NULLS)]FROM MachineTelemetryORDER BY [timestamp];

The new options for IGNORE NULLS and RESPECT NULLS allow you to decide how null values should be treated when analyzing your data.

Learn more

Get started with SQL Server 2022 today. For more information and additional examples, visit the following resources:

Microsoft Learn

• GENERATE_SERIES
• DATE_BUCKET
• FIRST_VALUE
• LAST_VALUE

GitHub examples

• Time Series T-SQL functions in SQL Server 2022.

The post Working with time series data in SQL Server 2022 and Azure SQL appeared first on Microsoft SQL Server Blog.

Part of theSQL Server 2022 blog series.

Cardinality estimation (CE) is a process used by the query optimizer to estimate the number of rows returned by a query or sub-part of a query. These estimates rely on two underlying things: statistics about the data and a set of assumptionsknown as the model. The model determines how we interpret statistics and data coming from various sub expressions, and so, for any given set of statistics, different models will arrive at different cardinality estimates. 

SQL Server 2022

The most Azure-enabled release yet, with continued performance, security, and availability innovation.

Learn more

Until SQL Server 2022, CE could only have one model. The set of assumptions used was baked into the code of the server, and so whatever model was picked is what was used for all queries. However, we know that not all queries and workloads perform best under one single model. For some queries, the model we use works out well, but for others, a different model would perform better. With CE Feedback in SQL Server 2022, we can now tailor the model used to generate a query play to the specific query.

CE has always had three basic assumptions that comprise the model: independence (or partial independence), uniformity, and containment. These three assumptions determine how we interpret histograms, and they determine how we combine data during joins or in the presence of multiple predicates. In this blog, I will explain these model variants and what they mean in more detail.

Uniformity

Let's begin by discussing uniformity assumption. This assumption is used when interpreting data from the on-disk histogramsabstracted data about the columns being queried. We assume that all data within steps, or buckets, of a histogram is uniformly distributed at an average frequency for that bucket. Thus, when we query data, this allows us to determine the number of rows that satisfy the predicate. 

Now, CE Feedback modifies the uniformity assumption only in one special casethat of Row Goal queries. These queries look like TOP n, or Fast n, or IN. and there is a special optimization for row goal queries that relies on the independence assumption. Whenever we believe that a particular value occurs at a high enough frequency (based on our interpretation of the histogram using the independence assumption), we choose to do a quick scan of a few pages assuming that we will get enough qualifying rows very quickly. However, if the data is skewed, we may have falsely assumed more qualifying values than were actually present. This means we scan far more pages than expected to get the requisite number of rows.

CE Feedback can detect such scenarios and turn off the special row goal optimization. If it turns out that the query is indeed faster without this assumption, we keep this change by persisting it in the query store in the form of a query store hint, and the new optimization will be used for future executions of the query.

Independence

Consider a where clause with two predicates, combined with an AND. Something like City='Seattle" AND State='WA'. Under the model assumption of independence, we would take the selectivity of the individual predicates (City='Seattle', State='WA') and multiply those probabilities together. Under the model assumption of correlation, we would take the most selective predicate (City='Seattle') and use the selectivity of that predicate only to determine the selectivity of the conjunctive clause. There is a third model of partial correlation, in which we multiply the selectivity of the most selective predicate with a weakened selectivity (raised to a power less than 1, to make the selectivity closer to 1) of the successive predicates. 

CE always starts out with this last model of partial independence (referenced in other places as exponential backoff), but with CE Feedback, we can see if our estimates are too high, meaning the predicates are more independent, or too low, meaning that there is more correlation than expected, and adjust the model used for that query and those predicates accordingly for future executions. If this makes the plan or performance better, we persist this adjustment using a query store hint, and use it for future executions.

Containment

The model assumption of containment means that users query data that is actually in the table. Meaning, if there is a column = constant predicate in the table, we assume that the constant actually exists in the table, at the frequency appropriate for where it falls within the histogram. However, we also assume that there is a containment relationship between joins. Basically, we assume that users wouldn't join two tables together if they didn't think there would be matches. However, there are two ways of looking at the containment relationship between joins: Base containment, or Simple containment.

Base containment assumes that there is an inherent relationship between the tables participating in a join but doesn't make assumptions about the filters occurring on top of those tables before the join occurs. A good example might be a table of store sales and a table of store returns. We would assume that all things returned were also sold, but we would not assume that any filtering on the sales or returns tables makes containment more or less likelywe just assume containment at the base table level and scale the size of the estimated result up or down based on the filters in play.

Simple containment is a bit differentinstead of assuming some inherent relationship between the base tables, it assumes that the filters applied to those tables create a containment relationship. For example, querying for graduating seniors from a specific high school and joining that with a query for athletes in a given zip code. While there is some inherent relationship between the two tables a priori, the filters applied specifically limit and create a containment relationship. 

CE starts with the base containment model for all queries. However, if the estimates for the join are 'off' in some waythe incoming estimates are good, the outgoing estimates are badwe try the alternate containment model. When the query is executed again, we try out the other model, and if it is better, we persist it with a query store hint and use it for future executions.

Conclusion

In summary, CE requires a basic set of assumptions that are used to interpret and combine statistical data about histograms or sub-parts of a query. Those assumptions work well for some queries, and less well for others. In SQL Server 2022, we have introduced a method of CE Feedback which adjusts those assumptions in a per-query way, based on actual performance of the query over time. 

Learn more

• See what's new in SQL Server 2022.
• Try out the Microsoft Learn SQL Server 2022 module and the SQL Server 2022 workshop.

The post Cardinality Estimation Feedback in SQL Server 2022 appeared first on Microsoft SQL Server Blog.

Part of the SQL Server 2022 blog series.

In the face of changing market conditions and pressure to accelerate growth, the ability for customers to do more with less is critical. And while the cloud has long been the north star for realizing efficiencies and accelerating innovation, at Microsoft, we understand that these benefits also need to happen outside of the cloud. In 2021 we announced the general availability of Azure Arc-enabled SQL Managed Instance. Previously only available in Azure, Azure Arc-enabled SQL Managed Instance allows customers to build new cloud native applications on any infrastructure, in their on-premises environments and across other public clouds. Now, we are offering a way for customers to make the most of their legacy applications, with Azure Arc-enabled SQL Server.

Yesterday, we announced the general availability of SQL Server 2022, the latest milestone in the 30 year history of SQL Server. This newest edition of SQL Server delivers continued innovation with new hybrid and multicloud capabilities that allow customers to manage and protect their SQL environments in more ways than ever before, no migration required. Together with these innovations, we are also introducing a consumption-based cloud billing model to purchase SQL Server enabled by Azure Arc.

Bring Azure Innovation to SQL Servers Anywhere

Azure Arc is a bridge that extends the Azure platform to help customers build applications and services with the flexibility to run on-premises, at the edge, and in multicloud environments. With Azure Arc, customers can manage their entire SQL estate through a single pane of glass and delivers multilayered security and improved data governance through a single integrated solution.

When customers enable Azure Arc on SQL servers, they can unlock more robust functionalities:

Manage

Manage their entire SQL estate in the Azure portal and get a single, unified view into their entire SQL Server estate to better manage inventory and licenses. Leverage SQL Best Practice Analyzer to automatically evaluate SQL Server environments for performance, scalability, and business continuity, all with no additional cost.

Govern

Microsoft Purview is a family of data governance, risk, and compliance solutions that can help organizations govern, protect, and manage their environments. With Azure Arc, SQL Server customers will have Purview access policies readily available for those on-premises environments to centralize insights and governance across their entire data estate.

Secure

We also enabled Microsoft Defender for Cloud for SQL Server. Microsoft Defender enhances the security posture of a customer's SQL Server environments, no matter where they reside. Microsoft Defender continuously scans and proactively identifies any vulnerabilities or attacks that are happening within SQL Server environments. This multi-layered security is made possible through Azure Arc, so data is protected both at the powerful database engine level and by Azure security capabilities from the cloud. 

SQL Server 2022 customers will also be able to benefit from Single Sign-On with Azure Active Directory to secure identities. These are security capabilities that customers have asked for in their on-premises environments for a long time and we are excited to be able to bring them to our SQL server customers.

Cloud billing model for all SQL workloads*

We are excited to provide customers with a new consumption-based, cloud billing model that provides flexibility to innovate quickly and save on costs. With Pay-as-you-go billing on SQL Server, customers can benefit from:

• Flexibility to choose between consumption-based licensing or the perpetual SQL Server licenses.
• Improved cost efficiencies for both Standard Edition and Enterprise Edition. Eliminate the need to pay full upfront licenses and pay for only what is used, by the hour, which is ideal for spike workloads or ad-hoc usage.
• Support for multicloud and hybrid deployments with consistent billing for all a customer's entire SQL environment, no matter where they reside.

The PAYG model is enabled by Azure Arc, so connection of the SQL Server to Azure via Azure Arc is a prerequisite. For SQL Server 2022 customers, the Azure Arc connection is a default part of the SQL Server 2022 setup process and the PAYG billing is available now. For customers with prior versions of SQL Server (2014 editions and above), this billing model will be enabled in the Azure Portal (coming soon) once an Azure Arc connection is established.

Innovate anywhere with Azure Arc today

Azure Arc was built to bring Azure innovation anywhere. With this week's announcements, our SQL Server customers can unlock cloud-native features on-premises, while benefiting from a more secure and streamlined management experience for their SQL Server environments, no matter where they reside. For SQL Server 2022 customers, these benefits will come already integrated with SQL Server. But customers with SQL Server 2014 and above can unlock many of the same innovations with the help of Azure Arc.

With Azure Arc, SQL Server customers will be able to maximize the value of their investments with technology that allows them to do more with less.

Learn more

Learn about the general availability for SQL Server 2022: general availability for SQL Server 2022.

• More on the latest billing model and how enable Arc for SQL Server.
• View pricing for SQL Server 2022 and the new pay-as-go options.
• Read more about Azure Arc.

The post Manage, govern, and secure all your SQL Servers with new hybrid capabilities enabled by Azure Arc  appeared first on Microsoft SQL Server Blog.

Part of theSQL Server 2022 blog series.

Today, we announced the general availability of SQL Server 2022, the most Azure-enabled release of SQL Server yet, with continued innovation across performance, security, and availability¹. This marks the latest milestone in the more than 30-year history of SQL Server.

SQL Server 2022 is a core element of the Microsoft Intelligent Data Platform. The platform seamlessly integrates operational databases, analytics, and data governance. This enables customers to adapt in real-time, add layers of intelligence to their applications, unlock fast and predictive insights, and govern their datawherever it resides.

SQL Server 2022's connections to Azure², including Azure Synapse Link and Microsoft Purview make it easier for customers to drive deeper insights, predictions, and governance from their data at scale. Azure integration also includes managed disaster recovery (DR) to Azure SQL Managed Instance, along with near real-time analytics, allowing database administrators to manage their data estates with greater flexibility and minimal impact to the end user³.

Performance and scalability are automatically enhanced via built-in query intelligence. Security innovation, building on SQL Server's track record as being the least vulnerable database over the last 10 years, continues with Ledger for SQL Server, which uses blockchain to create a tamper-proof track record of time of all changes to the database.

Watch how one of our customers, Mediterranean Shipping Company, is already taking advantage of the new capabilities in SQL Server 2022.

Azure-enabled features

Link feature for Azure SQL Managed Instance: To ensure uptime, SQL Server 2022 is fully integrated with the new link feature in Azure SQL Managed Instance. With this new capability, you benefit from a PaaS environment applied to disaster recoveryallowing you to spend less time on setup and management even when compared to an IaaS environment. This works by using a built-in Distributed Availability Group (DAG) to replicate data to a previously deployed Azure SQL Managed Instance as a DR replica site. The instance is ready and waiting for whenever you need itno lengthy configuration or maintenance required. You can also use this link feature in read scale-out scenarios to offload heavy requests that might otherwise affect database performance. We are working on building out more capabilities to support online disaster recovery.

Azure Synapse Link for SQL: Previously, moving data from on-premises databases, like SQL Server, to Synapse required you to use extract, transform, and load (ETL). Configuring and running an ETL pipeline is time-consuming, and insights often lag behind what is happening at any moment. Azure Synapse Link for SQL Server 2022 provides automatic change feeds to capture the changes within SQL Server and feed them into Azure Synapse Analytics. Synapse Link provides near real-time analysis and hybrid transactional and analytical processing with minimal impact on operational systems. Once the data comes to Synapse, you can combine it with many different data sources, regardless of their size, scale, or format, and run powerful analytics over all of it using your choice of Azure Machine learning, Spark, or Power BI. Because the automated change feeds only push what is new or different, data transfer occurs much faster and allows for near real-time insights, all with minimal impact on the performance of the source database in SQL Server 2022.

"Synapse Link for SQL Server 2022 helps us to seamlessly replicate operational data in near real-time to be able to have more powerful analytics."Javier Villegas, IT Director for DBA and BI Service, Mediterranean Shipping Company.

Microsoft Purview integration: Microsoft Purview is a unified data governance and management service. We are excited to highlight that SQL Server is also integrated with Microsoft Purview for greater data discovery, allowing you to break down data silos. Through this integration you will be able to:

• Automatically scan your on-premises SQL Server for free to capture metadata.
• Classify your data using built-in and custom classifiers and Microsoft Information Protection sensitivity labels.
• Set up and control specific access rights to SQL Server.

Additional Azure-connected features: SQL Server 2022 has a number of additional Azure-enabled features. A simple connection to the Azure Arc agent, part of the default setup process for SQL Server 2022, enables additional capabilities, including:

• Single view of all SQL Servers deployed on-premises, in Azure and other clouds.
• Fully automated technical assessment for SQL Server at no additional cost, to help you optimize your database's performance, scalability, security, business continuity, and more.
• Protect your on-premises data using Microsoft Defender for Cloud.
• Secure identities with Single Sign-On and Azure Active Directory.
• Pay-as-you-go billing.

Continued innovation to the core SQL Server engine

Performance: Performance is critical. On the SQL Server Engineering team, our core engine feature principles are: do no harm, no app changes required. With SQL Server 2022, performance enhancements come without requiring code changes by the end user.

SQL Server continues to offer differentiated performance, with #1 OLTP performance, #1 Non-Clustered DW performance on 1TB, 3TB, 10TB, and 30TB according to the independent Transaction Processing Performance Council. In SQL Server 2022:

• With Query Store, we are adding support for read replicas and enabling query hints to improve performance and quickly mitigate issues without having to change the source T-SQL.
• With Intelligent Query Processing, we're expanding more scenarios based on common customer problems. For example, the "parameter sensitive plan" problem refers to a scenario where a single cached plan for a parameterized query is not optimal for all possible incoming parameter values. With SQL Server 2022's Parameter Sensitive Plan optimization feature, we automatically enable the generation of multiple active cached plans for a single parameterized statement. These cached execution plans will accommodate different data sizes based on the provided runtime parameter values.

"As a company with 24/7 availability requirements, we are looking forward to embracing all SQL Server 2022 features that can make database failover faster, such as Buffer Pool Parallel Scan, ParallelRedo and Accelerated Database Recovery(ADR) enhancements. On the development side, we expect to further utilize continuous improvements in the Intelligent Query Processing package. In an environment with a lot of servers and huge databases, even when you have people and resources to deal with performance issues, each feature that can improve performance or fix performance issues automatically or without touching the code is very valuable." Milos Radivojevic, Head of MSSQL Database Engineering at Entain.

Security: Over the past ten years, SQL Server has had few vulnerabilities. Building on this, the new Ledger for SQL Server feature creates a tamper-evidence track record of data modifications over time. This detects tampering by malicious actors and is beneficial for scenarios such as internal and external audits.

Availability: With Contained Availability Groups you can create an Always On availability group that manages its own metadata objects (users, logins, permissions) at the availability group level in addition to the instance level with contained availability groups. Additionally, it keeps multi-write environments running smoothly when you have users across multiple locations. With SQL Server 2022, we are automating the last-writer wins rule to ensure that when a conflict is detected, the most recent modification time will be chosen to be persisted on all replicas.

New pay-as-you-go SQL Server billing model, enabled by Azure Arc

Today, we are also excited to announce a new billing model that provides flexibility to innovate quickly and move as fast as you do.

Via a simple connection to Azure Arc, a default part of the SQL Server 2022 setup process, you now have access to a new cloud-enabled billing model for SQL Server, providing you with cost efficiency to pay only for what you use. Pay by the hour for consumption spikes and ad hoc usage without the need for upfront investment. Learn more in the announcement blog post.

Azure SQL migration offer

If you're ready to start your journey to the cloud, Microsoft can help. Today we are announcing a new offer, the SQL + Apps Migration Factory. This program can assess and migrate qualifying low complexity SQL Server applications and databases to Azure SQL at no cost to you.⁴ Talk to your Microsoft account team or apply now at aka.ms/SQLAppsMigrationFactory to get started. 

SQL Server IoT 2022 

We are also announcing SQL Server IoT 2022, which is designed for fixed function use cases and licensed through the OEM channel under special dedicated use rights. You can read more about SQL Server IoT 2022.  

Learn more and get started with SQL Server 2022 today

• Learn more about SQL Server 2022 at the PASS Data Community Summit, including Microsoft and community-delivered keynote and general sessions.
• Visit the SQL Server 2022 website.
• Get ready for SQL Server 2022 by reading the documentation and watching SQL Server 2022 Data Exposed video series.
• Try out the Microsoft Learn SQL Server 2022 module and the SQL Server 2022 workshop.
• Get started today by deploying in Azure.
• Let us know if you have any thoughts or feedback through our SQL Server feedback portal.

Learn more about Azure SQL Managed Instance

• Learn about the November 2022 featurewave and other announcements at the PASS Data Community Summit.  
• Visit the Azure SQL Managed Instance website. 

^[1] SQL Server 2022 free editions (Developer edition, Express edition) are available to download starting today. SQL Server 2022 paid editions (Enterprise edition, Standard edition) will be available in Volume Licensing (Enterprise Agreement, Enterprise Agreement Subscriptions) and MPSA starting today, which represents the majority of SQL Server customers. Customers purchasing via CSP, OEM, and SPLA can begin purchasing SQL Server 2022 in January 2023.

^[2] For all Azure-connected features of SQL Server 2022, customers can optionally enable these capabilities based on business requirements.

^[3] The bidirectional disaster recovery capability of the Link feature for Azure SQL Managed Instance is available in limited public preview.Sign upfor early access. General availability will occur at a future date.

^[4] Subject to the limitations described in the full SQL + Apps Migration Factory program specifications here, and provided that the SQL Server workloads are low complexity with no code changes, Microsoft agrees to assess and migrate SQL Server databases and SQL Server-associated applications from your datacenter or AWS EC2 to Azure at no cost to customer. Migrations must be completed by June 30, 2023. 

The post SQL Server 2022 is now generally available appeared first on Microsoft SQL Server Blog.

Part of the SQL Server 2022 blog series.

Memory grant feedback (MGF) is an existing feature in SQL Server, with two critical improvements available in SQL Server 2022: feedback persistence, and percentile grant feedback. These two features enhance the benefits of memory grant feedback as it already existed in SQL Serverallowing for less re-learning of appropriate grants and preventing fluctuating grant requirements from blocking the benefit of memory grant feedback.

Memory grant feedback

This existing feature aims to prevent costly spills or wasteful memory allocation of queries by remembering the memory usage of previous executions of the query and adjusting the grant based on previous data. This helps to prevent spills for underestimates, and to increase throughput of a workload by trimming overly large memory grants to a size that better fits a query.

Memory grant persistence

In prior versions of SQL Server, memory grant feedback data was stored only in the query plan cache.  Thus, whenever the plan was evicted from cache, or in the case of failover/server restart, the system would have to re-learn the memory grant feedback from scratch. It seems prudent to store relevant information in the query store. This way it could be retrieved at any time from disk, with no concern for performance degradation after cache eviction or server restart.  Because query store is on by default in SQL Server 2022, this feature is an obvious win for improving the overall effectiveness of memory grant feedback.

Percentile grant feedback

Prior to SQL Server 2022, memory grant feedback was only determining the current grant adjustment based on the single most recent execution of the query.  However, in some casesespecially those where there is a cached plan for a stored procedure in which different parameters cause vastly different result set sizes (and thus vastly different memory requirements)this can trigger a severe anti-pattern of alternating request sizes and always-wrong memory grant adjustments.  This pattern is shown in the image below:

In this example, the first query execution needs 800MB and is given 800MB. On the second execution, the query requires only 5MB but is given 800MB. MGF will realize that this was a massive over-grant and adjust the subsequent execution grant to 5MBbut we are back the original parameter for which 800MB is needed!  When MGF only looks at the single prior execution, this undesirable pattern can occur.  Before SQL Server 2022, MGF will detect this scenario and disable itselfnoticing that it is not helping to improve the workload. 

SQL Server 2022 introduces percentile granta way of looking at more than just the single prior execution of a query. With percentile grant, we can look back to the history of executions and adjust memory grants based on a larger set of data points. We always err toward providing more memory to avoid spills, as spills are typically substantially more impactful to the customer than hits to throughput from an oversize grant. Thus, in this parameter-sensitive scenario, we might end up with a pattern of grants and executions that looks more like the following:

In this example, you can see that the first execution required a very low memory grant, but the second execution required a much higher grant, which it was not given due to the first execution.  The third execution requires a smaller grant size, but percentile grant (blue line) gives it a grant higher than the last required grant, and the algorithm that we had previously (orange line) would grant only the amount needed in the prior execution.  Over time, the blue line fine-tunes itself to the upper limit of the memory grants required by the query, whereas the prior grant algorithm finds itself out of phase with the grant required. 

Taken together, these two requirements to memory grant feedback make the feature more reliable and more robust. By persisting the feedback, it becomes robust to failovers, restarts, and cache evictions.  By using the new percentile grant algorithm, we are able to respond much more effectively to queries with a widely vacillating grant requirementallowing the feature to continue to bring benefit to the customer even in pathologically bad scenarios.  These two improvements to memory grant feedback are just one of the many intelligent query processing improvements in SQL Server 2022.

Learn More

• Download the preview of SQL Server 2022.
• See what's new in SQL Server 2022.

The post Memory Grant Feedback: Persistence and Percentile Grant appeared first on Microsoft SQL Server Blog.

Part of the SQL Server 2022 blog series.

DOP inefficiencies are a constant challenge; Current DOP methods are inefficient

The degree of parallelism (DOP) with which a query is executed can greatly impact its performance. Any time a query is using parallelism, there is always the question of if it’s using the right amount of parallelism. Sometimes, if the degree of parallelism is too high, it can introduce inefficiencies into the query execution. If the degree of parallelism is too low, we may be missing out on some of the speed-up that parallelism can provide. Users can manually set a maximum degree of parallelism for a query or for a server using the MAXDOP setting or hint. However, it has been a constant challenge for users to manually determine and tweak the correct degree of parallelism for each query. At most, they would set the MAXDOP specifically when they notice a problemthey don't typically try to determine the optimal degree of parallelism for each query in their workload.

Degree of parallelism feedback: feature overview

In SQL Server 2022, we introduced a new feature called DOP feedback. This feature will look at any parallel query and determine if it might perform better with a lower degree of parallelism than currently being used. For example, perhaps 16 threads will perform better than 20 if there are a lot of waits on other threads. It will test out the new degree of parallelism and, either decide that this was a good change and keep the 16 threads, or it will revert to previous levels of parallelism and go back to 20 threads. If the new degree of parallelism is good, then this optimization is persisted inside the query store and will be applied appropriately to a query for future executions. 

DOP feedback never increases the degree of parallelism, at best, it will revert to a stable previous DOP, and it works incrementally, meaning instead of trying to drastically lower the degree of parallelism all at once, it will try a slightly lower degree of parallelism. Then if that’s good, it might try another slightly lower degree of parallelism. If the new, even lower degree of parallelism is good, it might try to reduce again down to the degree of parallelism of two, although it will not make a parallel plan become serial. If the new, lower DOP is not as good, we go back to the previous known good DOP and keep the query at that level.

Example

A query is compiled with a degree of parallelism of 32. This means that the query will split off 32 different threads to execute the query. If DOP feedback detects a fair amount of wait times between threads and CPU overhead, it will suggest a lower DOPsay, 20. On the next execution, the query will execute with a DOP of 20. If the performance is better over the next several executions, the DOP of 20 will be considered stabilized. However, DOP feedback may then determine that there are still too many waits and further attempt a DOP of 16. Again, several executions are used to verify the feedback. Then, perhaps, a DOP of 8 is tried. If after several executions the DOP 8 performance is not better, then the system will return to suggesting a DOP of 16 as the most recent, stable, and verified DOP. 

Simple setup and easy optimization

With this feature enabled, all of this is done without triggering query recompiles, and without user action.

DOP feedback for SQL 22 addresses a long-held challenge for our customers: finding the right degree of parallelism for each query without having to manually test and tweak each query for optimal performance. This is one of a handful of features in Intelligent Query processing that will provide customers with the best performance and a low-touch to no-touch experience. You can read more about other similar features in the SQL 2022 blog series.

Learn more

• Download the preview of SQL Server 2022.
• Learn how to configure the max degree of parallelism Server Configuration Option.
• See what's new in SQL Server 2022.

The post Intelligent Query Processing: degree of parallelism feedback appeared first on Microsoft SQL Server Blog.