Customer wants to save money. They have 2 separate on-prem SQL Server AlwaysOn clusters, we already upgraded one of them (two nodes) to SQL Server 2022. Now the other 2-node cluster... What if we do not build a new cluster for this one, but instead, we add a 3rd node to the existing cluster to better utilize the resources. Unfortunately we are not allowed to just simply put these databases on the first cluster under a separate AlwaysOn AG group. So as a compromise, we would run these databases on a 3rd node to give a bit more separation and use the other two nodes as Secondaries. This way the customer only needs to pay for one more node, not for two nodes. What do you think about this idea? Would it impact and slow down the databases on the 1st AG group due to the added AlwaysOn redo queue?

I am learning to use Master Data Services for the first time and currently stuck on **updating** existing data. This is also my first time using SSIS so I am currently learning on how to use SQL Command to update data. **Overview data load workflow** 1. Data is being stored into a staging table (DQS_STAGING_DATA) 2. When load successful, data then will be loaded from DQS_STAGING_DATA into each staging table in MDS with import type 0 (Ex: stg.Person). **My current SSIS workflow** [Loading data into MDS stg.Person and stg.Company](https://i.sstatic.net/LBhe3Ldr.png) **What I have tried** Change import type from import type 1 to 0. > 1: Create new members only. Any updates to existing MDS data fail. > 0: Create new members. Replace existing MDS data with staged data, but only if the staged data is not NULL. How do I update data inside of the stg.Person and stg.Company using my current SSIS workflow and ensure that Master Data Excel Add-ins will reflect the new data? Both of these staging tables also have their own subscription view. **My expectation** 1. A simple to follow step by step and beginner explanation to update existing data in Master Data Services. 2. Comment and feedback on my current SSIS pipeline.

Suppose that I have a database with Read Committed Snapshot Isolation already enabled. Is there any reason at all to not also enable Snapshot Isolation? Intuitively, you would think that the row versions would be kept around from longer. [The documentation dismisses this](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver17#behavior-when-reading-data) . > Even though READ COMMITTED transactions using row versioning provides a transactionally consistent view of the data at a statement level, row versions generated or accessed by this type of transaction are maintained until the transaction completes. So I am left without any ideas. Assume SQL Server 2022. SQL Server 2025 brought with it Optimized Locking, which creates just enough uncertainity in my mind that I don't want to ask about it here.

To have an Availability Group, I must have a Windows Server Failover Cluster. All Windows Server Failover Clusters have the ability to form exactly one quorum. This clearly means that if I have two Failover Cluster Instances, I must have two quorums. But what happens if I make an Availability Group out of two Failover Cluster Instances? **Do I need to manage three quorums?** [The documentation](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server?view=sql-server-ver17#WSFC) certainly gives the impression that the Availability Group is subject to the vote of a quorum, but I don't know which quorum.

I have read the SQL Server 2022 Licence Guide with great care. As far as I can tell, the following is a perfectly valid licence configuration if you have **two licences and Software Assurance**. Assume that I am talking about the same AG throughout. |Server Name | Location | Setup | Covered by Licence | |:---- |------:| -----:| -----:| |Prod Primary | Main data centre | Production Primary, hosting exactly one AG | Licence A - Primary | |Prod HA | Main data centre | Automatic failover AG replica, synchronously replicated | Licence A - Free HA server | |Prod Manual Failover | Main data centre | Manual failover AG replica, asynchronously replicated | Licence A - Free DR server| | DR Readable | DR site | Readable AG secondary, failover/replication don't matter | Licence B | |DR HA | DR site | Automatic failover AG replica, synchronously replicated | Licence B - Free HA server | |DR Manual Failover | DR site | Manual failover AG replica, asynchronously replicated | Licence B - Free DR server| I find it surprising that this seems allowed. **Am I correct that it is allowed?** Obviously, Microsoft has the final say on anything like this. However, what I'm describing here *must* be so common that the professionals here have either seen it before or already know why it does not work.

I know, at least theoretically, that heap tables can be partitioned. As I learn more about table partitioning, I realise that the use case is very limited. Similarly, the use case for heaps in SQL Server is also very limited. Whenever I intersect my list of heap use cases and partitioning use cases, I find myself with an empty list. Are there any sets of circumstances where a partitioned heap would appear strongly desirable?

We've all read [the documentation for Snapshot isolation](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16#modify-data-without-optimized-locking) and know about Update Conflict Detection and when you should _theoretically_ use Snapshot isolation for writes. However, I have never found anyone who does their writes under Snapshot isolation. Paul White has [a post on how it can go wrong](https://www.sql.kiwi/2014/06/the-snapshot-isolation-level/) , but I have never seen anyone discuss what it looks like when it goes _right_. I haven't found it in any textbooks, blogs, or production servers. **How can I design an experiment to show the benefits of writing while under Snapshot isolation?** I am particularly interested in comparing write performance. However, what should I vary to test when writing under Snapshot is a good idea? Furthermore, what is a fair comparison to it? Read Committed, RCSI, or something more extreme like Serializable? I am **not** asking about Read Committed Snapshot or [using Snapshot for reads](https://dba.stackexchange.com/questions/346376/why-not-use-snapshot-isolation-for-everything-read-only)· ; They're both awesome.

I'm running into an issue while trying to execute the stored procedure **sp_BlitzLock** in SQL Server 2022 Developer Edition. Here's the exact error message I'm getting: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_BlitzLock'. Completion time: 2025-07-21T22:17:36.5575216+02:00 Any help would be greatly appreciated!

I have a need to repeatedly replay workloads from QA over and over again to verify that various scripts that we have do not impact on functionality of our overall product. As part of my tests, I am not interested in anything beyond a simple pass or fail for the workload. I imagine I should be able to monitor this with EE. I just wanted to confirm which is the best tool suited for this and ideally the easiest to use. I'm aware of distribute replay though from the bits I've heard it can be a bit clunky and it is on a deprecation path.

I'm trying to connect (on the same host) from SSMS 19.0.2 to a newly installed SQL Server 2022 developer edition instance. I am getting a weird internal error:

===================================

Internal connection fatal error. Error state: 15, Token : 23 (Microsoft.Data.SqlClient)

------------------------------
Program Location:

   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I found [this](https://dba.stackexchange.com/questions/317116/microsoft-sql-server-2019-developer-edition-suddenly-throwing-internal-connectio) , and the answer suggests uninstalling KB5011048 (.Net Framework 4.8.1), but I do not have that version of .Net installed. Can someone point out what's wrong? *Edit:* I have made some progress. After fiddling with excluded port ranges, I now have SQL Server again listening on the default (1433) port. A Wireshark trace suggested that there was some sort of problem with a TLS handshake, so I changed the connection options to "Encrypt connection" and "Trust Server Certificate". Now I get a connection timeout in the post-login phase. If I use sqlcmd, the following happens:

> .\sqlcmd -S 'tcp:' -N true -C
read tcp 127.0.0.1:55499->127.0.0.1:1433: wsarecv: An existing connection was forcibly closed by the remote host.
read tcp 127.0.0.1:55499->127.0.0.1:1433: wsarecv: An existing connection was forcibly closed by the remote host.

In wireshark, I see TDS prelogin messages go back and forth, then the client sends what wireshark describes as a "TLS Exchange", to which the server sends a TCP ACK, and then closes the connection.

I have recently discovered [Transact-SQL snapshot backups](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-transact-sql-snapshot-backup?view=sql-server-ver17) . They let you interact with snapshot backups, like this flowcharts explains. It is obvious that this is compatible with a traditional SAN. I have found that EBS has [something similar](https://aws.amazon.com/blogs/modernizing-with-aws/automating-sql-server-point-in-time-recovery-using-ebs-snapshots/) , but are the EBS-style snapshots compatible with Transact-SQL snapshot backups? That is, can EBS generate snapshots that would agree with syntax like this?

BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

The documentation for [sys.dm_hadr_database_replica_states](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql?view=azuresqldb-current) states > 1 = Synchronizing. For a primary database, indicates that the database is ready to accept a **scan request** from a secondary database. For a secondary database, indicates that active data movement is occurring for the database. This is the first time that I've seen the phrase "scan request" in the context of Availability Groups. I cannot guess what it means nor find it elsewhere in the documentation. What is it? Where is it documented?

Start up the auto_stats Extended Event, filter out some noise, and prepare yourself to monitor it.

CREATE EVENT SESSION [AutoStatsTest] ON SERVER 
ADD EVENT sqlserver.auto_stats(
    WHERE ([duration]>1 AND [object_id] > 0) ) 
ADD TARGET package0.ring_buffer

ALTER EVENT SESSION [AutoStatsTest] ON SERVER
STATE = START
GO

Find a temporal table. I had a copy of the StackOverflow2010 database to hand, so I just made Votes system versioned.

ALTER TABLE Votes ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE Votes
    SET (SYSTEM_VERSIONING = ON);
GO

With your temporal table, do something that will trigger an automatic update of stats. This worked for me

SELECT TOP (1000) [BountyAmount]
FROM [StackOverflow2010].[dbo].[Votes]
WHERE [PostId] 
			
			
			
				100

Given the duration (800 microseconds on my pathetic machine), I knew that this was nonsense. Upon consulting sys.dm_db_stats_properties, I found that the actual percentage was much less than 100.

/*
Save yourself the pain of writing
the stats query
and just use sp_BlitzIndex from GitHub
*/
EXEC sp_blitzindex @databasename = 'StackOverflow2010', @tablename = 'Votes'

In summary, **it appears that the auto_stats Extended Event reports an incorrect sample_percentage for temporal tables**. Is this a SQL Server bug or is it my misunderstanding? If it is my misunderstanding, then where can I read further? Individual Extended Events, as far as I know, are very lacking in documentation. I have only seen this happen with temporal tables. I have found it on both SQL Server 2022 on a real box (the table was multi-terabyte, so I screamed when I saw a 100% sample rate for the primary key's statistic) and on my local 2019 test box. Adding a columnstore index seems to help reproduce this, but I am not 100% sure.

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_ColStore
on dbo.Votes
(
    Id,
    PostId
)

I am absolutely sure that there is a real bug here. However, my attempts to reproduce it have given inconsistent results. I suspect that you need a TB-scale table. **Final update:** I give up on explaining this one. The statistics for the table says that 90 times more rows than appeared in the actual execution plan (caught live as it was running in production, scanning the non-clustered columnstore index) were used to update the statistics. The number of rows in the actual execution plan is 10,000 times less than the table's row count and the auto_stats Extended Event says that 100% of the rows in the table were read. It is impossible for all of these to be true at the same time. I suspect that it might actually be the deleted bitmap that causes all of this.

**Question**: Why example 1 does not work while example 2 works: **SQL Table to check for non-numeric values in varchar column**: CREATE TABLE #t(Mycol varchar(15)) INSERT #t VALUES ('123.86'),('4a5.67'),('45.68'),('45r8.78') Query to check the NON-NUMERIC values in the table: SELECT Mycol from #t WHERE ISNUMERIC(Mycol) = 0 **Output**: | Mycol | |---------------------| | 4a5.67 | | 45r8.78 | **GOAL**: Achieve the same by using dynamic SQL **Example 1**: Did not work, why? How can we improve this code without declaring variables outside EXECUTE sp_executesql statement; or is it even possible? EXECUTE sp_executesql N'SELECT @colName as Mycol from #t WHERE ISNUMERIC(@colName) = 0', N'@colName varchar(15)', @colName = N'Mycol'; **Output**: | Mycol | |---------------------| | Mycol | | Mycol | | Mycol | | Mycol | **Example 2**: Works - but required more variable declarations. DECLARE @Qry nvarchar(150), @colName varchar(15) = 'Mycol' SET @Qry = N'SELECT ' + @colName + ' FROM #t WHERE ISNUMERIC(' + @colName + ') = 0' EXECUTE sp_executesql @Qry, N'@colName varchar(15)', @colName = N'Mycol'; Output: | Mycol | |---------------------| | 4a5.67 | | 45r8.78 |

I’m currently working on a proof of concept (POC) involving column-level encryption and key management (SMK/DMK) in a SQL Server 2019 Always On Availability Group (AG) environment (CU20). I’ve tested multiple scenarios and am trying to determine the best approach for ensuring encryption consistency across replicas. In my first test, I created the Database Master Key (DMK) before adding the database to the AG. Everything worked fine — the encrypted data was accessible from the readable secondary and even after failover. In another test, I added the DMK after the database was already in the AG. In this case, encrypted data was not accessible on the readable secondary or after failover. To fix this, I removed the database from the AG and re-added it with the password — this allowed encryption to work across all replicas. In a more complex scenario, I created a DMK without explicitly encrypting it with the local Service Master Key (SMK) on the primary. After a failover, the readable secondary (which became the new primary) couldn’t access encrypted data. To resolve this, I ran OPEN MASTER KEY DECRYPTION BY PASSWORD followed by ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY on the new primary. This fixed the issue on the new primary, but now the original secondary (i.e., the old primary) couldn’t access encrypted data. I noticed that after restarting the servers, only the latest SMK used for DMK encryption allows automatic decryption, and the previous SMK is no longer valid unless re-added. Given this behavior, I have a few questions: * Is it expected that only one SMK can be used for automatic decryption of a DMK at any time? * Should DBAs routinely re-encrypt the DMK with the local SMK after every failover to ensure continuity? * Would it be better to restore the same SMK across all replicas to maintain consistency — and is that considered safe in a production environment? I’m aware that SMK restore using FORCE can affect linked servers, credentials, and SSIS, so I’m hesitant to use it in prod without clarity. I’d really appreciate input on the best practices or any Microsoft-recommended approach for managing encryption chains in AG setups with minimal risk.

I have a big problem with performance in SQL 2022 on windows 2025. I do not know which is bigger issue windows or SQL. Some background. We are using **Proxmox** as Hypervisor. - HOST1 - DELL PowerEdge R7625, 2xAMD EPYC 9174F 4,10GHz, 1TB RAM, 16x64GB RAM RDIMM 4800MT/s, DISKS 6x1.6TB NVMe Gen4 dysk AG U.2 Enterprise - VM1 - Windows 2025, SQL 2022 socket-2, vCores 16, RAM 256 GB - HOST2 - Huawei 2488h-v5, 2 x Intel® Xeon® Gold 6244 3.6G, 512 GB RAM DDR4 - RDIMM 2933MT/s, DISKS - 5x1.9 GB NVMe - VM2 - Windows 2012R2 - multi instance SQL 2014, SQL 2017 socket-2, vCores 6, RAM 256 GB - VM3 - Windows 2019 - multi instance SQL 2017, SQL 2019, SQL 2022 socket-2, vCores 8, RAM 256 GB I have one query which shows that theres is sth wrong with What I have tried: - Comparison between other windows and sql versions and different hardware several queries - I have excluded hardware problem - tested same VM on different hosts Analyzing configuration on hardware, hypervisor, OS and SQL level - I have analyzed several queries which runs slower and they had all in common waits like MEMORY_ALLOCATION_EXT and i see this on this VM1, but o do not see those on VM2, VM3 - I have moved VM1 to Host2 to see if it helps - it didn't 6 I Have moved VM2 to Host 1 to see if it breaks sth ;) it didn't - I came to one conclusion that the problem is in SQL2022 on Windows 2022. **VM1 - SQL 2022** is always slow on all queries which has loops, cursors, large memory grants. I found this https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-query-perf-between-servers#diagnose-environment-differences And started to analyze this problem with this query only SET NOCOUNT ON DECLARE @spins INT = 0 DECLARE @start_time DATETIME = GETDATE(), @time_millisecond INT WHILE (@spins < 20000000) BEGIN SET @spins = @spins +1 END SELECT @time_millisecond = DATEDIFF(millisecond, @start_time, getdate()) SELECT @spins Spins, @time_millisecond Time_ms, @spins / @time_millisecond Spins_Per_ms I added some powershell scripts also $bf = [System.DateTime]::Now for ($i = 0; $i -le 20000000; $i++) {} $af = [System.DateTime]::Now Write-Host ($af - $bf).Milliseconds " milliseconds" Write-Host ($af - $bf).Seconds " Seconds" What i found was that this query runs significantly different on different enviroment :( Table of comparison: Using Brent's Ozar SP_BlitzFirst procedures EXEC master.dbo.sp_BlitzFirst @expertmode = 1 It showed that this session which is waiting its waits is SOS_SCHEDULER_YIELD (16133 ms) But when i run SELECT TOP 10 wait_type, wait_time_ms / 1000.0 AS wait_time_sec, 100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, signal_wait_time_ms / 1000.0 AS signal_wait_time_sec, waiting_tasks_count FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'SQLTRACE_BUFFER_FLUSH', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT') ORDER BY waiting_tasks_count DESC, wait_time_ms DESC; It shows that on this server I have problem with to many tasks wating for MEMORY_ALLOCATION_EXT RESERVED_MEMORY_ALLOCATION_EXT I tried to change VM1 to 24 processors to test this in VM if it will help but it does not help. If I look at percentage of waits whish is most common I see 87 % of SOS_SCHEDULER_YIELD, but it must be something else because it yields because of something :( Memory ? I will test new VMs with WS 2025, SQL2022 and WS2022 and SQL2022, WS2025 and SQL2019 to test how it acts.

The most typical corruption recovery scenario involves using a [full database backup](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver17) from before corruption struck and every [transaction log backup](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-ver17) between then and now. I have recently discovered [Transact-SQL snapshot backups](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-transact-sql-snapshot-backup?view=sql-server-ver17) . Like full backups, you can replay transaction logs on top of them. As this Microsoft diagram shows Does this suggest that Transact-SQL snapshot backups can be used to recover from database corruption, just like full database backups can?

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 the methods of defining the report path as described on the Microsoft site which is

http://server/ReportServer?/foldername/reportname¶metername= "filepath"

I've tried sever different ways to pass the file url in as a parameter but every time it comes up as an error >path of the item is not valid. The file exists on a network server and if I run the report manually from the report sever I can just pass in the network url as follows "\\\\\filename.txt" and it works as expected. When I try to run this by passing in the filepath into the url and running from a browser it tells my invalid path. Long story short, how do you pass in a network server filepath that is not local to the report server as a param?

I have an availability group setup on Sql Server 2022. It was working fine for some days until some strange problem happened. The workload that was querying data from secondary readonly replica, was showing invalid data. I have investigated the problem, and it turned out that the secondary database is not being synced with the primary and new changes was not present on it. The database was not reporting any problem related to the AG. The AG's dashboard was all green, the status of databases on primary and secondary was "Synchronized", and no problem in terms of resources (cpu, memory and disk) was detected on any nodes. After a restart of Sql Server on the secondary node, the syncing started again and it is working till now. This was a very disappointing experience, so I cannot trust this setup anymore. The automatic failover would fail for sure, if I did not detect the problem in time. Also scaling workloads on readonly replicas is now a very risky thing to do. Have you any experience like that before? Am I missing something here? Do you know any ways to monitor and detect the situation (the reporting of the database itself is not good enough, as it seems)?

Yesterday, I witnesses a running query in sp_WhoIsActive that I could not explain. It had the following properties: * It ran in a nested transaction. The first transaction bulk inserts into a temp table that is later given a unique clustered index. The second runs an UPDATE against a system-versioned table. * The UPDATE was running for hours despite usually taking less than seconds. 5 minutes at most. * There were multiple calls to this UPDATE in different sessions, all being blocked by one of them. * sp_WhoIsActive revealed that: * The query has no in-flight execution plan (@get_plans = 2) * **The block leader is almost always blocked on "SLEEP_TASK", but sometimes it isn't blocked at all. I recall that it seems to stay blocked on this for about 4ms.** * The query was still doing work, only reads as far as I could tell, but incredible slowly (from @delta_interval = 10) * My guess is that the query waits on SLEEP_TASK for some set amount of time, does one read, and then goes back to sleep. * All of the reads were logical, not physical. * The block leading query had so far done millions of reads, but the blocked queries had only done tens of thousands * The query was single-threaded (@get_task_info = 2) * open_tran_count was 3 * I cannot share the XML showing the locks, but I find it interesting that it shows that the block leader shows: * S lock on "DATABASE.ENCRYPTION_SCAN" * Sch-S lock on "METADATA.PLAN_GUIDE_HASH" * Lots of locks on both the table and its history table * tempdb locks all presumably taken by the transaction populating the temp table. * For the blocked queries, the lock XML showed that they were all waiting on an IX lock on the clustered index of the history table. This index is partitioned and not unique. The history table has no other indexes. * The main table has only a primary key. It is not partitioned. * I have confirmed that the history table is free of corruption. The text of the query this, but with the names changed.

UPDATE
    MyTable
SET
    MyTable.FloatCol = TempTable.FloatCol,
    MyTable.Date = TempTable.Date
FROM
   #TempTableWithUniqueClusteredIndex AS TempTable
JOIN
    TemporalTableWithJustAPrimaryKey AS MyTable
ON
    MyTable.PrimaryKeyInt = TempTable.PrimaryKeyInt;

Query Store holds an estimated plan for this query. It is of this shape. With this database dropping and creating script, I can get an actual execution plan of identical shape.

SET STATISTICS XML OFF;
IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK;
END;

-- =========================================
-- 1. Drop/Create sample DB and switch context
-- =========================================
USE master;
GO

ALTER DATABASE SampleDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE IF EXISTS SampleDB;
GO

CREATE DATABASE SampleDB;
GO

USE SampleDB;
GO

-- =========================================
-- 2. Partition function & scheme for history
--    (partitioning by ValidFrom date/time)
-- =========================================
CREATE PARTITION FUNCTION PF_ValidFrom (DATETIME2) 
AS RANGE LEFT FOR VALUES
    ('2015-01-01','2020-01-01','2025-01-01');
GO

CREATE PARTITION SCHEME PS_ValidFrom 
AS PARTITION PF_ValidFrom
    ALL TO ([PRIMARY]);
GO

-- =========================================
-- 3. Pre-create history table on scheme
-- =========================================
CREATE TABLE dbo.MainTableHistory
(
    PrimaryKeyInt INT        NOT NULL,
    FloatCol      FLOAT      NOT NULL,
    [Date]        DATE       NOT NULL,
    ValidFrom     DATETIME2  NOT NULL,
    ValidTo       DATETIME2  NOT NULL
)
ON PS_ValidFrom (ValidFrom);
GO

-- Partitioned, non-unique clustered index on history
CREATE CLUSTERED INDEX CX_MainTableHistory
    ON dbo.MainTableHistory(PrimaryKeyInt, ValidFrom)
    ON PS_ValidFrom (ValidFrom);
GO

-- =========================================
-- 4. Create main temporal table (unpartitioned)
--    with a DATE column and system-period cols
-- =========================================
CREATE TABLE dbo.TemporalTableWithJustAPrimaryKey
(
    PrimaryKeyInt INT         NOT NULL
      CONSTRAINT PK_TemporalTable PRIMARY KEY,
    FloatCol       FLOAT       NOT NULL,
    [Date]         DATE        NOT NULL,
    ValidFrom      DATETIME2   GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo        DATETIME2   GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
ON [PRIMARY];
GO

-- =========================================
-- 5. Seed MainTable with 1,000 rows
-- =========================================
INSERT INTO dbo.TemporalTableWithJustAPrimaryKey (PrimaryKeyInt, FloatCol, [Date])
SELECT TOP (1000)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
       RAND(CHECKSUM(NEWID())) * 100,                -- random float
       DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 365, '2020-01-01')
FROM sys.all_columns a
CROSS JOIN sys.all_columns b;
GO

-- =========================================
-- 6. Turn on system versioning pointing at our history
-- =========================================
ALTER TABLE dbo.TemporalTableWithJustAPrimaryKey
    SET (SYSTEM_VERSIONING = ON 
         (HISTORY_TABLE = dbo.MainTableHistory
         ,DATA_CONSISTENCY_CHECK = ON));
GO

-- =========================================
-- 7. Simulate nested transactions:
-- =========================================
BEGIN TRAN
    CREATE TABLE #TempTableWithUniqueClusteredIndex
    (
        PrimaryKeyInt INT   NOT NULL,
        FloatCol       FLOAT NOT NULL,
        [Date]         DATE  NOT NULL
    );

    INSERT INTO #TempTableWithUniqueClusteredIndex (PrimaryKeyInt, FloatCol, [Date])
    SELECT TOP (50)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
           RAND(CHECKSUM(NEWID())) * 100,
           DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 365, '2021-01-01')
    FROM sys.all_columns a
    CROSS JOIN sys.all_columns b;

    CREATE UNIQUE CLUSTERED INDEX CX_Temp_UC
      ON #TempTableWithUniqueClusteredIndex(PrimaryKeyInt);

BEGIN TRAN
    SET STATISTICS XML ON;

    UPDATE
        MyTable
    SET
        MyTable.FloatCol = TempTable.FloatCol,
        MyTable.Date = TempTable.Date
    FROM
       #TempTableWithUniqueClusteredIndex AS TempTable
    JOIN
        TemporalTableWithJustAPrimaryKey AS MyTable
    ON
        MyTable.PrimaryKeyInt = TempTable.PrimaryKeyInt;

ROLLBACK;

My question is this: **does anything that I have said above explain these SLEEP_TASK waits?** To [my knowledge](https://www.sqlskills.com/help/waits/sleep_task/) , such waits should be very rare on single-threaded queries.