Breaking News: SQL Server 2022 Keeps Getting Worse.

SQL Server
51 Comments

<sigh> Okay, so, the last few Cumulative Updates have had known issues around broken remote queries using the generic ODBC connector and errors with contained availability groups, but I couldn’t really care less about those. If you use those features, I give you bombastic side eye anyway.

However, in the last few days, two more known issues have surfaced.

The first problem is that Cumulative Update 4 can give you incorrect query results when all of these are true:

  • Your index explicitly specifies the sort order, like DESC or ASC (see update below)
  • Your query has a WHERE filter on that sorted column using an IN list or multiple equality searches
  • Your query has an ORDER BY with the sort order as the index (which is, after all, why you created the index)

So for example, this can give me incorrect query results:

To work around that problem, the CU4 documentation suggests you uninstall CU4 or enable trace flag 13166 and free the plan cache.

Update: in the comments, Paul White points out that trace flag 13166 skips a logic step when building query plans, but it only applies to descending index keys. That means the CU4 documentation might be wrong, and this bug might only apply to indexes with a descending key specified.

The second problem is memory dumps every 15 minutes if you have both Query Store and Parameter-Sensitive Plan Optimization (PSPO) turned on. Microsoft says they’re working on this issue, but for now, the workaround is to disable Query Store or PSPO, or continuously delete PSPO plans from Query Store yourself.

Should you do new installations of SQL Server 2022 today? I’m not going to give you the answer, dear reader – instead, I wanna hear your opinion in the comments. If you were deploying a mission-critical production server in June, which SQL Server version would you pick?

Previous Post
Who’s Hiring in the Microsoft Data Platform Community? May/June 2023 Edition
Next Post
[Video] Office Hours at Sea: SQL Server Q&A

