Blog

Microsoft has an official list of what’s new in 2022 overall, but here I’m specifically focusing on system objects that might be interesting to script developers like you and I, dear reader.

New stored procedure sp_get_table_card_est_and_avg_col_len – I assume the “card” refers to statistics and cardinality, not Hallmark. SQL Server has historically struggled with memory grants because it uses datatype size to budget memory grants, and bigger-than-necessary data types (like NVARCHAR(MAX)) have led to larger-than-necessary grants. It’d be cool if this proc was a down payment to mitigate that problem, but I have a sinking feeling it has to do with external data sources. I would tell you more about it, but when I run it, I get:

New view sys.dm_tran_orphaned_distributed_transactions – every now and then, I’ve run across Availability Groups replicas with problems due to orphaned distributed transactions consuming DTC resources or holding locks. This new undocumented DMV might be a down payment to resolve that problem. I don’t have an easy way to reproduce the problem quickly, so I can’t demo it.

New view sys.database_automatic_tuning_configurations – this one’s a little odd because Books Online tells me it’s been around since SQL Server 2017, but I don’t remember seeing it before, and it’s not in my 2019 test instances. Tells you if Force_Last_Good_Plan is on, and I would imagine that down the road, as more automatic tuning options might come out over the next several releases, this might have more info.

New Query Store DMV columns – now that Query Store is starting to work on read-only replicas, looks like they added plan_persist_plan_feedback.replica_group_id, plan_persist_query_hints.replica_group_id to support those goals. Plus plan_persist_plan_forcing_locations gets columns for timestamp and plan_forcing_flags.

New spinlock troubleshooting – sys.dm_os_workers gets columns for spinlock_wait_time_ms, spinlock_max_wait_time_ms, and spinlock_wait_count.

New stuff to support offloaded compression

This stuff needs its own section. RC0 introduced the ability to offload compression to Intel processors equipped with QuickAssist.

We get new sp_configure options for ‘hardware offload mode’ and ‘backup compression algorithm’. By default, these are off. To turn on offloaded compression, install the Intel QAT drivers, then do an alter:

Which returns:

After restarting the SQL Server, check this brand spankin’ new DMV:

And, uh, on my VM, it’s still not enabled:

Because you can enable it even on processors that don’t support it, which strikes me as kinda odd. I suppose you would want to make it part of your standard build, and then whenever it’s available, it’ll get used, assuming you call for offloaded backup compression in the right way.

New messages in RC0

In each release, I check sys.messages for new stuff. Some of this stuff gets added for the cloud, like Azure SQL DB or Managed Instances, so read these with a grain of salt. Here’s what’s new in RC0, new from the last CTP:

• 1136: The tempdb has reached its storage service limit. The storage usage of the tempdb on the current tier cannot exceed (%d) MBs.
• 5373: All the input parameters should be of the same type. Supported types are tinyint, smallint, int, bigint, decimal and numeric.
• 5374: WITH clause is not supported for locations with ‘%ls’ connector when specified FORMAT is ‘%ls’.
• 16722: Cannot change service objective for %ls to %ls as long-term retention is not supported yet on Hyperscale. Please disable long-term retention on the database and retry
• 17414: Retrieving the address of an exported function %.*ls in accelerator library %.*ls failed with error 0x%x.
• 17415: %.*ls component enumeration failed with zero component count.
• 17416: %.*ls component enumeration failed with mismatch in component count.
• 17417: %.*ls %.*ls not compatible with SQL Server.
• 17418: Detected %.*ls %.*ls.
• 17419: %.*ls hardware detected on the system.
• 17420: %.*ls hardware not found on the system.
• 17431: %.*ls initialization failed with error %d.
• 17432: %.*ls initialization succeeded.
• 17433: %.*ls session creation failed with error %d.
• 17434: %.*ls session sucessfully created.
• 17435: %.*ls will be used in hardware mode.
• 17436: This edition of SQL Server supports only software mode. %.*ls will be used in software mode.
• 17437: %.*ls will be used in software mode.
• 17438: %.*ls session alive check failed with error %d.
• 17439: %.*ls session tear down failed with error %d.
• 17440: %.*ls session close failed with error %d.
• 17441: This operation requires %.*ls libraries to be loaded.
• 19713: Statistics on virtual column are not avalable.
• 19714: Number of columns in PARTITION clause does not match number of partition columns in Delta schema.
• 21093: Only members of the sysadmin fixed server role or db_owner fixed database role or user with control db permission can perform this operation. Contact an administrator with sufficient permissions to perform this operation.
• 22786: Synapse workspace FQDN is not in the list of Outbound Firewall Rules on the server. Please add this to the list of Outbound Firewall Rules on your server and retry the operation.
• 22787: Change feed table group limit of %d groups exceeded
• 22788: Could not enable Change Feed for database ‘%s’. Change Feed can not be enabled on a DB with delayed durability set.
• 25755: Could not create live session target because live session targets are disabled.
• 31633: The length of the provided %ls exceeds the maximum allowed length of %u bytes.
• 31634: The %ls must contain a ‘%ls’ for use with managed identity.
• 31635: The %ls’s ‘%ls’ value must be a %ls for use with managed identity.
• 31636: Error retrieving the managed identity access token for the resource id ‘%ls’
• 33547: Enclave comparator cache failed to initialize during enclave load.
• 39057: The value provided for the ‘%.*ls’ parameter is too large.
• 39058: The parameter ‘%.*ls’ has a type that is not supported.
• 45770: Failed to move the database into elastic pool due to internal resource constraints. This may be a transient condition, please retry.
• 46552: Writing into an external table is disabled. See ‘https://go.microsoft.com/fwlink/?linkid=2201073’ for more information.
• 46553: Create External Table as Select is disabled. See sp_configure ‘allow polybase export’ option to enable.
• 46953: Pass through authorization using S3 temporary credentials is not supported. Please use S3 credentials to access storage.
• 47507: Adding memory optimized files to the database replicated to Azure SQL Managed Instance is not supported because its service tier does not support In-memory OLTP capabilities. Consider replicating database to managed instance service tier supporting In-memory OLTP capabilities.
• 47508: Adding multiple log files to the database replicated to Azure SQL Managed Instance is not supported because managed instance does not support multiple log files.
• 47509: Adding FileStream or FileTables to the database replicated to Azure SQL Managed Instance is not supported because managed instance does not support FileStream or FileTables.
• 47510: Adding multiple memory optimized files to the database replicated to Azure SQL Managed Instance is not supported because managed instance does not support multiple memory optimized files.

