SQL Server 2014 slow insert

Question

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

asked 2016-08-24 by Abed

---

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 the WHILE 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 and sys.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:

• Is your log file properly sized? Do you have an appropriate number of VLFs?
• Use Perfmon to look at the PhysicalDisk object to look at Avg. Disk sec/Read, Avg. Disk sec/Write, and Avg. Disk Queue Length. Are IOs taking longer than 15ms? Is your queue length larger than 1?
• Move the transaction log to a different physical disk to segregate IO.

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.

answered 2016-08-25 by Andy Mallon