We have a very strange problem with a new SQL Server 2019 on a 2 new physical machines:
Infrastructure: Starting a new installation of SQL Server 2019 Enterprise (15.0.2000.5 / X64 on Windows Server 2019 Standard 10.0 / Build 17763) on 2 new physical machines (for AlwaysOn replicas). The new machines are a LENOVO :
- ThinkSystem SR630 – [7X02CTO1WW]
- 1 CPU : 1 Xeon Gold 6208U – 2.90 Ghz (16 cores x 2 – hyperthreading)
- 256 Gb RAM (32 Gb x 8)
The problem appears systematically on the 2 new machines…
Test: The test that generate the malfunctions is as follows:
- creation of a database
- enlarging the database files and switching to SIMPLE recovery mode
- creation of a table
- filling the table with 1 million rows
- execution of the test request (calculation of aggregated time intervals – collapsing)
It is this last query (executed almost 10 times) which very often goes into error with the following message:
Msg 601, Level 12, State 1, Line … Could not continue scan with NOLOCK due to data movement.
Of course, at no time did we implement the
NOLOCK hint or the
READ UNCOMMITTED isolation level. But… information is written to the SQL Server event log:
- time-out for buffer latch in pages of tempdb
- sometimes a dump is generated
Example of « buffer latch » messages :
A time-out occurred while waiting for buffer latch — type 4, bp 00000292CE3D60C0, page 9:18634, stat 0x10b, database id: 2, allocation unit Id: 422212527063040/140737550352384, task 0x00000292AB073468 : 9, waittime 300 seconds, flags 0x100000001a, owning task 0x00000292AB06B848. Not continuing to wait. A time-out occurred while waiting for buffer latch — type 4, bp 00000292CE398340, page 6:10372, stat 0x10b, database id: 2, allocation unit Id: 422212527063040/140737550352384, task 0x00000292AB07B468 : 8, waittime 300 seconds, flags 0x1a, owning task 0x00000292AB073468. Not continuing to wait. A time-out occurred while waiting for buffer latch — type 4, bp 00000292CE3DC480, page 9:18655, stat 0x10b, database id: 2, allocation unit Id: 422212527063040/140737550352384, task 0x00000292AB703C28 : 12, waittime 300 seconds, flags 0x1a, owning task 0x00000292AB07B468. Not continuing to wait.
- Installing CU6 and after CU7 does not solve the problem
- Disk failure has been sweep of, because we create the database on the 3 different hard disk drives and the problem remains, even when we move the tempdb.
- The « buffer latch » information indicate likely a memory fault. But no hardware information appear about the RAM in the servers (we does a complete memory test).
- While soft NUMA is enable, we do complementary tests by disabling SOFT NUMA, but is does not solve the problem.
Some elements that seems to decrease the occurrence of the phenomenon:
- disabling SOFT NUMA (with hyperthreading enabled): the first 3 test are successes and last 7 are failure
- disabling hyperthreading (with SOFT NUMA disabled): on 10 tests only 1 is a failure
- disabling hyperthreading (with SOFT NUMA enabled): on 90 tests only 1 is a failure
Some elements that seems to solve the problem:
- setting MAXDOP 1
- recomputing statistics (UPDATE STATISTICS T_TIME_INTERVAL_TIV WITH FULLSCAN;)
We remove the RAM and replace it by another, with a different reference. The problem remains in worth...
Perhaps something to do with the new « Concurrent PFS updates » of SQL Server 2019... is there any trace flag that disable this new behaviour ?
========== SCRIPT ========== -- 1) creating database CREATE DATABASE DB_BENCH GO DECLARE @SQL NVARCHAR(max) = N''; SELECT @SQL = @SQL + N'ALTER DATABASE DB_BENCH MODIFY FILE (NAME = ''' + name + N''', SIZE = 10 GB, FILEGROWTH = 64 MB);' FROM DB_BENCH.sys.database_files; SET @SQL = @SQL + N'ALTER DATABASE DB_BENCH SET RECOVERY SIMPLE;' EXEC (@SQL); GO USE DB_BENCH GO -- 2) creating table and view CREATE TABLE T_TIME_INTERVAL_TIV (TIV_ID INT NOT NULL IDENTITY PRIMARY KEY, TIV_GROUP INT, TIV_DEBUT DATETIME2(0), TIV_FIN DATETIME2(0)) GO CREATE VIEW V AS SELECT TIV_GROUP AS id, TIV_DEBUT AS intime, TIV_FIN AS outtime FROM T_TIME_INTERVAL_TIV GO -- 3) inserting datas TRUNCATE TABLE T_TIME_INTERVAL_TIV; GO BULK INSERT T_TIME_INTERVAL_TIV FROM "C:\DATA_SQL\intervals.txt" WITH (KEEPIDENTITY , FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n'); GO -- 4) testing SET NOCOUNT ON; GO SET STATISTICS TIME ON; GO WITH T1 As (SELECT id, intime FROM V UNION ALL SELECT id, outtime FROM V), T2 As (SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY intime) NN, id, intime FROM T1 T1_1), T3 As (SELECT T2_1.NN - ROW_NUMBER() OVER(PARTITION BY T2_1.id ORDER BY T2_1.intime,T2_2.intime) NN1, T2_1.id, T2_1.intime intime, T2_2.intime outtime FROM T2 T2_1 INNER JOIN T2 T2_2 ON T2_1.id=T2_2.id And T2_1.NN=T2_2.NN-1 WHERE EXISTS (SELECT * FROM V S WHERE S.id=T2_1.id AND (S.intime < T2_2.intime AND S.outtime>T2_1.intime)) OR T2_1.intime = T2_2.intime) SELECT id, MIN(intime) intime, MAX(outtime) outtime FROM T3 GROUP BY id, NN1 ORDER BY id, intime, outtime; ========== END OF SCRIPT ==========
File to execute the script:
- dump 0005 SQL Server
- Query plans
3.1 raw data
3.2 after a UPDATE STATS in FULLSCAN mode
3.3 Comparison of the two plans
On 2020-09-14UTC16:00, complementary tests:
Using 2017 version of the database on the SQL Server 2019 as this:
USE master GO ALTER DATABASE DB_BENCH SET COMPATIBILITY_LEVEL = 140; GO
Results without any error over 180 execution of the same query
Using 2019 version and enabling TF 3925 or 3972 or the combination of 3925 and 3972, results with systematically error on all execution (with MAXDOP 0, hyperthrading on and NUMA soft on).