If any of those messages are interesting to you, feel free to leave a comment about it.

New database-scoped configuration options

These are all new since SQL Server 2019 – some were introduced in prior CTPs, but I’m mentioning them all here because there’s good stuff in here for query tuners:

• 25 – PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES
• 26 – DW_COMPATIBILITY_LEVEL
• 27 – EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS
• 28 – PARAMETER_SENSITIVE_PLAN_OPTIMIZATION
• 29 – ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY
• 31 – CE_FEEDBACK
• 33 – MEMORY_GRANT_FEEDBACK_PERSISTENCE
• 34 – MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT
• 35 – OPTIMIZED_PLAN_FORCING
• 37 – DOP_FEEDBACK
• 38 – LEDGER_DIGEST_STORAGE_ENDPOINT
• 39 – FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION

That last one’s particularly interesting to me because SQL Server 2019 originally shipped in a way that you could see runtime parameters in sys.dm_exec_query_statistics_xml, and then they turned it off around CU11-12 without documenting the changed behavior. That was a total bummer, because that feature was a lifesaver for troubleshooting parameter sniffing. I’m hoping we can get that back again.

SQL Server 2014’s end of support date is coming in less than two years: July 9, 2024. I know that sounds far-off right now, but consider the work you have to do between now and then:

• Pick a new version to migrate to
• Set up development SQL Servers for that new version
• Start developing & testing with it
• Get users to sign off that it’s working as expected
• Design your high availability & disaster recovery strategies for it (something I recommend folks revisit with each new release)
• Set up production/HA/DR SQL Servers
• Migrate over to the new servers

With that in mind, if you’re still running SQL Server 2014 today in production, now is the time to start moving your development servers over to the newer SQL Server version. I’ve got a post on which SQL Server version you should use, but if you’re just now starting a 2014 replacement plan, then you’re the kind of shop that doesn’t upgrade very often. (Which is cool! Change = risk.) In that case, I’d think about using SQL Server 2022 to get the longest lifespan you can out of your SQL Servers.

The first release candidate for Microsoft SQL Server 2022 is out, so let’s see what’s involved with installing it.

Start with a fresh, empty Windows VM for testing. Never install test/evaluation bits on your laptop or an existing SQL Server – it’s a recipe for trouble. Pre-release bits (and even release bits!) can cause side-by-side existence problems that you don’t want to have to waste time troubleshooting.

When the Windows VM is ready, download the installer here. When you run it, the Installation Center opens:

Click the Installation tab at left:

And for a standalone evaluation/development server, click the first line for a new standalone installation. (Most of the rest are just hyperlinks to go download other things that aren’t included in the SQL Server installer.)

The installer launches, and it’s time to choose your character:

Your choices:

• Evaluation Edition – just like Enterprise, but it times out after 180 days.
• Developer Edition – just like Enterprise, no time bomb, but isn’t allowed to be used for production purposes. Frankly, you’d be suicidal to use Evaluation Edition for production purposes too, since it has that time bomb. You wanna choose Developer because your evaluation period is probably going to extend beyond 180 days, and you don’t wanna have to worry about time bombs.
• Express Edition – only for tiny databases.

Choose your character – I’m going with Developer – and then hit next.

Let’s not pretend you’re going to read that, but I’ll point out one amusing section:

The $5 limit is because starting with SQL Server 2022, Microsoft is using Fiverr for development, testing, and support. The most they can get back from Fiverr for a refund is the $5 they spent on the contractor, so they gotta cap their losses. Otherwise, they’d go bankrupt from KB4538581 alone.