51 Comments. Leave new

  • New Jersey DBA
    May 26, 2023 1:29 pm

    Currently SQL Server 2019, more standard than enterprise due to TDE available in standard

    Reply
  • SQL Server 2019 enterprise. Last CUs have just fixed some memory dump issues that version had. SQL server 2022 doesn’t worth the risk.

    Reply
  • Hi Brent,

    I think this only applies to indexes with DESC keys.

    e.g.

    CREATE INDEX DisplayName ON dbo.Users(DisplayName DESC);

    — Not ordered by DisplayName ASC
    SELECT *
    FROM dbo.Users
    WHERE DisplayName IN (N’Brent Ozar’, N’Jon Skeet’)
    ORDER BY DisplayName ASC;

    Reply
    • That’s not what the docs say, though – it says “You have indexes that explicitly specify the sort order.” It doesn’t specify ascending or descending.

      Reply
      • Well far be it from me to disagree with the infallible documentation ? but their example does use a DESC index key and the trace flag is specifically aimed at skipping a logic step when possible, for descending index keys only.

        Reply
  • Given that I encountered the PSPO bug in my environment and had to shut down Query Store until they published the cause and workaround… I’d stick with SQL 2019 until they get the critters exterminated in 2022.

    With the query bug issue in CU4, I am testing the trace flag downstream, but I have no plans to push it to my main production systems at this point.

    Reply
  • Rodrigo Ribeiro
    May 26, 2023 1:52 pm

    Ever since the release of SQL Server 2019, numerous customers have been experiencing a significant number of excessive access violation dumps. Naturally, I had high hopes that the 2022 version would address most, if not all, of these issues. However, to my disappointment, it seems that the situation has worsened.

    In light of these ongoing challenges, it appears that sticking with SQL Server 2017 might be the best course of action for those seeking to avoid such problems. The 2019 and now the 2022 versions of SQL Server have proven to be quite unpredictable, resembling more of a gamble than a reliable solution.

    Reply
  • DeepSilence
    May 26, 2023 1:55 pm

    SQL Server 2017 Enterprise Edition. Testing SQL Server 2022 on some test servers so far, but we’re gonna wait to deploy it in production based on your info and others.

    Reply
  • With a DESC index key, if you specify ORDER BY key ASC you’ll get key DESC and vice versa ?

    Reply
    • I’m not sure what you mean – can you rephrase the question? (I’m reading that as, “Brent, you’re saying SQL Server returns results in the wrong order,” and I don’t think either of us mean that, heh.)

      Reply
      • Yep, that’s what I’m seeing. The order is exactly wrong for a descending index key:

        CREATE INDEX DisplayName ON dbo.Users(DisplayName DESC);

        — Ordered by DisplayName ASC instead of the requested DESC
        SELECT *
        FROM dbo.Users
        WHERE DisplayName IN (N’Brent Ozar’, N’Jon Skeet’)
        ORDER BY DisplayName DESC;

        — Ordered by DisplayName DESC instead of the requested ASC
        SELECT *
        FROM dbo.Users
        WHERE DisplayName IN (N’Brent Ozar’, N’Jon Skeet’)
        ORDER BY DisplayName ASC;

        Reply
        • BTW the question marks earlier were emoji apparently not supported in the comments section.

          Simpler repro:

          DECLARE @T table (i integer NOT NULL INDEX ii (i DESC));
          INSERT @T (i) VALUES (1), (2), (3);

          — Ordered descending!
          SELECT Ascending = T.i
          FROM @T AS T
          WHERE T.i IN (1, 2, 3)
          ORDER BY T.i ASC; — We asked for ascending

          — Ordered ascending!
          SELECT Descending = T.i
          FROM @T AS T
          WHERE T.i IN (1, 2, 3)
          ORDER BY T.i DESC; — We asked for ascending

          Reply
  • Vlad Drumea
    May 26, 2023 1:59 pm

    Should you do new installations of SQL Server 2022 today? – Yes, but only for testing purposes. I can’t even imagine how brave someone has to be to just throw 2022, in its current state, in a prod environment.

    Reply
  • Marc Scirri
    May 26, 2023 4:10 pm

    Is the incorrect query results issue specific to Compat Level 2022?

    Reply
    • That isn’t mentioned in the documentation, but your best bet would be to take the code example Paul White put above in the blog comments and try yourself.

      Reply
      • Marc Scirri
        May 31, 2023 5:44 pm

        I went all the way back to compat level 2012 and it still happens.

        Reply
      • Marc Scirri
        May 31, 2023 5:59 pm

        It also only happens when I specify DESC for my index order. It works fine when I specify ASC or don’t specify.

        Reply
  • Régis Sajo Diniz
    May 26, 2023 5:11 pm

    Oracle 12

    Reply
  • gareth.husk
    May 26, 2023 5:16 pm

    Screwing up query return order results would make SQL Server 2022 a hard no.

    And I was beginning to plan the rotation from 2019 to 2022. Technically this issue won’t affect us as we stay on the GDR track but screwing up basic queries that i know we have a lot of (get me the most recent activity by a user…) is going to cause trust issues

    Reply
  • Joe McBratnie
    May 26, 2023 5:27 pm

    Should you do new installations of SQL Server 2022 today? Not for production mission critical or not, we spend money on a DB server then it should be as good as can make if 2019 and 2022 are shaky at best. I am suggesting 2017 still with the some of my COTS systems requiring 2019. I am hoping Microsoft starts quality checking the products they are releasing to us.

    Reply
  • Barefoot Hillbilly
    May 26, 2023 5:36 pm

    We were pretty excited about some of the core engine enhancements and are currently targeting 2022 for new customers.
    However, this definitely gives me pause. To some extent, quality issues on a new release are par for the course. You wouldn’t believe the quality issues I’ve seen with one of SQL Server’s major competitors. But I’m straining to remember the last time MS released something that would impact us. These both would impact us.

    Reply
  • Jake Williams
    May 26, 2023 6:03 pm

    I am amazed at the level of detail that you people are finding, verifying and reporting on is astounding. Also I won’t be going to 2022 anytime soon. It’s probably a ruse to get people to use that crappy cloud based option, with price tags.

    Reply
  • Chris Fournier
    May 26, 2023 6:56 pm

    If the workload is business critical, then yes to 2022 with caveats (no PSPO or DESC indexes). If the workload is mission critical, then I’d err on the side of no to 2022 unless we performed a pretty serious internal testing and were satisfied with the results. TBH this doesn’t feel too different from SQL 2012’s release and how many of our customers didn’t want to move from 2008R2 until 2012 was fully baked. I understand that most businesses don’t want to take unnecessary risks by being a pioneer of new technologies, but if you can afford to its worth it in the long run.

    Reply
  • Edward Miller
    May 26, 2023 7:14 pm

    I’m a no on 2022. Cumulative updates are supposed to make things better not introduce new bugs. i’d like to add that I think Microsoft’s QA has gotten steadily worse over time and not just for SQL Server. Visual Studio 22 is absolutely the least stable version of VS ever released. It has unimaginable bugs like letting you work for a while only to delete all that code if you rename the file even from within VS. When you talk about using VS to create solutions that use SQL Server having both doing a crappy job makes isolating and fixing bugs that much harder. Is it a bug in VS, a bug in SQL Server, or my code?

    Reply
    • George Walkey
      June 2, 2023 2:07 pm

      ” Cumulative updates are supposed to make things better not introduce new bugs.”

      Bob Ward needs to fix bugs before introducing new ones – my 2c

      Reply
  • You might think that with more information getting pushed to Microsoft (using Azure rather than needing to activate reporting when on-premise) that issues like these would get known sooner and be more thoroughly tested. They are at least releasing CU’s more often and early in the cycle than previous. Lets hope for a return to quality fixes.

    Reply
  • Brendan O’Connor
    May 26, 2023 7:33 pm

    I’m starting to love MS Access

    Reply
  • Jennifer Mahoney
    May 26, 2023 10:44 pm
    Reply
    • Hmm, I read that post a couple times and I can’t seem to find the part where he’s talking about code quality. Can you point me to that?

      Otherwise it seems like you’re linking to a post about a single feature and saying that it means the whole product is big free, and I don’t think that’s quite correct.

      Reply
      • Jennifer Mahoney
        May 26, 2023 11:02 pm

        You’re correct, he’s not talking about code quality at all. As two people whose opinions I trust, I guess it reminded me of the importance of taking time to understand your needs when upgrading SQL Server, and using the version that best meets those needs.

        Reply
  • Hi Brent!
    Thanks for this post.

    My choice would be SQL Server 2019.

    Reply
  • How does these bugs affect the different flavors of SQL server resources in Azure?

    Reply
  • damn, that ordered columnstore index is still tempting

    Reply
  • In your opinion, what is the reason for such errors? Is this due to recent layoffs, or do recent layoffs aim to solve errors like this?

    Reply
  • Toby Harman
    May 29, 2023 7:31 am

    I’d love to go to SQL Server 2022 and take advantage of the Buffer Pool Scans ad Parallel Redo Batch Redo / Parallel Redo Thread Pool (I’m bored of having to futz around with Trace flag 3459 trying to get the right databases to have the Parallel threads), but we have a BUNCH of testing to do based on what we’re seeing here.

    Reply
  • We are at end of life on the OS (2012 R2) and migrating from SQL 2014 with plans to purchase the 2022 licenses in July. It seems we are headed for a cliff and I’m not sure how to stop the horses.
    We haven’t officially migrated the development servers yet, even though they’re ready – and I’ve been jumping up and down about this for the last year.
    Ugh. But thank you for continuing to provide these updates!

    Reply
  • Just browse the feature set, and there’s no way a single company can maintain a certain level of quality while reducing staff. They are at the risk of losing mission critical customers and then that would be the beginning of the end for the whole SQL server world. They spend too much resource on Azure related features and Azure offering itself and I think they are not spending as much resources as they used to in QAing the SQL engine itself while adding features I hope I am wrong, it if that’s what’s going on, the trend will continue until it’s too late.

    Reply
  • I wonder if these bugs are also present in Azure SQL DB and Azure SQL MI?

    Reply
    • And unfortunately, all we can do is wonder, because Microsoft doesn’t disclose things like this up in Azure. Don’t worry, everything’s taken care of for you! Wink wink.

      Reply
  • Pilchard123
    May 30, 2023 8:43 am

    Did something go wrong with your blog recently? The most recent post I could see for the last few weeks (your site being on my morning list of things to check for news) was “Happy 21st Anniversary, BrentOzar.com. What’s Coming Next?” and I figured there was nothing posted after that because you were taking a well-deserved break. But when I came to look this morning, there were 12 posts “after” Happy 21st, but they had all sorts of dates; the earliest was “Classing Up SSMS is as Easy as 1, 2, 3.”, dated April 26th, all the way to “Breaking News: SQL Server 2022 Keeps Getting Worse.”, dated May 26th. I’ve always accessed the blog directly through https://www.brentozar.com/blog/ , so I don’t *think* I did anything different this time.

    Reply
  • Speaking of indexes, there’s a pretty catastrophic bug with the sampled statistics calculation which Microsoft have now confirmed has been this way since at least SQL 2005, which I’ve attempted to bring to the attention of the community in a stackexchange post titled ‘possible-missing-step-in-sampled-statistics-generation’ here… https://dba.stackexchange.com/questions/323571/possible-missing-step-in-sampled-statistics-generation

    In a nutshell, there’s a missing extrapolation on the DISTINCT_RANGE_ROWS column of the histogram when in sampled mode, leaving the column representative of the data sample instead of the entire data and confusing the subsequent extrapolation of the AVG_RANGE_ROWS column…

    Which of course will make no difference whatsoever to the actual results returned, but at least in many cases we’ll get our results, correct or otherwise, much more quickly once that’s sorted out!..

    Reply
    • Chris – if something has been the same behavior for 18 years, I wouldn’t expect Microsoft to change that in a CU. It would be the kind of behavior that would likely only be changed under new compatibility levels and/or trace flag 4199.

      Reply
  • David Reabow
    May 30, 2023 8:00 pm

    The biggest take-away from this post is that you have a “bombastic side eye”
    On a serious note, thanks for the updates!

    Reply
  • I am so glad you posted this. I have been having a lot of consternation about whether to upgrade to 2019 or 2022; there is the crowd that say 2022 is the greatest release ever after running in azure for over a year. To be fair, a lot of the early bugs were pretty fringe and limited, but I have trust issues with Microsoft over how much of a mess 2019 was for its first 15+ cumulative updates. Something this egregious I think puts a nail in the coffin for 2022 for me for the nest 1.5-2 years outside of some very specific requirement 2022 alone addresses.

    Reply
  • There is a new blog article by Microsoft concerning PSPO issues and fixing them:
    https://techcommunity.microsoft.com/t5/sql-server-blog/parameter-sensitive-plan-optimization-why/ba-p/3836281

    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.