<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:
|
1 2 3 4 5 |
CREATE INDEX DisplayName ON dbo.Users(DisplayName DESC); SELECT * FROM dbo.Users WHERE DisplayName IN (N'Brent Ozar', N'Jon Skeet') ORDER BY DisplayName DESC; |
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?
51 Comments. Leave new
Currently SQL Server 2019, more standard than enterprise due to TDE available in standard
SQL Server 2019 enterprise. Last CUs have just fixed some memory dump issues that version had. SQL server 2022 doesn’t worth the risk.
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;
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.
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.
Oh interesting! Thanks, I’ll amend the post to note that too.
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.
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.
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.
With a DESC index key, if you specify ORDER BY key ASC you’ll get key DESC and vice versa ?
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.)
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;
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
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.
Is the incorrect query results issue specific to Compat Level 2022?
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.
I went all the way back to compat level 2012 and it still happens.
It also only happens when I specify DESC for my index order. It works fine when I specify ASC or don’t specify.
Oracle 12
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
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.
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.
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.
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.
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?
” Cumulative updates are supposed to make things better not introduce new bugs.”
Bob Ward needs to fix bugs before introducing new ones – my 2c
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.
I’m starting to love MS Access
Apparently Pinal disagrees? https://blog.sqlauthority.com/2023/05/24/sql-server-2022-performance-buffer-pool-scan/
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.
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.
Exactly – I’ve written plenty of posts here too about how I love where SQL Server 2022 is going, but… it’s just gone off the rails.
Hi Brent!
Thanks for this post.
My choice would be SQL Server 2019.
How does these bugs affect the different flavors of SQL server resources in Azure?
damn, that ordered columnstore index is still tempting
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?
I couldn’t even begin to guess.
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.
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!
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.
I wonder if these bugs are also present in Azure SQL DB and Azure SQL MI?
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.
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.
No, everything’s been working great, but when I run sales, I “stick” a post to the top of the blog.
My colleague and I noticed the same thing, just have to hunt a little to get to the posts I’m looking for.
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!..
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.
The biggest take-away from this post is that you have a “bombastic side eye”
On a serious note, thanks for the updates!
Hahaha, my pleasure.
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.
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