<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?
70 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.
Exactly why we pulled back from deploying SQL MI. We found a bug in a cross-database view, where it would not return data that had been updated since the database was restored from backup. This occurred somewhere between SQL MI CTP1.4 and CTP2.0. Lucky for us, M$ don’t upgrade all regions in one go, so we could demo the bug in UK-South but it not happening in France Central.
Even a call with some SQL MI PMs came to nothing after sending them traces proving the issue.
So we returned to SQL Server 2017, then upgraded to 2019 after much testing.
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
I opened a ticket about the Query Store issue. They said it would be resolved with CU5
How could Microsoft’s testers not have noticed the “core dump every 15 minute problem”?
It reminds me of the bug that was in SQL 2000
“Incorrect Record is Deleted or Updated When a Clustered Index is in Descending Order”
If you deleted when ID was greater than 1000, it would delete everyone whose ID was less than 1000…
https://support.microsoft.com/en-us/topic/inf-list-of-bugs-fixed-in-sql-server-2000-service-pack-1-1-of-2-8eaa54c9-5050-8c06-ff24-86fb1f2ab2dc
We have all new SQLs with SQL 2022 (so already many of them already) and some are migrating to 2022. But we have seen also many bugs around there.
One very important thing is that contained availability group is not supporting SSISDB – that I didn’t found in any official MS documentation and it was surprise for me! We had one pre-prod SQL server with contained availability group and after patching to CU4 (to fix this bug https://learn.microsoft.com/en-US/troubleshoot/sql/releases/sqlserver-2022/errors-apply-cu-contained-availability-group ) I found that SSIS subsystem was completely missing (before with CU3 it existed there). I made MS support case and official answer came that they don’t have made any changes in SSISDB for SQL 2022 and they don’t support SSISDB in contained availability group. The Catalog has not been tested with and does not support Contained Availability Group. For SSISDB catalog, the internal connection to the database is made via connection name SSISDB instead of the AG listener name. With the current design, SSISDB cannot make use of Contained AG environment.
They promised to update also documentation for this.
So we need to go with traditional availability group in our env then.
Also there is bug for DBCC check that if you checking on node directly with contained AG then you getting allocation error for contained AG master database. If you checking DBCC through listener name inside contained AG then all is fine.
They promised to fix this bug in CU5
So there are new fancy features, but they are still buggy, but MS actively developing and try to fix them.
Out of curiosity – what is the bombastic side eye about using generic ODBC about? We have other servers that are vendor servers that we get some data from? Is it that we should be getting the specific drivers for that server where possible?
Yep, you got it!
Has CU5 fixed any of the issues mentioned? I don’t have a SQL2022 instance to try.
CU5 fixes this issue
https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2022/incorrect-results-run-queries
Kinda – it fixes one of the two issues in the post. The memory dump issue, Microsoft is still working on for the next CU.
I have confirmed CU5 fixes 2 issues:
Memory dumps every 15 minutes if you have both Query Store and Parameter-Sensitive Plan Optimization (PSPO) turned on.
incorrect query results for desc indexes/selects with order by desc/asc
Yay! That’s good to hear.
Good to hear that they fixed at least that one. As was mentioned in this blog post I had to start wondering about the quality of the fixes.
Love to see your thoughts on CU 5
Small note that since CU5, CU6 has come out with more PSPO and Query Store combination fixes.
I guess the new rule is “wait until it’s patched in a CU then wait one more CU to see if they finished fixing it”.
LOL yeah just noticed that myself
Anyone else dealing with random failing queries and ‘incorrect syntax near ‘)’ errors? Our dev team wasted days trying to figure out why their queries were occasionally failing before looping in the DBA, me. No issues with the queries themselves, just MSSQL’22 occasionally doing something to create a syntax error. We’re seeing it on CU2 through at least CU5; Standard, Enterprise, and Dev.
Appears to be something in the Query Store, as disabling completely may fix it.
A group of us are trying to piece it together; https://learn.microsoft.com/en-us/answers/questions/1289807/mssql-2022-random-incorrect-syntax-)-errors?comment=answer-1290067&page=1&sharingId=2BE3A6F3FC8B2B13
FYI, something else to be aware of.
You should be on CU6 if you’re troubleshooting something like this – there were already Query Store fixes in CU6.
Wait – one more PSP/Query store fix in CU 7 “Fixes an access violation that you encounter during the initialization of Query Store if a parameter sensitive plan (PSP) optimization query variant has inconsistent data within the Query Store.” Guess we need to wait more than one CU to get it fixed.