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

Answer 1

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.

Answer 2

What Indexes do you have on the table (if any)? Any constraints using those indexes? Wondering if starting your Identity at a high number is causing an issue with the PK constraint/index at all during the insert. Any chance you can remove that identity property or the PK, then insert to see if that helps at all?

Also, are you truncating between your insert tests, or just dropping and re-creating the table?

Answer 3

Maybe log file contention or latency as you are performing 1,000 separate inserts. Far better to perform one set-based insert of 1,000 rows. You can achieve this by creating a numbers table and performing the insert via: -

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)
SELECT TOP 1000 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
FROM dbo.Numbers;

I would start with looking at wait stats to try and see what SQL Server is waiting on. With that information you can diagnose the root cause and implement a solution. This will help with looking at your wait stats http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

You can use a query from the SQL Server Diagnostic Information Queries to look at disk latency, such as: -

SELECT DB_NAME(fs.database_id) AS [Database Name], CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],
CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms],
CONVERT(DECIMAL(18,2), mf.size/128.0) AS [File Size (MB)], mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads, 
fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls], fs.num_of_reads + fs.num_of_writes AS [total_io],
io_stall_queued_read_ms AS [Resource Governor Total Read IO Latency (ms)], io_stall_queued_write_ms AS [Resource Governor Total Write IO Latency (ms)] 
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);

Another option would be to run Diskspd to test your storage latency.