完整恢复模型和制表块插入日志记录

在测试一些查询时，我将数据库的恢复模式设置为完整，并运行了两个相同的 1M 行插入，有和没有TABLOCK.

在一个TABLOCK方面，我绕过9295了 sql server 2008 实例上8714的日志记录，并绕过了 SQL Server 2017 实例上的日志记录。

在不使用 tablock 的情况下运行插入时，我绕过10356592008 年实例的1068599记录和 2017 年实例的记录。

在 sql server 2008 上进行测试的原因是为了匹配数据加载性能指南关于 ML 操作恢复模型的声明：

仅当您的数据库处于大容量日志记录或简单恢复模式时，最少记录的操作才可用。

那么，如果不是最小日志记录，我在这里看到的是什么？

Use DatabaseName

ALTER DATABASE DatabaseName SET RECOVERY FULL;
GO
BACKUP DATABASE DatabaseName TO DISK = '\\location\DatabaseName.bak';

BACKUP LOG DatabaseName TO DISK = '\\location\DatabaseName_log.trn';
GO

IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Accounts;
END;
GO

CREATE TABLE dbo.Accounts( AccountID INT PRIMARY KEY NOT NULL,
                            AccountName varchar(255),
                            DateCreated DATETIME2);

-- Insert 1M Rows into dbo.Account without TABLOCK
GO
SET STATISTICS IO, TIME ON;
INSERT INTO dbo.Accounts  (AccountID,AccountName,DateCreated)
SELECT TOP(1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        'Name N ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(255)),
        DATEADD(MINUTE,-ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),GETDATE())
FROM MASTER..SPT_VALUES SPT1
CROSS APPLY MASTER..SPT_VALUES SPT2;
GO
-- check the amount of records in the log file
SELECT count(*)
FROM
fn_dbLog(NULL,NULL);
--1035659 rows
GO

-- clear the log
BACKUP LOG DatabaseName to disk = '\\location\DatabaseName_log2.trn';

GO

--drop the table

IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Accounts;
END;
GO
-- create the table
CREATE TABLE dbo.Accounts( AccountID INT PRIMARY KEY NOT NULL,
                            AccountName varchar(255),
                            DateCreated DATETIME2);

-- Insert 1M Rows into dbo.Account WITH TABLOCK
GO
SET STATISTICS IO, TIME ON;
INSERT INTO dbo.Accounts WITH(TABLOCK) (AccountID,AccountName,DateCreated)
SELECT TOP(1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        'Name N ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(255)),
        DATEADD(MINUTE,-ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),GETDATE())
FROM MASTER..SPT_VALUES SPT1
CROSS APPLY MASTER..SPT_VALUES SPT2;
GO
-- check the amount of records in the log file
SELECT count(*)
FROM
fn_dbLog(NULL,NULL);

--9295 rows