How to run an input operation test with INSERT statements on UUID data type?

If you want to see the impact of larger data type like 16-byte UNIQUEIDENTIFIER (UUID), comparing to the 4-byte INT and 8-byte BIGINT, you can follow this tutorial.

Create the following SQL script, fyi_uuid_insert.sql:

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

DECLARE @Count INT
SET @Count = 100

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

CREATE TABLE FYI_UUID (UUID UNIQUEIDENTIFIER) 
SET @STime = GETDATE()

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

SET @ETime = GETDATE()
DROP TABLE FYI_UUID

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

Each record now has a single field of UNIQUEIDENTIFIER, which uses 16 bytes.

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

Count   Duration   Throughput   Latency
  100        667          149         6
 1000       6437          155         6
10000      65073          153         6
25000     159707          156         6

The actual input data throughput is 156 * 16 bytes/sec = 2,496 bytes/sec = 2.5 KB/sec, using the last test result.

Comparing to 4-byte INT records and 8-byte BIGINT records, inserting 8-byte BIGIN records is getting more efficient:

       Data type   Record size   Throughput rec/sec   Throughput KB/sec
             INT             4                  231                 0.9
          BIGINT             8                  165                 1.3
UNIQUEIDENTIFIER            16                  156                 2.5

⇒ Input Operation Test with VARCHAR Data Type

⇐ Input Operation Test with BIGINT Data Type

⇑ IOPS Tests on Azure SQL Database with INSERT

⇑⇑ SQL Server Storage Tutorials