Accept the terms, and hit Next, and the installer will do some basic validation to make sure the VM is in a safe place:

In my case, the Windows Firewall warning is because Windows Firewall is enabled. If you click the Warning link, you’ll get instructions to set up an exclusion rule to let SQL Server traffic pass through the firewall. If you plan to test apps hitting this server, make a note that you’ll need to change the Windows Firewall settings later – but you can ignore this for now during setup, and circle back to it later.

When you hit Next, you get the first new screen:

As strange as it may seem, Microsoft is on a mission to get even more money out of you than just the SQL Server licensing costs. They want you to pay ongoing fees to use Azure services to manage your SQL Servers, regardless of where they live. If you want to burn money, buy candles.

Me personally, I can’t afford that and it’s irrelevant to my testing, so I’m going to uncheck that and click Next. Now, it’s time to pick features.

Install as few features as practical. Every box you check here has a performance cost. Many of them will consume CPU and memory even when they’re not being actively used.

The Features list in that screenshot looks like it’s cut off, like there’s more stuff below “Redistributable Features”. In RC0, there’s nothing else to see – there’s nothing below that. It’s a bug. What, you thought the product was ready? Buckle up, bud – it’s the first of many.

In that feature list, the only thing I’m going to check is Database Engine Services. I’m not a fan of SQL Server’s full text search, although I understand why people use it. Wanna check any other box on a production SQL Server? You should pause for a moment and reconsider the life choices that brought you here. Wanna check them just because it’s a dev box? Bad idea: if you make something available in development, then developers will come to rely on it, and then they’ll build code atop it, and need it in production. In terms of features, treat your dev servers like production.

After checking the Database Engine Services box, the Instance Root Directory option will light up:

You can leave that one on the C drive – that’s fine. We’ll set the data & backup locations in subsequent steps. Click Next.

You can install multiple instances of SQL Server on the same Windows VM. I’m actually going to choose Named Instance because I need this for something else I’m doing, but you should leave it on Default.

When you click Next, there will be a significant pause before the next screen appears:

If you’re installing just for the purposes of testing 2022, you can stick with these service account names. Check the box for Grant Perform Volume Maintenance Tasks – that’s Instant File Initialization, which helps restore databases much more quickly. That’s important when you’re doing testing on new versions.

If your application needs case sensitivity or a different collation, click the Collation tab:

If you’re testing to replace an existing server, connect to that server with SQL Server Management Studio. Right-click on the server name, click Properties, and the existing server’s collation will be listed on the properties screen:

Back on your SQL Server 2022 setup, choose the collation that matches your existing production server, and click Next.

On the Server Configuration tab, the first decision you have to make is whether to allow SQL authentication. Over time, trends have gone back & forth as to whether this was a good idea. We were trying to stamp it out for a while, but then Azure SQL DB came out and it didn’t support Windows auth, so Microsoft had to backtrack the idea that SQL authentication was bad. These days, Azure SQL DB supports Windows auth (although it’s often a pain in the rear), so “experts” are all over the place as to whether or not you should have it on.

Most of the apps I interact with require it, so I’m going to set Mixed Mode, and then set a strong password for the SA account.

You have to take action in the “Specify SQL Server administrators” box. If you don’t, nobody’s going to be an admin on the instance, and you’re going to be in a world of hurt when you go to actually use this server. At bare minimum, add yourself by clicking the “Add Current User” button, which will take a few seconds. For real production purposes, you’ll want to use an Active Directory group consisting of database administrators.

Click the Data Directories tab, and you’ve got some work to do:

You have to take action here too. If you leave the “Data root directory” to be the OS boot drive, then sooner or later someone’s going to create or restore a database, pour a lot of data into it, and run the OS boot drive out of space. Choose a different volume for the database files to live.

I’m using a server with a Z drive, so after I type in “Z:\” in that top box and hit Tab, here’s what my screen looks like:

For my purposes, that’s fine. Click the TempDB tab:

If you have standard file sizes that you use, feel free to change those. For me, 8MB file size and 64MB autogrowth is awfully small. Even on dev/test servers, I go with 1024MB initial size and autogrowth sizes for both data and log files, like this:

Then click the MaxDOP tab:

SQL Server’s gotten a lot better over the last couple of releases at setting the right Maximum Degree of Parallelism (MaxDOP or MAXDOP, depending on where in that screenshot you look – consistency is the hobgoblin of little minds.)

Click the Memory tab:

Click the Recommended radio button, or else SQL Server will drain your memory dry. Just as a side note, Max Server Memory doesn’t actually mean the maximum – there are things SQL Server will use memory above and beyond that maximum. We’re just setting a ceiling to make sure things don’t go absolutely crazy.

Don’t click the FILESTREAM tab. I know it’s in all caps, which means Microsoft is yelling at you, imploring you to use SQL Server as a really expensive file server. It’s a bad idea. Save your lunch money, and put files where they belong.

Click Next, and decision time is over – just click Install:

After installation finishes, you can use SQL Server Management Studio to connect. You don’t have to download a new version – SSMS 19 isn’t required.