How To Measure Performance of INSERT Statements in SQL Server?

When indexes are defined for a table, each time a new row is inserted to the table, all the indexes must be updated. This extra update work could slow down the insert statement.

To find out how much slower the INSERT statements will be on tables with indexes, you need to measure the performance of INSERT statements on tables without indexes. This tutorial shows you how to measure the time spend on inserting 100000 rows to fyi_links without indexes:

USE FyiCenterData;
GO

-- Drop the old table, if needed
DROP TABLE fyi_links;
GO

-- Create a table with no indexes
CREATE TABLE fyi_links (
  id INT,
  url VARCHAR(80) NOT NULL,
  notes VARCHAR(1024),
  counts INT,
  created DATETIME NOT NULL DEFAULT(getdate())
);
GO

-- Empty the table if needed
DELETE FROM fyi_links;
GO

-- Performance test of INSERT
DECLARE @start_time DATETIME, @end_time DATETIME;
SET @start_time = GETDATE();
INSERT INTO fyi_links 
   SELECT id, rand_string, REVERSE(rand_string), 
      rand_integer, rand_datetime
   FROM fyi_random
SET @end_time = GETDATE();
PRINT 'Milliseconds used: '+CONVERT(VARCHAR(20),
   DATEDIFF(MILLISECOND,@start_time,@end_time));
GO

-- First time
(100000 row(s) affected)
Milliseconds used: 18013

-- Second time
(100000 row(s) affected)
Milliseconds used: 736

-- Third time
(100000 row(s) affected)
Milliseconds used: 780

⇒ Index Slowing Down INSERT Statements in SQL Server

⇐ Creating a Large Table with Random Data for Indexes in SQL Server

⇑ Understanding and Managing Indexes in SQL Server

⇑⇑ SQL Server Database Tutorials