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