With the release of SQL Server 2022 for mainstream, we checked in with our DBAs to see what they are most excited about in the new version. We have all been waiting patiently for this release and are really happy to see it drop for mainstream support.
Parameter Sensitive Plan Optimization
Kat chose this as one of her top features. Parameter sniffing has been a “troublesome” issue to resolve in many customers’ systems due to the nature of how they are querying the database. This new feature inside Query Store will allow multiple cached plans for a single query that can have different data distributions based on the values provided at run time. One thing to note, unlike other features in Query Store, databases will need to have a compatibility level of 160 for the feature to be enabled.
Contained Availability Groups
Ken H. is glad to have this feature finally available in the SQL Server 2022 release. Keeping users, logins, permissions, SQL Agent jobs, etc. synchronized between replicas has been wrought with human error, that surfaces at the worst possible time – after a failover event.
We DBAs often intend to circle back when making a change or adding a replica, or attempt to circumvent by creating automated process(s) to synchronize the metadata. The reality is, at least in my case, something gets missed that is only discovered after a failover event.
Contained availability groups extend the concept of the group of databases being replicated to include relevant portions of the master and msdb databases. This is different from contained databases, which store the user information within the user database. In a contained availability group, users, logins, permissions, etc. are created at the availability group level, and they are automatically synchronized to all Replicas. Each contained availability group has its own master and msdb system databases.
Fixed Server Roles
Faryl O. is looking forward to the addition of additional built-in server roles, such as ##MS_LoginManager## and ##MS_ServerStateReader##. Especially when users have specific security requirements that require access to only certain “administrative” areas and not others, assigning permissions according to the principle of least privilege can be difficult without creating custom server roles or simply assigning them sysadmin privileges to avoid further hassle.
In SQL Server 2022, permissions typically reserved for administrative roles such as sysadmin and serveradmin can be divided up into new server roles and subsets of server roles so that users and application processes can only have the minimum amount of permissions they need.
Resumable Add Table Constraints
Ken D. is glad to see an addition to pause/resumable features becoming available and expanded. The ALTER TABLE ADD CONSTRAINT operations (restricted to PRIMARY KEY and UNIQUE KEY constraints currently) now have the ‘WITH RESUMABLE=ON’ option.
On very large tables these sometimes time-consuming, and resource-intensive operations can now be paused to allow for other maintenance activities to occur, or if showing a negative impact on a running system, then resumed from the point where it was paused. No need to start from the beginning.
The ability to resume execution from point of interruption includes if the commands were to fail or if a system failover occurs. The ‘ONLINE = ON’ option and ‘RESUMABLE = ON’ options must be specified at first execution to make the process resumable so planning ahead for what might be ‘unplanned’ situations is needed.
SQL Server continues to strengthen the ability to perform necessary DBA maintenance tasks such as this and online index creation/rebuild to keep systems performing well and meet customer needs while lessening the impact on the availability of large databases.
Data Virtualization
Mike M. is excited about the opportunities that may come from enhanced data virtualization in the SQL Server 2022 release. With this, we will be able to query data sets and troubleshoot data/analytics issues without the increased overhead of having to connect separately or import various tables and objects to identify problem areas. DBA maintenance will be minimized to a smaller number of environments and T-SQL usage will become more efficient and streamlined even when dealing with various source data locations. The inclusion of external tables as a select is a particularly interesting feature and I am excited to see how it is leveraged to perform small ETL/ELT operations to meet user demands.
Management Features
Lina W. is excited about some of the new management features which will make overall database management easier and more efficient. For example, SQL will now calculate and recommend settings for Max Memory during installation. This will eliminate the need to change those settings later, which many people forget about or don’t even realize needs to be set and will cut down on memory growing out of control on your SQL Server. Another new exciting management feature involves the way we shrink data files and log files. By using the new option WAIT_AT_LOW_PRIORITY, new queries will no longer be blocked by the shrink operation, so you can shrink and still perform normal business operations.
Query Store
Christina T. is looking forward to the enhancements that have been made to the Query Store. In previous editions of SQL Server, Query Store was available but was disabled by default. This SQL Server 2022 release has revamped the query intelligence of Query Store and shifted this as one of the many features that are now enabled by default. Additional improvements support Query Store being available on read-only AlwaysOn replicas to better optimize performance on secondary workloads. We can also use Query Store Hints to mold query plans without making any additional changes to the application code.
New T-SQL Functions
A developer at heart, Rebecca is looking forward to some of the new T-SQL functions now available such as DATETRUNC(), GREATEST(), and LEAST().
The new DATETRUNC() function makes it much easier to return a specific date part instead of having to spend time remembering how to manipulate dates. The data type that is returned is dynamic depending on the type of date used. Meaning, if you give it a date that is datetime2, the returned value will also be a datetime2.
The GREATEST() function will return the max value from a comma-delimited list of expressions. Those expressions can be a combination of different arguments such as variables, column names, functions including aggregate functions, scalar subqueries, and constants. Expressions can contain different operators including bitwise, string, and arithmetic. If you list a mixture of different comparable data types, it will return a value using the data type with the highest precedence. If you’re not familiar with data type precedence, you can read more about it here.
What can’t you compare? You can’t compare using larger data types that exceed 8000 bytes such as varchar(max), nvarchar(max), varbinary. However, if they’re 8000 bytes or less, you can compare them! You also can’t compare arguments with data types such as xml, text, ntext, image, and so on.
Last but not least, is the LEAST() function which is just like the GREATEST() function but returns the min value from a comma-delimited list of expressions instead of the max value and has the same constraints as well.
Ledger
In a day and time where data integrity is paramount, Josh is looking forward to the added protection that Ledger will offer.
Ledger can help provide evidence that your data has not been tampered with to business partners, auditors, or others who need to know the integrity of your data by utilizing blockchain technology. Ledger utilizes updateable ledger tables or append-only ledger tables.
Updateable ledger tables allow you to insert, update and delete rows, but each of those changes is tracked in a history table. Append-only ledger tables only allow inserts.
Regardless of which type of table is used, Ledger then utilizes SHA-256 hashing through a Merkle tree data structure to create a block called the database digest. It represents the state of all Ledger tables in the database at the time of its creation and is stored outside of the system (preferably on tamper-proof storage).
While Ledger cannot prevent attacks, it can guarantee that, if data has been tampered with, it will be detected when the ledger tables are validated. During the verification process, Ledger will report all of the inconsistencies that were detected.
Need More Info?
Check out our other SQL Server blog posts. We provide expert-level SQL support and remote DBA services. Get in touch, or find out more here.