What’s New in SQL Server 2022

SQL Server, SQL Server 2022
53 Comments

Today at Microsoft Ignite, Microsoft started dropping new details on the next version of SQL Server, 2022. Here’s a 13-minute video from Bob Ward explaining and demoing some of the new features:

The new features include:

  • Failover back/forth from SQL Server 2022 and Azure SQL DB Managed Instances, including restoring versionless databases from Azure SQL DB Managed Instances down to on-premises SQL Server 2022
  • Azure Synapse Link integration to avoid big ETL jobs between SQL Server and Azure Synapse
  • SQL Server Ledger – blockchain immutable histories of tables
  • Parameter-sensitive plan optimization that caches multiple plans per stored procedure

There’s no release date yet, but you can apply for the early adoption program now.

Previous Post
5 Ways to Measure a Query
Next Post
Who’s Hiring in the Database Community? November 2021 Edition

53 Comments. Leave new

  • Thanks, Brent! Common wisdom in the past (rightly or wrongly) has said to hold off on adoption, even for Dev environments, for a few months post release. Or for the first service pack. Do you think that still holds true?

    Reply
  • @Daniel SQL Server 2017 and newer don’t have Service Packs (only Cumulative Updates), so that part of the old common wisdom will have to change.

    Reply
  • I really hope that with 2022 it will finally be possible to integrate system database into availability groups. It is really unnecessary effort to take care that settings, logins, agent jobs etc. are in sync on all instances.

    Reply
  • We are currently working on a hybrid DR from on-prem to Azure… Their new setup for creating the cluster is really great looking. We are planning on doing to a dedicated VM running SQL though, rather than a hosted SQL instance.

    Reply
  • And the million dollar question is: Will SQL 2022 allow restoring of a single table from a backup? 🙂

    Reply
  • Rolf,
    You can use Amamar third party solution to achieve this

    Reply
  • So, Brent, what happens to your parameter sniffing class? 🙂

    Reply
    • Given how quickly y’all adopt new versions of SQL Server, I’ll have to plan to discontinue it next week. 😉

      Reply
      • The real question should be will the new “Parameter-sensitive plan optimization that caches multiple plans per stored procedure” alleviate parameter sniffing problems or will it just create new and interesting parameter sniffing patterns to mystify users 🙂

        Reply
  • Bandar Almutairi
    November 2, 2021 10:52 pm

    Good stuff with parameters sensitive plan optimization and all but isn’t it about time that they change default isolation level.

    Reply
  • Mr David Tovee
    November 3, 2021 6:37 pm

    I’m trying to work out what the “parameters sensitive plan optimization” means?
    Is it the reverse of ‘Forced parameterization”?
    Does it use ranges?
    I guess we will find out in the fullness of time…

    Reply
  • Mr David Tovee
    November 3, 2021 8:44 pm

    OK, I rewatched the video as it seems like I missed something… Bob Ward didn’t explain how “built-in query intelligence” worked except said it decided if an index seek or scan was used and SQL can now cache multiple plans against the same stored procedure (I realise this is new).
    “Parameter sensitive plan optimization” sounds like the reverse of “forced parameterization” for store procedures 🙂
    If we used dynamic SQL in our stored procedures with hardcoded values wouldn’t we get similar results of multiple plans for the stored proc? (I know it will be on the SQL not the stored proc).
    Getting my flame retardant clothing ready…

    Reply
    • No no, you’re exactly right! It does seem like they’re building a plan per parameter, and I’m not sure how they’re going to reuse plans in this situation. I’m really excited to see how they avoid plan cache bloat.

      Reply
      • well with new blades with upwards of 1Tb of ram. Plan cache as long as remains in memory…but for etl workloads i can see 5 joins exactly the same tables with different parameters causing some fun in ….doh its slow…erm which, everyone else is fine, I am going to interested how you identify which plan is causing problems and how do you force it be selecively updated…or do we still update stats and recompile them all.

        Reply
    • If I should guess I’d say, they are using statistics to decide if they need a new plan.

      1st query: country = ‘Iceland’ -> statistic says 10 rows -> build a plan with nested lookup
      2nd query: country = ‘USA’ -> statistic says 100k rows -> realize, that there is only a plan with much fewer rows -> create a new plan with hash mash
      3rd quer: country = ‘UK’ -> statistic says 50k rows -> it is “near enough” to the 100k from query 2 -> reuse this plan

      Reply
  • Mr David Tovee
    November 3, 2021 8:49 pm

    I don’t think I explained myself very well I mean if we had a parameter like 10 we could build into the dynamic SQL the value like “10=10” so for each unique parameter for the stored proc we would have unique dynamic SQL and a unique plan.
    I might have got the wrong end of the stick and It’s getting to the end of the day for me…

    Reply
    • Right, but then you have the problem of plan cache bloat that we talk about in the Mastering Query Tuning class module on dynamic SQL. It’s effectively putting OPTION (RECOMPILE) on every query because you won’t be able to cache any execution plans.

      Reply
  • Mr David Tovee
    November 3, 2021 8:51 pm

    I agree this sounds really exciting but as others have pointed out I don’t see this coming to production servers for several years but I guess the cloud could be calling…

    Reply
  • Hi Brent, thanks for sharing,
    Will the parameter sniffing end?

    Reply
    • Yes, right from the first release of SQL Server 2022, I’m excited to share that they’ve gotten it completely right, and we will never have performance problems due to parameter sniffing again.*

      * Cmon, seriously, think about that

      Reply
  • Thank you brent!
    I am very excited.

    Reply
  • Uggh more Azure
    My Cores, CPUs and Disks are cheaper and faster

    Query Store on by default = will script to turn it off
    But Maxdop and CE in query Store is nice

    Analytics: nice, bring them in-house and ill be impressed

    and what are the maximums on Standard Edition?
    yet still 128GB RAM?
    Yawn

    Brent, didnt you Njoy the 13CUs for 2019?

    Reply
    • Matthew H Iskra
      November 24, 2021 10:49 pm

      I do law enforcement IT. We ethically cannot do Azure; legally it’s a gray area. We still have requirements to do laser-disc due to the write-once-never-change nature of the media. This was due to court cases in the… 80s… I think. IANAL.

      And lastly, a Haiku:

      There is no free “cloud”
      just someone’s machine you use
      With data you gave.
      — with all modesty and apologies to Matsuo Basho

      Reply
  • You said in your conference;
    We can use dynamic query to solve the Parameter sniffing.
    Also said the server should have 128 GB of RAM at minimum for caching multiple plans of the one query.
    I think in SQL server 2022, many plans will be created per stored procedure.
    How will Microsoft deal with this problem?

    Reply
  • Khaled Budajaja
    November 5, 2021 8:29 pm

    Parameter sniffing is fixed in SQL Server 2022
    Good to know
    Thanks Brent for sharing

    Reply
  • That means we can have AG groups databases on read only replica (on premise) from high availability clustering setup and we can add some read write DB out of AG groups on the on premise server. This will help to setup node for ETls reading from ag groups dbs and writing to staging dbs.

    Reply
    • If you’re referring to the Azure SQL DB Managed Instance part, no, that’s not correct. The Azure DR scenario uses Distributed Availability Groups, and those probably won’t be readable.

      Reply
      • Thank you Sir for your prompt rely. Are there any options with MI where i can replicate TXN data to other node/server close to real time using AG groups and have read write stagings DBs on the same node ?

        Reply
  • So, many new stuff to make me pay for the cloud. Hard pass. And hold on till we see what of these performance optimizations will be part of the Standard Edition.

    Reply
    • “So, many new stuff to make me pay for the cloud. Hard pass. And hold on till we see what of these performance optimizations will be part of the Standard Edition.”

      This

      Reply
  • Alain Smithers
    November 12, 2021 1:44 am

    Will the Parameter-sensitive plan optimization “fix” the case where the optimizer won’t do a key lookup on parameter @MyKeyId?:

    WHERE MyKeyId = ISNULL(@MyKeyId, MyKeyId)

    It would be convenient if this type of WHERE clause was efficient.

    Reply
    • I wouldn’t expect that, no.

      Reply
    • at least you should rewrite it (even if it is annoying because longer) into WHERE (@MyKeyId IS NULL OR MyKeyId = @MyKeyId).

      Depending where / how you are using it an OPTION (RECOMPILE) will help too in this case (if the statement will not be called all the time)

      Reply
  • Am I missing something in the failover/failback? Imagine I have 2 nodes – one primary on site, and one for DR using an async Availability Group (or mirroring, or Transaction Log Shipping). If I want to do maintenance on the primary, I can make the DR replica synchronous (or do a final transaction log backup), do a failover, and run over at DR for a little while. Once the maintenance is done, I can reverse the stream pretty quickly, and be back up and running on my primary server.

    If I use Managed Instances, sure I can failover easily, but the example I keep seeing from Bob (and other MS demos) shows me how easy it is to back that up to Azure storage, and then restore it. That works great for their trivial size database, but if I’m dealing with 100’s of GB or more, and/or I don’t have a data center with the network capacity of Azure, how big an outage am I going to have to take to get my data back to my primary data center?

    Am I missing something in the demos? Or is this really a one-way feature?

    Reply
    • They’ve repeatedly said that the feature will allow for failing back via the AG, but they just haven’t demoed it. My guess is that they’re either doing the demos that way to keep the timeline tight (after all, it’s really hard to demo everything in a short period of time), or else the preview bits weren’t quite ready to show the fail back yet.

      Reply
  • Any new Graph features? SHORTEST_PATH was added in 2019. Hoping for “all paths” related feature.

    Reply
  • “SQL Server 2022, now with 100% more blockchain!”

    Hats off to MS on this one. They finally heard our cries over the last decade to add blockchain to our databases and dodged the whole “system databases in availability groups” bullet yet again. Thank god for that. This is the big one boys. Blockchain is coming to SQL Server, freakin’ blockchain!! What a time to be alive.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu