Questions tagged [sql-server-2022]

SQL Server 2022 (major build version 16.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
0 votes
0 answers
23 views

Unresponsive restore process for log shipped db having memory optimised tables

Log shipping documentation has a caution: https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-ver16 Known issue: For databases with memory-...
variable's user avatar
  • 3,541
0 votes
0 answers
37 views

Can my database LDF and MDF files go into this directory?

Is there a way to determine, given a drive and a directory, if a CREATE DATABASE command would succeed? There are basically two questions here. 1. How do you determine if a simple command like this ...
Null Pointers etc.'s user avatar
1 vote
1 answer
126 views

Performance degradation after migrating from SQL Server 2016 to SQL Server 2022

I have migrated our load test environment from SQL Server 2016 to SQL Server 2022 (Developer edition). Here are some findings: Changed compatibility level of databases to 160 (for SQL Server 2022) - ...
user3510083's user avatar
2 votes
2 answers
173 views

What SQL Server server-level roles allow you to add users to database-level roles?

In SQL Server 2022 (on-premise), I have a login (call it 'UserManager') which I have added to the ##MS_DatabaseConnector##, ##MS_DatabaseManager## and ##MS_LoginManager## fixed server-level roles. The ...
DGreen's user avatar
  • 121
0 votes
1 answer
34 views

INFORMATION_SCHEMA.COLUMNS - how to distinguish between table columns and view columns

My database has lots of tables and lots of views. For one of my tasks, I'm using this query. When I run following query it returns tons of columns - many of which belong to tables and many belong to ...
nam's user avatar
  • 291
3 votes
1 answer
138 views

Does automatic tuning revise the plan decision?

When automatic tuning is enabled, using SET AUTOMATIC_TUNING(FORCE_LAST_GOOD_PLAN), then based on query regression suppose SQL server auto decides to force a plan, then when does it revisit it's ...
variable's user avatar
  • 3,541
-1 votes
0 answers
48 views

Restore of database overwriting existing database even though `with replace` isn't set

SQL Server 2022, Standard Edition I have a database on one server (Server 1) called TestDb. I restored it on another server (Server 2) as TestDb_CORS. Users did some testing on the restored database, ...
variable's user avatar
  • 3,541
0 votes
1 answer
62 views

Restoring a SQL DB that had TDE turned on, cannot find certificate

I have SQLServer 2022 Developer on my machine. A customer has send me a SQLS 2019 backup file. When I attempt to restore it into a fresh DB, I get: Cannot find server certificate with thumbprint '...
Maury Markowitz's user avatar
0 votes
0 answers
44 views

Why does a DPM protection group configured for Sync work for some databases in simple recovery model but not others?

I'm using System Center Data Protection Manager 10.22.197.0 to back up SQL Server 2022 CU 10 on Windows Server 2022. DPM agent is also 10.22.197.0 on these SQL servers. The SQL Server instance was ...
M Herbener's user avatar
0 votes
1 answer
89 views

Auditing for direct Object Access (Skipping indirect access)

to isolate internal tables from third party database access, we've created schemas specifically for that use case. Third party Logins/DB Users only have access to those "public" Schemas. The ...
Arokh's user avatar
  • 105
0 votes
0 answers
50 views

MSSQL 2022 always on contained audit not logging

when i try to create an audit from the listener he goes on the node directly not in the contained instance. Then the audit log only show's the node instance not the AG log How to create an audit on ...
Tisax190's user avatar
0 votes
0 answers
22 views

Slow performance joining on ID with partitioned clustered columnstore table

I have an Orders table that has a clustered columnstore and is partitioned on OrderDate (datetime2(2)). Has about 50 fields It works great in most of our use cases. It's only when people need to join ...
Gabe's user avatar
  • 1,334
0 votes
1 answer
91 views

Communicating from a SQL Server 2022 to a SQL Server 2008 over linked server

I'm doing a proof of concept to assess whether we can upgrade some our servers piecemeal to SQL Server 2022. Unfortunately we still have a SQL Server 2008 which holds an old version of Microsoft ...
Jamie's user avatar
  • 101
0 votes
1 answer
40 views

How to load a Database associated with a Query?

I'm using MS SQL Server Management Studio. Logged into the proper server. I'm loading a previously used Query. I have selected (in Object Explorer) the Database it was associated with. But I get an ...
Clay Nichols's user avatar
1 vote
2 answers
311 views

How can I optimize a recursive CTE inside a IVTF?

I have a recursive CTE inside a inline table-valued function. The ITVF is returning a list of IDs containing a long sequence of ancestors for a person, it usually loops back about 12 to 18 times ...
James McGrath's user avatar
0 votes
0 answers
35 views

SQL 2022 replication does not work on non-standard port

Consider the following: 3 identical Windows Server 2019 VMs running WSFC (no shared storage) 3 SQL 2022 PROD instances across those 3 VMs (using WSFC/AG for easy hostname, three instance AG) 1 SQL ...
JulioQc's user avatar
  • 133
0 votes
0 answers
65 views

TraceFlag 15097

I wanna use traceflag 15097 and benefit from AVX-512 on SQL Server 2022. I saw some videos where they demonstrated the feature, however I didn’t find out how to use the extended events to monitor ...
nojetlag's user avatar
  • 2,877
0 votes
0 answers
114 views

how to install Components (client tools connectivity and Client tools backwords Compatibility) in sql server 2022

I'm installing SQL Server 2022. When I install SQL server 2022, I cannot find the components below in the feature selection: Client tools connectivity Client tools backwards Compatibility Client ...
bangu00's user avatar
1 vote
1 answer
884 views

MS SQL show percent extents changed per table

I've got differential backups set up for MS SQL Server 2022 and would like to know how much each table in the database is contributing towards the size of the differential backup. The page below shows ...
CoderBrien's user avatar
0 votes
0 answers
95 views

Statistics on a highly volative table

We have a queue table that as the nature of that type of table are highly volatile. For the rest of the database do need to have automatic update of statistics turned on. This means that the ...
Peter Å's user avatar
  • 345
0 votes
1 answer
91 views

SQL72014 error importing bacpac with multiple columns using a UDT bound to a rule

Apology First off, apologies for length - this is a weird issue so I'm trying to give enough detail to make it reproducible. tl;dr So I've think found an issue while exporting a *.bacpac Data Tier ...
mclayton's user avatar
  • 143
0 votes
1 answer
84 views

How to optimize spatial join?

The SQL Server is pretty fast finding neighbors of a geography in a spatial index. Either it is finding nearest neighbors or all neighbors in specific area, the query result is instant. I am not able ...
gotqn's user avatar
  • 4,016
0 votes
1 answer
44 views

How I can place further constraints upon foreign key?

In sql server I have the following table: CREATE TABLE mydb.dbo.files ( id bigint IDENTITY(1,1) NOT NULL, name nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, folder bigint ...
Dimitrios Desyllas's user avatar
0 votes
1 answer
33 views

Switching table partition into temporary table

I'm trying to switch a table partition into a temporary table with the following query: create partition function [PFCT_T](datetime2) as range right for values () create partition scheme [PSCH_T] as ...
Arokh's user avatar
  • 105
0 votes
0 answers
13 views

Running DBCC on NON-READABLE replica

Since the system databases are not duplicated in a AG environment, I want to be able to run DBCC on them on a weekly basis and I would also like to be able to run DBCC on any databases where the AG ...
Keith Clark's user avatar
0 votes
2 answers
154 views

SELECTING TOP X based on last 30 days of activity

I have a table CREATE TABLE [dbo].[UserMoviesActions]( [Actionid] [int] IDENTITY(1,1) NOT NULL, [MovieId] [int] NOT NULL, [UserId] [int] NOT NULL, [CreatedDateUtc] [datetime2](7) NOT ...
Zoinky's user avatar
  • 359
0 votes
1 answer
88 views

Correct way to get a players position

I have a UserActivities table CREATE TABLE [dbo].[UserActivities]( [UserId] [int] NOT NULL, [Points] [int] NOT NULL, which could have 100k+ users, each user having 1k+ activities. I would ...
Zoinky's user avatar
  • 359
23 votes
1 answer
4k views

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 ...
gotqn's user avatar
  • 4,016
0 votes
0 answers
103 views

SQL Server 2022: AlwaysOn AG databases can not sync after applying CU6 or higher

The setup is: OS - Win Server 2022 & MSSQL - SQL Server 2022. AlwaysOn Availability Groups are enabled on two (primary + secondary) replicas and use WSFC. Several availability groups are created ...
Tom_HJ's user avatar
  • 1
0 votes
0 answers
66 views

SQL Server Network Configuration Tab is missing. Can't enable TP/IP Protocols

I am using SQL Server 2022 and the "SQL Server configuration manager" is not visible. Further more I opened "Services and Applications" and in computer management and SQL Server is ...
Ahmad Imran's user avatar
1 vote
1 answer
317 views

New SQL Server 2022 slower than old 2014 server

I'm planning and testing a new SQL Server 2022 install to migrate from an older 2014 database. I've noticed during performance testing some major discrepancies with some bulky queries. Here is a query ...
JulioQc's user avatar
  • 133
0 votes
1 answer
737 views

[ODBC SQL Server Driver][DBNETLIB] SSL Security Error - Windows 2003 App Server to Remote SQL Server 2022 Connection

We have migrated the application databases from an old SQL Server 2000 instance running on Windows 2003 to a new SQL Server 2022 instance running on Windows 2022. The application tier has a number of ...
Fza's user avatar
  • 652
0 votes
1 answer
96 views

Is WHERE [FOO] IN (SELECT [BAR] FROM [...]) guaranteed to give the same execution plan as the equivalent WHERE EXISTS?

In the past, there was a lot of paranoia around IN. In particular, I remember claims that it can be optimised differently to an equivalent WHERE EXISTS. Is this claim still true? Or is it now ...
J. Mini's user avatar
  • 135
0 votes
0 answers
24 views

How to use AAD-MFA on SQL 2022 on an Azure VM?

So apparently SQL Server 2022 now supports the use of AAD-MFA (Azure Active Directory with Multi-Factor Authentication) as an auth method. (Source) However, this requires Azure Arc, and according to ...
Mike's user avatar
  • 658
4 votes
1 answer
779 views

SQL Server memory requirements

At this moment I run into some database performance problems for a SaaS application. During the day the RESOURCE_SEMAPHORE wait stats shoot up to 30 to 60 seconds for 1 or 2 minutes. During that time ...
ThomasDB's user avatar
-3 votes
1 answer
163 views

How to exclude some column from history table in temporal table

I have a same question in Stack Overflow about this, But I hope to find answer here I have temporal table like below -- Create a Temporal Table CREATE TABLE dbo.Sample ( SampleId int identity(1,1) ...
sorosh_sabz's user avatar
-1 votes
2 answers
94 views

Extended events query in User Database

I've been looking at query store results for a user database for a membership system. Performance has been poor with a number of queries timing out and showing inconsistent behavior. This is a brand ...
Sir Swears-a-lot's user avatar
1 vote
0 answers
78 views

Why does msdb of dev server show the backup history from another server after I perform a restore?

Observed a strange thing today. I have full/diff/log backups configured on prod server. I restored one of the prob db onto the dev server. Backupset, backupmediafamily msdb tables on the dev server ...
variable's user avatar
  • 3,541
0 votes
1 answer
69 views

SSRS report filepath parameters

I'm trying to migrate reports over to a new SSRS 2019 server and one particular report gets called from an excel macro where it reads in the contents of a file located at a given filepath. I've tried ...
Robert Weatherman's user avatar
1 vote
1 answer
213 views

How to perform SQL vulnerability assessment in SSMS 19.1 onwards?

I'm using on-prem SQL 2022 enterprise edition. SQL vulnerability assessment option is taken out from SSMS 19.1 onwards. I'm reading that this feature is now part of defender for cloud. If anyone has ...
variable's user avatar
  • 3,541
-2 votes
1 answer
45 views

Where can I check if the PARAMETER_SENSITIVE_PLAN_OPTIMIZATION is enabled or not on a db?

Where can I check if the PARAMETER_SENSITIVE_PLAN_OPTIMIZATION is enabled or not on a db? I tried looking for it in sys.databases and also under the database properties UI. Is query store enabling a ...
variable's user avatar
  • 3,541
0 votes
1 answer
161 views

Strange CPU assignment behavior in MS SQL Server 2022

Environment We have a legacy system with a MS SQL Server 2012 database, which should be migrated to a new server with MS SQL Server 2022. Specs of the new system: vServer with dedicated processor ...
user avatar
1 vote
1 answer
81 views

Does any IQP functionality need to be explicitly enabled in SQL 2022?

I'm exploring IQP functionality on SQL 2022 Developer Edition. It appears based on documentation that enabling Query Store is the only requirement for some of the IQP functionalities, and that Query ...
variable's user avatar
  • 3,541
0 votes
2 answers
99 views

Why Are Many Open Delta Rowgroups Created On A SQL Server Columnstore Table When Simultaneously Trickle Feeding Rows And Running Select Queries?

SQL Server 2022 Express (16.0.1000.6) on Windows Server 2016 Standard. I have a table with two indexes. An ordered columnstore index (ordered by a smalldatetime column, and then an int column). A ...
Roj's user avatar
  • 109
0 votes
0 answers
50 views

Database Engine Services and Integration Services failed during SQL Server 2022 installation

Please, what can i do concerning the issue?
Ibukun John's user avatar
0 votes
1 answer
62 views

T-SQL restore database restores old data only

I had a migration from SQL Server 2014 to SQL Server 2022. I first created FULL backups from the SQL Server 2014 instance, and then ran RESTORE DATABASE script shown below to do a test migration. ...
user avatar
0 votes
1 answer
156 views

command line client included in "SQL Server Management Studio 19"

With a web search I found that sqlcmd is included in SQL Server. Is there a command line client included in "SQL Server Management Studio 19"? I wish to run some SQL scripts in an automated ...
Sybil's user avatar
  • 2,488
0 votes
1 answer
52 views

Moving data from Oracle12C to SQL 2022

Currently we have to migrate off of our SQL 2012 server since it is outside Microsoft support. We currently have replication from an Oracle 12C database to current SQL 2012. We like to move to 2022 ...
Leo Torres's user avatar
5 votes
11 answers
3k views

Re-write T-SQL where clause causing performance issue

I have a query with the below where clause taking lot of time to execute since the underlying table has lot of data in it: Environment: SQL Server 2022 WHERE clause: DATEFROMPARTS(dyear, dmonth, dday) ...
SQLPRODDBA's user avatar
  • 1,908
-1 votes
1 answer
355 views

Sudden query problem with higher compatibilty levels on SQL 2022

We recently migrated a db from a SQL2016 server to a SQL2022 server. When we migrated the database we changed the SQL compatibility level to SQL2022 (160). Life was good. Then suddenly something ...
zu1b's user avatar
  • 121