Sql-Server
為什麼 CHECKDB 讀取具有記憶體優化表的數據庫上的事務日誌文件？
tl;dr：為什麼 CHECKDB 讀取具有記憶體優化表的使用者數據庫的事務日誌？
似乎 CHECKDB 在檢查我的一個數據庫時正在讀取使用者數據庫的事務日誌文件 - 特別是使用記憶體中 OLTP 表的數據庫。
該數據庫的 CHECKDB 仍然在合理的時間內完成，所以我主要只是對這種行為感到好奇；但這絕對是該實例上所有數據庫中 CHECKDB 的最長持續時間。
在查看 Paul Randal 的史詩“ CHECKDB From Every Angle：所有 CHECKDB 階段的完整描述”時，我看到 SQL 2005 之前的 CHECKDB用於讀取日誌以獲得數據庫的一致視圖。但由於這是 2016 年，它使用內部數據庫快照。
但是，快照的先決條件之一是：
源數據庫不得包含 MEMORY_OPTIMIZED_DATA 文件組
我的使用者數據庫具有這些文件組之一，因此看起來快照不在桌面上。
根據CHECKDB 文件：
如果無法創建快照，或者指定了 TABLOCK，則 DBCC CHECKDB 獲取鎖以獲得所需的一致性。在這種情況下，需要獨占數據庫鎖來執行分配檢查，並且需要共享表鎖來執行表檢查。
好的，所以我們正在執行數據庫和表鎖定而不是快照。但這仍然不能解釋為什麼它必須讀取事務日誌。那麼給了什麼？
我在下面提供了一個腳本來重現該場景。它用於
sys.dm_io_virtual_file_stats辨識日誌文件讀取。請注意，大多數情況下它會讀取日誌的一小部分 (480 KB)，但偶爾會讀取更多 (48.2 MB)。在我的生產場景中，它每晚在我們執行 CHECKDB 時讀取大部分日誌文件（大約 2 GB 文件中的 1.3 GB）。
這是到目前為止我使用腳本獲得的輸出範例：
collection_time num_of_reads num_of_bytes_read 2018-04-04 15:12:29.203 106 50545664或這個：
collection_time num_of_reads num_of_bytes_read 2018-04-04 15:25:14.227 1 491520如果我用正常表替換記憶體優化對象，輸出如下所示：
collection_time num_of_reads num_of_bytes_read 2018-04-04 15:21:03.207 0 0為什麼 CHECKDB 讀取日誌文件？尤其是，為什麼它偶爾會讀取日誌文件的更大部分？
這是實際的腳本：
-- let's have a fresh DB USE [master]; IF (DB_ID(N'LogFileRead_Test') IS NOT NULL) BEGIN ALTER DATABASE [LogFileRead_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [LogFileRead_Test]; END GO CREATE DATABASE [LogFileRead_Test] GO ALTER DATABASE [LogFileRead_Test] MODIFY FILE ( NAME = LogFileRead_Test_log, SIZE = 128MB ); -- Hekaton-yeah, I want memory optimized data GO ALTER DATABASE [LogFileRead_Test] ADD FILEGROUP [LatencyTestInMemoryFileGroup] CONTAINS MEMORY_OPTIMIZED_DATA; GO ALTER DATABASE [LogFileRead_Test] ADD FILE ( NAME = [LatencyTestInMemoryFile], FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\LogFileRead_Test_SessionStateInMemoryFile' ) TO FILEGROUP [LatencyTestInMemoryFileGroup]; GO USE [LogFileRead_Test] GO CREATE TYPE [dbo].[InMemoryIdTable] AS TABLE ( [InMemoryId] NVARCHAR (88) COLLATE Latin1_General_100_BIN2 NOT NULL, PRIMARY KEY NONCLUSTERED HASH ([InMemoryId]) WITH (BUCKET_COUNT = 240)) WITH (MEMORY_OPTIMIZED = ON); GO CREATE TABLE [dbo].[InMemoryStuff] ( [InMemoryId] NVARCHAR (88) COLLATE Latin1_General_100_BIN2 NOT NULL, [Created] DATETIME2 (7) NOT NULL, CONSTRAINT [PK_InMemoryStuff_InMemoryId] PRIMARY KEY NONCLUSTERED HASH ([InMemoryId]) WITH (BUCKET_COUNT = 240) ) WITH (MEMORY_OPTIMIZED = ON); GO -- RBAR is the new black (we need some logs to read) declare @j int = 0; while @j < 100000 begin INSERT INTO [dbo].[InMemoryStuff](InMemoryId, Created) VALUES ('Description' + CAST(@j as varchar), GETDATE()); set @j = @j + 1; end -- grab a baseline of virtual file stats to be diff'd later select f.num_of_reads, f.num_of_bytes_read into #dm_io_virtual_file_stats from sys.dm_io_virtual_file_stats(default, default) f where database_id = db_id('LogFileRead_Test') and file_id = FILE_IDEX('LogFileRead_Test_log'); -- hands off my log file, CHECKDB! GO DBCC CHECKDB ([LogFileRead_Test]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY; -- grab the latest virtual file stats, and compare with the previous capture GO select f.num_of_reads, f.num_of_bytes_read into #checkdb_stats from sys.dm_io_virtual_file_stats(default, default) f where database_id = db_id('LogFileRead_Test') and file_id = FILE_IDEX('LogFileRead_Test_log'); select collection_time = GETDATE() , num_of_reads = - f.num_of_reads + t.num_of_reads , num_of_bytes_read = - f.num_of_bytes_read + t.num_of_bytes_read into #dm_io_virtual_file_stats_diff from #dm_io_virtual_file_stats f, #checkdb_stats t; drop table #checkdb_stats; drop table #dm_io_virtual_file_stats; -- CHECKDB ignored my comment select collection_time, num_of_reads, num_of_bytes_read from #dm_io_virtual_file_stats_diff d order by d.collection_time; drop table #dm_io_virtual_file_stats_diff; -- I was *not* raised in a barn USE [master]; ALTER DATABASE [LogFileRead_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [LogFileRead_Test];由於這個 repro 通常只生成 1 或 106 個日誌文件讀取，我想我會使用 file_read 和 file_read_completed 擴展事件會話深入研究 1。
name timestamp mode offset database_id file_id size duration file_read 2018-04-06 10:51:11.1098141 Contiguous 72704 9 2 0 NULL file_read_completed 2018-04-06 10:51:11.1113345 Contiguous 72704 9 2 491520 1以下是有關這些偏移量的上下文的 VLF 詳細資訊 (
DBCC LOGINFO()) 等：RecoveryUnitId FileId FileSize StartOffset FSeqNo Status Parity CreateLSN 0 2 2031616 8192 34 2 64 0 0 2 2031616 2039808 35 2 64 0 0 2 2031616 4071424 36 2 64 0 0 2 2285568 6103040 37 2 64 0 0 2 15728640 8388608 38 2 64 34000000005200001 0 2 15728640 24117248 39 2 64 34000000005200001 0 2 15728640 39845888 40 2 64 34000000005200001 0 2 15728640 55574528 0 0 0 34000000005200001 0 2 15728640 71303168 0 0 0 34000000005200001 0 2 15728640 87031808 0 0 0 34000000005200001 0 2 15728640 102760448 0 0 0 34000000005200001 0 2 15728640 118489088 0 0 0 34000000005200001因此，CHECKDB 操作：
- 開始將 63 KB（64,512 字節）讀入第一個 VLF，
- 讀取 480 KB（491,520 字節），以及
- 未讀取 VLF的最後 1441 KB（1,475,584 字節）
我還擷取了呼叫堆棧，以防它們有幫助。
file_read 呼叫堆棧：
(00007ffd`999a0860) sqlmin!XeSqlPkg::file_read::Publish+0x1dc | (00007ffd`999a0b40) sqlmin!XeSqlPkg::file_read_enqueued::Publish (00007ffd`9a825e30) sqlmin!FireReadEvent+0x118 | (00007ffd`9a825f60) sqlmin!FireReadEnqueuedEvent (00007ffd`9980b500) sqlmin!FCB::AsyncRead+0x74d | (00007ffd`9980b800) sqlmin!FCB::AsyncReadInternal (00007ffd`9970e9d0) sqlmin!SQLServerLogMgr::LogBlockReadAheadAsync+0x6a6 | (00007ffd`9970ec00) sqlmin!LBH::Destuff (00007ffd`9970a6d0) sqlmin!LogConsumer::GetNextLogBlock+0x1591 | (00007ffd`9970ab70) sqlmin!LogPoolPrivateCacheBufferMgr::Lookup (00007ffd`9a9fcbd0) sqlmin!SQLServerLogIterForward::GetNext+0x258 | (00007ffd`9a9fd2d0) sqlmin!SQLServerLogIterForward::GetNextBlock (00007ffd`9aa417f0) sqlmin!SQLServerCOWLogIterForward::GetNext+0x2b | (00007ffd`9aa418c0) sqlmin!SQLServerCOWLogIterForward::StartScan (00007ffd`9aa64210) sqlmin!RecoveryMgr::AnalysisPass+0x83b | (00007ffd`9aa65100) sqlmin!RecoveryMgr::AnalyzeLogRecord (00007ffd`9aa5ed50) sqlmin!RecoveryMgr::PhysicalRedo+0x233 | (00007ffd`9aa5f790) sqlmin!RecoveryMgr::PhysicalCompletion (00007ffd`9aa7fd90) sqlmin!RecoveryUnit::PhysicalRecovery+0x358 | (00007ffd`9aa802c0) sqlmin!RecoveryUnit::CompletePhysical (00007ffd`9a538b90) sqlmin!StartupCoordinator::NotifyPhaseStart+0x3a | (00007ffd`9a538bf0) sqlmin!StartupCoordinator::NotifyPhaseEnd (00007ffd`9a80c430) sqlmin!DBTABLE::ReplicaCreateStartup+0x2f4 | (00007ffd`9a80c820) sqlmin!DBTABLE::RefreshPostRecovery (00007ffd`9a7ed0b0) sqlmin!DBMgr::SyncAndLinkReplicaRecoveryPhase+0x890 | (00007ffd`9a7edff0) sqlmin!DBMgr::DetachDB (00007ffd`9a7f2cd0) sqlmin!DBMgr::CreatePhasedTransientReplica+0x869 | (00007ffd`9a7f3630) sqlmin!DBMgr::StrandTransientReplica (00007ffd`9a7f2ae0) sqlmin!DBMgr::CreateTransientReplica+0x118 | (00007ffd`9a7f2cd0) sqlmin!DBMgr::CreatePhasedTransientReplica (00007ffd`99ec6d30) sqlmin!DBDDLAgent::CreateReplica+0x1b5 | (00007ffd`99ec6f90) sqlmin!FSystemDatabase (00007ffd`9abaaeb0) sqlmin!UtilDbccCreateReplica+0x82 | (00007ffd`9abab000) sqlmin!UtilDbccDestroyReplica (00007ffd`9ab0d7e0) sqlmin!UtilDbccCheckDatabase+0x994 | (00007ffd`9ab0ffd0) sqlmin!UtilDbccRetainReplica (00007ffd`9ab0cfc0) sqlmin!DbccCheckDB+0x22d | (00007ffd`9ab0d380) sqlmin!DbccCheckFilegroup (00007ffd`777379c0) sqllang!DbccCommand::Execute+0x193 | (00007ffd`77737d70) sqllang!DbccHelp (00007ffd`777e58d0) sqllang!CStmtDbcc::XretExecute+0x889 | (00007ffd`777e6250) sqllang!UtilDbccSetPermissionFailure (00007ffd`76b02eb0) sqllang!CMsqlExecContext::ExecuteStmts<1,1>+0x40d | (00007ffd`76b03410) sqllang!CSQLSource::CleanupCompileXactState (00007ffd`76b03a60) sqllang!CMsqlExecContext::FExecute+0xa9e | (00007ffd`76b043d0) sqllang!CCacheObject::Release (00007ffd`76b03430) sqllang!CSQLSource::Execute+0x981 | (00007ffd`76b039b0) sqllang!CSQLLock::Cleanupfile_read_completed 呼叫堆棧：
(00007ffd`99995cc0) sqlmin!XeSqlPkg::file_read_completed::Publish+0x1fc | (00007ffd`99995fe0) sqlmin!XeSqlPkg::file_write_completed::Publish (00007ffd`9a826630) sqlmin!FireIoCompletionEventLong+0x227 | (00007ffd`9a8269c0) sqlmin!IoRequestDispenser::Dump (00007ffd`9969bee0) sqlmin!FCB::IoCompletion+0x8e | (00007ffd`9969c180) sqlmin!IoRequestDispenser::Put (00007ffd`beaa11e0) sqldk!IOQueue::CheckForIOCompletion+0x426 | (00007ffd`beaa1240) sqldk!SystemThread::GetCurrentId (00007ffd`beaa15b0) sqldk!SOS_Scheduler::SwitchContext+0x173 | (00007ffd`beaa18a0) sqldk!SOS_Scheduler::Switch (00007ffd`beaa1d00) sqldk!SOS_Scheduler::SuspendNonPreemptive+0xd3 | (00007ffd`beaa1db0) sqldk!SOS_Scheduler::ResumeNoCuzz (00007ffd`99641720) sqlmin!EventInternal<SuspendQueueSLock>::Wait+0x1e7 | (00007ffd`99641ae0) sqlmin!SOS_DispatcherPool<DispatcherWorkItem,DispatcherWorkItem,SOS_DispatcherQueue<DispatcherWorkItem,0,DispatcherWorkItem>,DispatcherPoolConfig,void * __ptr64>::GetDispatchers (00007ffd`9aa437c0) sqlmin!SQLServerLogMgr::CheckLogBlockReadComplete+0x1e6 | (00007ffd`9aa44670) sqlmin!SQLServerLogMgr::ValidateBlock (00007ffd`9970a6d0) sqlmin!LogConsumer::GetNextLogBlock+0x1b37 | (00007ffd`9970ab70) sqlmin!LogPoolPrivateCacheBufferMgr::Lookup (00007ffd`9a9fcbd0) sqlmin!SQLServerLogIterForward::GetNext+0x258 | (00007ffd`9a9fd2d0) sqlmin!SQLServerLogIterForward::GetNextBlock (00007ffd`9aa417f0) sqlmin!SQLServerCOWLogIterForward::GetNext+0x2b | (00007ffd`9aa418c0) sqlmin!SQLServerCOWLogIterForward::StartScan (00007ffd`9aa64210) sqlmin!RecoveryMgr::AnalysisPass+0x83b | (00007ffd`9aa65100) sqlmin!RecoveryMgr::AnalyzeLogRecord (00007ffd`9aa5ed50) sqlmin!RecoveryMgr::PhysicalRedo+0x233 | (00007ffd`9aa5f790) sqlmin!RecoveryMgr::PhysicalCompletion (00007ffd`9aa7fd90) sqlmin!RecoveryUnit::PhysicalRecovery+0x358 | (00007ffd`9aa802c0) sqlmin!RecoveryUnit::CompletePhysical (00007ffd`9a538b90) sqlmin!StartupCoordinator::NotifyPhaseStart+0x3a | (00007ffd`9a538bf0) sqlmin!StartupCoordinator::NotifyPhaseEnd (00007ffd`9a80c430) sqlmin!DBTABLE::ReplicaCreateStartup+0x2f4 | (00007ffd`9a80c820) sqlmin!DBTABLE::RefreshPostRecovery (00007ffd`9a7ed0b0) sqlmin!DBMgr::SyncAndLinkReplicaRecoveryPhase+0x890 | (00007ffd`9a7edff0) sqlmin!DBMgr::DetachDB (00007ffd`9a7f2cd0) sqlmin!DBMgr::CreatePhasedTransientReplica+0x869 | (00007ffd`9a7f3630) sqlmin!DBMgr::StrandTransientReplica (00007ffd`9a7f2ae0) sqlmin!DBMgr::CreateTransientReplica+0x118 | (00007ffd`9a7f2cd0) sqlmin!DBMgr::CreatePhasedTransientReplica (00007ffd`99ec6d30) sqlmin!DBDDLAgent::CreateReplica+0x1b5 | (00007ffd`99ec6f90) sqlmin!FSystemDatabase (00007ffd`9abaaeb0) sqlmin!UtilDbccCreateReplica+0x82 | (00007ffd`9abab000) sqlmin!UtilDbccDestroyReplica (00007ffd`9ab0d7e0) sqlmin!UtilDbccCheckDatabase+0x994 | (00007ffd`9ab0ffd0) sqlmin!UtilDbccRetainReplica (00007ffd`9ab0cfc0) sqlmin!DbccCheckDB+0x22d | (00007ffd`9ab0d380) sqlmin!DbccCheckFilegroup (00007ffd`777379c0) sqllang!DbccCommand::Execute+0x193 | (00007ffd`77737d70) sqllang!DbccHelp這些堆棧跟踪與Max 的回答相關，表明 CHECKDB 正在使用內部快照，儘管存在 Hekaton 表。
我讀過快照執行恢復以撤消未送出的事務：
未送出的事務在新創建的數據庫快照中回滾，因為數據庫引擎在創建快照後執行恢復（數據庫中的事務不受影響）。
但這仍然不能解釋為什麼在我的生產場景中經常讀取大量日誌文件（偶爾在此處提供的重現中）。我認為我的應用程序在給定時間沒有那麼多正在進行的交易，而且這裡的重現中肯定沒有任何交易。
即使 SQL Server 文件聲明具有“記憶體中”表的數據庫不支持快照，但
DBCC CHECKDB仍然可以創建所需的“內部”快照，因為 checkdb 操作不會觸及記憶體中的表，並且快照僅擷取更改到磁碟表。據推測，微軟選擇阻止使用者在具有記憶體表的數據庫上創建快照，因為他們需要複製記憶體中的結構才能使快照真正成為正常的、以使用者為中心的完整快照。為快照複製記憶體中的表很容易導致伺服器記憶體不足，這不是A Good Thing™
您可以通過在執行時觀察主數據庫數據文件所在的數據文件夾來證明正在創建內部 DBCC 快照
DBCC CHECKDB。如果創建了內部快照，您將看到一個名為的文件LogFileRead_Test.mdf_MSSQL_DBCC7（7可能不同 - 它代表您數據庫的數據庫 ID）。創建快照文件後，SQL Server 必須在數據庫上執行恢復，以使其進入 DBCC CHECKDB 執行所需的一致狀態。您看到的任何日誌讀取操作都可能是該恢復過程的結果。我建構了一個用於檢查多個
DBCC CHECKDB操作的輸出的快速裝置，它證明如果 checkdbs 之間沒有事務，則沒有日誌文件讀取。USE master; SET IMPLICIT_TRANSACTIONS OFF; USE [master]; IF (DB_ID(N'LogFileRead_Test') IS NOT NULL) BEGIN ALTER DATABASE [LogFileRead_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [LogFileRead_Test]; END CREATE DATABASE [LogFileRead_Test] ALTER DATABASE [LogFileRead_Test] MODIFY FILE ( NAME = LogFileRead_Test_log, SIZE = 128MB ); ALTER DATABASE [LogFileRead_Test] ADD FILEGROUP [LatencyTestInMemoryFileGroup] CONTAINS MEMORY_OPTIMIZED_DATA; ALTER DATABASE [LogFileRead_Test] ADD FILE ( NAME = [LatencyTestInMemoryFile], FILENAME = 'C:\temp\LogFileRead_Test_SessionStateInMemoryFile' ) TO FILEGROUP [LatencyTestInMemoryFileGroup]; GO USE LogFileRead_Test; CREATE TABLE [dbo].[InMemoryStuff] ( [InMemoryId] NVARCHAR (88) COLLATE Latin1_General_100_BIN2 NOT NULL, [Created] DATETIME2 (7) NOT NULL, CONSTRAINT [PK_InMemoryStuff_InMemoryId] PRIMARY KEY NONCLUSTERED HASH ([InMemoryId]) WITH (BUCKET_COUNT = 240) ) WITH (MEMORY_OPTIMIZED = ON); ;WITH src AS ( SELECT n.Num FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))n(Num) ) INSERT INTO [dbo].[InMemoryStuff] (InMemoryId, Created) SELECT 'Description' + CONVERT(varchar(30) , ((s1.Num * 10000) + (s2.Num * 1000) + (s3.Num * 100) + (s4.Num * 10) + (s5.Num))) , GETDATE() FROM src s1 CROSS JOIN src s2 CROSS JOIN src s3 CROSS JOIN src s4 CROSS JOIN src s5; USE master; DECLARE @cmd nvarchar(max); DECLARE @msg nvarchar(1000); DECLARE @l int; DECLARE @m int; SET @m = 10; SET @l = 1; IF OBJECT_ID(N'tempdb..#vfs', N'U') IS NOT NULL DROP TABLE #vfs; CREATE TABLE #vfs ( vfs_run int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED , collection_time datetime2(7) , num_of_reads bigint , num_of_bytes_read bigint ); WHILE @l <= @m BEGIN SET @msg = N'loop ' + CONVERT(nvarchar(10), @l); RAISERROR (@msg, 0, 1) WITH NOWAIT; SET @cmd = 'USE [LogFileRead_Test]; -- grab a baseline of virtual file stats to be diff''d later select f.num_of_reads, f.num_of_bytes_read into #dm_io_virtual_file_stats from sys.dm_io_virtual_file_stats(default, default) f where database_id = db_id(''LogFileRead_Test'') and file_id = FILE_IDEX(''LogFileRead_Test_log''); DBCC CHECKDB ([LogFileRead_Test]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY; -- grab the latest virtual file stats, and compare with the previous capture select f.num_of_reads, f.num_of_bytes_read into #checkdb_stats from sys.dm_io_virtual_file_stats(default, default) f where database_id = db_id(''LogFileRead_Test'') and file_id = FILE_IDEX(''LogFileRead_Test_log''); select collection_time = GETDATE() , num_of_reads = - f.num_of_reads + t.num_of_reads , num_of_bytes_read = - f.num_of_bytes_read + t.num_of_bytes_read into #dm_io_virtual_file_stats_diff from #dm_io_virtual_file_stats f, #checkdb_stats t; --drop table #checkdb_stats; --drop table #dm_io_virtual_file_stats; -- CHECKDB ignored my comment select collection_time, num_of_reads, num_of_bytes_read from #dm_io_virtual_file_stats_diff d order by d.collection_time; --drop table #dm_io_virtual_file_stats_diff; '; INSERT INTO #vfs (collection_time, num_of_reads, num_of_bytes_read) EXEC sys.sp_executesql @cmd; SET @l += 1; END USE master; SET @cmd = 'USE [master]; ALTER DATABASE [LogFileRead_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [LogFileRead_Test]; '; EXEC sys.sp_executesql @cmd; SELECT * FROM #vfs ORDER BY vfs_run;結果：
╔═════════╦═════════════════════════════╦══════════════╦═══════════════════╗ ║ vfs_run ║ collection_time ║ num_of_reads ║ num_of_bytes_read ║ ╠═════════╬═════════════════════════════╬══════════════╬═══════════════════╣ ║ 1 ║ 2018-04-06 15:53:37.6566667 ║ 1 ║ 491520 ║ ║ 2 ║ 2018-04-06 15:53:37.8300000 ║ 0 ║ 0 ║ ║ 3 ║ 2018-04-06 15:53:38.0166667 ║ 0 ║ 0 ║ ║ 4 ║ 2018-04-06 15:53:38.1866667 ║ 0 ║ 0 ║ ║ 5 ║ 2018-04-06 15:53:38.3766667 ║ 0 ║ 0 ║ ║ 6 ║ 2018-04-06 15:53:38.5633333 ║ 0 ║ 0 ║ ║ 7 ║ 2018-04-06 15:53:38.7333333 ║ 0 ║ 0 ║ ║ 8 ║ 2018-04-06 15:53:38.9066667 ║ 0 ║ 0 ║ ║ 9 ║ 2018-04-06 15:53:39.0933333 ║ 0 ║ 0 ║ ║ 10 ║ 2018-04-06 15:53:39.2800000 ║ 0 ║ 0 ║ ╚═════════╩═════════════════════════════╩══════════════╩═══════════════════╝此外，您可能希望使用一種簡單的基於集合的方法，而不是使用 RBAR 方法將數據插入測試表，如下所示：
;WITH src AS ( SELECT n.Num FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))n(Num) ) INSERT INTO [dbo].[InMemoryStuff] (InMemoryId, Created) SELECT 'Description' + CONVERT(varchar(30) , ((s1.Num * 10000) + (s2.Num * 1000) + (s3.Num * 100) + (s4.Num * 10) + (s5.Num))) , GETDATE() FROM src s1 CROSS JOIN src s2 CROSS JOIN src s3 CROSS JOIN src s4 CROSS JOIN src s5;在我的測試中，它在 3 秒內填滿表格，而 RBAR 方法需要很長時間。此外，您的程式碼中的漂亮註釋讓我哈哈大笑。