I have a very strange slow insert issue, here I am providing the table structure, and the insert statement used.
The database is clean, contains no other tables, no indices, no constraints, no triggers.
I tested this on two different machines, with high speed SSD drives, 8 cores 3.5 GHz processors. The server can read IO up to 700MB/s and can write up to 300MB/s
However, while executing the below insert statement, the processor is almost at 5% max and the IO operation less than 500KB/s which means that SQL Server is not using the server resources properly.
The maximum I can achieve is 250 row/s. The below insert statement is inserting 1000 rows and require 4 seconds to finish.
Any idea what is causing this slow in inserting?
SQL Server used is 2014 Developer Edition SP2
Thanks in advance.
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
Best Answer
Looking at physical IO is not a good indication of what work your query is doing. SQL Server does not write changes to disk immediately--it writes to the buffer cache (in memory), then flushes dirty pages to disk during the next checkpoint. Likely, all 1000 inserts will fit into memory, and no writes will happen on the data file until the next checkpoint.
Instead, try looking at the wait type that you are experiencing when you experience slowness for this operation.
If you are experiencing
WRITELOG
waits, try wrapping theWHILE
loop into an explicit transaction. This would reduce the number of times SQL Server flushes the log buffer to disk, and improve performance.You can then investigate
sys.dm_tran_database_transactions
andsys.dm_io_pending_io_requests
to zero in on whether the IO is pending at the OS or disk subsystem.Before digging too deeply into the DMVs, you might try looking into the following things:
Assuming your disk subsystem is not the bottleneck (and you seem confident that is not the problem), then the first place I would look would be at the number of VLFs in your log file.