How to run an input operation test with INSERT statements?

You can put an INSERT statement in a WHILE loop to run an input operation test as shown in this tutorial.

Create the following SQL script, fyi_int_insert.sql:

-- fyi_int_insert.sql
-- Copyright (c) FYIcenter.com 

DECLARE @Count INT
SET @Count = 100

DECLARE @I INT
DECLARE @STime DATETIME
DECLARE @ETime DATETIME
DECLARE @Duration INT

TRUNCATE TABLE FYI_INT
SET @STime = GETDATE()

SET @I = 0
WHILE @I < @Count BEGIN
   INSERT INTO FYI_INT VALUES (@I)
   SET @I = @I+1
END

SET @ETime = GETDATE()
TRUNCATE TABLE FYI_INT

SET @Duration = DATEDIFF(millisecond,@STime,@ETime)
SELECT @Count AS Count, @Duration AS Duration, 
  1000*@Count/@Duration AS Throughput, @Duration/@Count AS Latency

This script, fyi_int_insert.sql, will generate 4 fields in the output:

• Count - The number of records inserted into the table.
• Duration - The total execution time in seconds
• Throughput - The number of INSERT statements executed per seconds.
• Latency - The average execution time of each INSERT statement in milliseconds.

Now run the script with different @Count values on the Azure SQL Database. You will get something like:

Count   Duration   Throughput   Latency
  100        410          243         4
 1000       4140          241         4
10000      45453          220         4
50000     215640          231         4

As you can see, the script is working. This Azure SQL Database gives me a throughput of about 240 INSERT statements per second. It's not really fast. But it is very stable, no ups and downs.

⇒ Verify Azure SQL Database Storage Usage

⇐ Table for IOPS Tests on INSERT Statements

⇑ IOPS Tests on Azure SQL Database with INSERT

⇑⇑ SQL Server Storage Tutorials