SQL Server 2022 (major build version 16.00.xxxx). Please also tag sql-server.
Questions tagged [sql-server-2022]
216 questions
25
votes
1 answer
STRING_SPLIT with order not working on SQL Server 2022
I need to use STRING_SPLIT with enabled ordering:
We have upgraded all of our servers to SQL Server 2022 and change the compatibility level to 160, but the following code is not working:
SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', '…
gotqn
- 4,348
- 11
- 52
- 91
23
votes
1 answer
Is "+" slower than "CONCAT" for large strings?
I have always thought that CONCAT function is actually a wrapper over the + (String Concatenation) with some additional checks in order to make our life more easier.
I have not found any internal details about how the functions are implemented. As…
gotqn
- 4,348
- 11
- 52
- 91
16
votes
2 answers
Why is SQL Server setup recommending MAXDOP 8 here?
I'm running SQL Server 2022 RC1 setup on an AWS i3.16xlarge with 2 sockets, 2 NUMA nodes, 32 logical processors per node, 64 logical processors altogether.
Setup is recommending MAXDOP 8:
But if you click on that link for configuring MAXDOP, the…
Brent Ozar
- 43,325
- 51
- 233
- 390
15
votes
1 answer
Broken Query Store
We have SQL Server 2022 Enterprise (16.0.4131.2) on Windows 2022 VM (8 vCPU cores) and there is an issue with the Query Store on one of the databases.
Below is the sequence of steps I can proceed to reproduce the issue on one specific database and…
Martin Karouš
- 508
- 3
- 11
15
votes
2 answers
Why does sys.fn_xe_file_target_read_file require an explicit cast on datetime2 column?
According to the documentation the returned column timestamp_utc should be of type datetime2(7)
But when I query like this
SELECT
*
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE timestamp_utc > dateadd(hour,…
Zikato
- 5,619
- 1
- 17
- 34
8
votes
3 answers
View doesn't recognise a change to an underlying table when an existing column is dropped and replaced with one with the same name but as computed
My understanding here is that when a View is created, the definition is stored in metadata sys.tables. i.e. sys.views.
Also, that if you use SELECT * FROM it will store the exact column names e.g. SELECT a, b FROM.
Even if you use the "CHECK…
Geezer
- 513
- 4
- 13
8
votes
1 answer
How to create alias in SQL Server 2022
As per https://learn.microsoft.com/en-us/sql/relational-databases/native-client/sql-server-native-client?view=sql-server-ver16:
The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server…
Tom Pažourek
- 553
- 7
- 19
7
votes
2 answers
Microsoft SQL Server | Investigate Root Cause of DBCC CHECKDB error | (SQLSTATE 42000)(Error 8992 | Catalog Msg 3853)
Details:
I am running Microsoft SQL Server 2022
Microsoft SQL Server 2022 (RTM-GDR) (KB5046861) - 16.0.1135.2 (X64)
Oct 18 2024 15:31:58
Copyright (C) 2022 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2022 Standard 10.0 …
Lucas Bjørndal
- 93
- 6
7
votes
2 answers
Does OPTIMIZE FOR UNKNOWN do anything for table-valued variables/parameters?
I have a query that is both prone to parameter sensitivity and is suffering from its table-valued parameter. I'm lazy and just want to solve this with query hints. When I'm lazy, I can solve parameter sensitivity with OPTION (OPTIMIZE FOR UNKNOWN).…
J. Mini
- 1,161
- 8
- 32
7
votes
2 answers
Does Instant File Initialization work for manual log file growths?
SQL Server 2022 introduced Instant File Initialization for transaction log file growth events. In the What's New in 2022 page, Microsoft notes:
In general, transaction log files cannot benefit from instant file
initialization (IFI). Starting with…
Brent Ozar
- 43,325
- 51
- 233
- 390
6
votes
4 answers
If all of the indexes of a table are partitioned with the same function and scheme but on different columns, is it still considered aligned?
The documentation says the following
Aligned index
An index that is built on the same partition scheme as its corresponding table. When a table and its indexes are in alignment, the database engine can switch partitions in or out of the table…
J. Mini
- 1,161
- 8
- 32
6
votes
1 answer
Tale of two servers. What could be limiting queries to running on two cores? MDOP set to 16
I have two servers.
A garbage picked, slapped together with duct tape and bubble gum. It is a mix of Dell R620 and Gen 8 HPE parts with 40 cores / 1.5 TB ram. Running ubuntu 22.04 and sql server 2022 Dev Version. Raid 10 3.7 TB SSD's. MDOP is set to…
Ryan
- 63
- 2
6
votes
2 answers
MSSQL Server 2022 on Linux: sys.* tables very slow for non-db_owner users
Querying sys-tables, for instance sys.columns, sys.indexes or sys.tables, has become significantly slower on SQL Server 2022 on Linux for users which are not db_owner.
To reproduce, take a SQL Server 2022 on Linux (I've used 16.0.4035 which is the…
final
- 335
- 2
- 11
6
votes
1 answer
Distributed Availability Groups refusing to resync my FILESTREAM database with SUSPEND_FROM_CAPTURE
My home lab setup consists of four servers running in HyperV across two physical hosts. The SQL Server instances are SQLAG101, SQLAG102, SQLAG201, and SQLAG202.
SQLAG101 and SQLAG102 are members of the SQLAG100 Availability Group, and are on the…
Hannah Vernon
- 70,928
- 22
- 177
- 323
5
votes
1 answer
Why not use Snapshot isolation for everything read-only?
Suppose that:
I already have Snapshot isolation enabled
I do not have Read Committed Snapshot Isolation enabled
I have been strict with my READCOMMITTEDLOCK hints wherever I truly need them
then is there any reason at all to not use the Snapshot…
J. Mini
- 1,161
- 8
- 32