SQL Server 2014 慢速插入

我有一個非常奇怪的慢插入問題，這裡我提供了表結構和使用的插入語句。

數據庫是乾淨的，不包含其他表，沒有索引，沒有約束，沒有觸發器。

我在兩台不同的機器上進行了測試，這些機器配備了高速 SSD 驅動器、8 核 3.5 GHz 處理器。伺服器讀取IO最高700MB/s，寫入最高300MB/s

但是，在執行下面的插入語句時，處理器幾乎處於最大 5% 並且 IO 操作低於 500KB/s，這意味著 SQL Server 沒有正確使用伺服器資源。

我可以達到的最大值是 250 行/秒。下面的插入語句插入 1000 行，需要 4 秒才能完成。

知道是什麼導致插入速度緩慢嗎？

使用的 SQL Server 是 2014 Developer Edition SP2

提前致謝。

CREATE TABLE [dbo].[MyTable](
   [ID] [int] IDENTITY(1666811137,1) NOT NULL,
   [col0] [smallint] NULL,
   [col1] [smallmoney] NOT NULL,
   [col2] [tinyint] NOT NULL,
   [col3] [tinyint] NULL,
   [col4] [tinyint] NOT NULL,
   [col5] [tinyint] NOT NULL,
   [col6] [datetime] NOT NULL,
   [col7] [varchar](15) NOT NULL,
   [col8] [varchar](20) NOT NULL,
   [col9] [tinyint] NOT NULL,
   [col10] [tinyint] NULL,
   [col11] [tinyint] NULL,
   [col12] [tinyint] NULL,
   [col13] [tinyint] NULL,
   [col14] [tinyint] NULL,
   [col15] [tinyint] NULL,
   [col16] [bit] NULL,
   [col17] [smallint] NULL,
   [col18] [varchar](17) NULL,
   [col19] [smallint] NULL,
   [col20] [tinyint] NULL,
   [col21] [varbinary](254) NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



/********* INSERT *********/
DECLARE @i int
SET @i = 0

WHILE @i < 1000
BEGIN
   SET @i = @i + 1

   INSERT INTO MyTable (col3, col1, col2, col4, col5, col6, col7, col8, col9, col21, col10, col11, col12, col13, col14, col18, col15, col16, col0, col19, col17, col20)
   VALUES (5, 0, 1, 1, 1, GETDATE(), 'ABC', '1234567890', 0, CAST('0x74657374' AS VARBINARY(MAX)), 1, 1, 1, 1, 0, 'VAL', 1, 0, 37, 37, 2, 0)
END

查看物理 IO 並不能很好地表明您的查詢正在做什麼。SQL Server 不會立即將更改寫入磁碟——它會寫入緩衝區記憶體（在記憶體中），然後在下一個檢查點期間將臟頁刷新到磁碟。很可能，所有 1000 次插入都將適合記憶體，並且在下一個檢查點之前不會對數據文件進行任何寫入。

相反，當您遇到此操作緩慢時，請嘗試查看您遇到的等待類型。

如果您遇到WRITELOG等待，請嘗試將WHILE循環包裝到顯式事務中。這將減少 SQL Server 將日誌緩衝區刷新到磁碟的次數，並提高性能。

然後，您可以調查IO 是否在作業系統或磁碟子系統中掛起，sys.dm_tran_database_transactions並將其歸零。sys.dm_io_pending_io_requests

在深入研究 DMV 之前，您可以嘗試查看以下內容：

• 您的日誌文件大小是否合適？你有適當數量的 VLF嗎？
• 使用 Perfmon 查看 PhysicalDisk 對像以查看 Avg。磁碟秒/讀取，平均。磁碟秒/寫和平均。磁碟隊列長度。IO 花費的時間是否超過 15 毫秒？您的隊列長度是否大於 1？
• 將事務日誌移動到不同的物理磁碟以隔離 IO。

假設您的磁碟子系統不是瓶頸（並且您似乎確信這不是問題），那麼我首先要看的是您的日誌文件中的 VLF 數量。

你有什麼索引（如果有的話）？使用這些索引有什麼限制嗎？想知道以高數字啟動您的身份是否會在插入過程中導致 PK 約束/索引出現問題。您是否有機會刪除該身份屬性或 PK，然後插入以查看是否有幫助？

另外，您是在插入測試之間截斷，還是只是刪除並重新創建表？

引用自：https://dba.stackexchange.com/questions/147774