A SQL Azure tip a day (18) – My Laptop is faster than SQL AZURE ?!?

EDIT: Also read this blogpost: https://sqlservice.se/sv/start/blogg/sql-azure-followup.aspx , that gives more context to the findings in this post.

Today we will take a look at the INSERT speed of SQL Azure, compared to my laptop. So I did the following on both my HP ENVY i5 Laptop and 1 internal spinning disc and SQL Server 2008 R2, and my SQL Azure Web edition database.

I created 2 tables with clustered indexes:

CREATE TABLE [dbo].[TableTest]( [Int_Col] [int] IDENTITY(1,1) NOT NULL, [Varchar_Col] [varchar](50) NOT NULL, [Datetime_Col] [datetime] NOT NULL, [GUID_Col] [uniqueidentifier] NOT NULL )

ALTER TABLE [dbo].[TableTest] ADD  CONSTRAINT [DF_TableTest_Varchar_Col]  DEFAULT ('Varchar_Col') FOR [Varchar_Col] ALTER TABLE [dbo].[TableTest] ADD  CONSTRAINT [DF_TableTest_Datetime_Col]  DEFAULT (getdate()) FOR [Datetime_Col]

ALTER TABLE [dbo].[TableTest] ADD  CONSTRAINT [DF_TableTest_GUID_Col]  DEFAULT (newid()) FOR [GUID_Col]

GO

CREATE CLUSTERED INDEX [ix_tabletest] ON [dbo].[TableTest] ( [GUID_Col] ASC )

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

GO

CREATE TABLE [dbo].[results]

( [GUID] [uniqueidentifier] NOT NULL, [test] [varchar](50) NULL, [starttime] [datetime2](7) NULL, [endtime] [datetime2](7) NULL, [rownr] [bigint] NULL, [Runtime_MS]  AS (datediff(millisecond,[starttime],[endtime])), [Rows_pr_ms]  AS (isnull(CONVERT([decimal],[rownr],0)/datediff(millisecond,[starttime],[endtime]),(0))) ) GO

CREATE CLUSTERED INDEX [ix_results] ON [dbo].[results] ( [GUID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) GO CREATE TABLE [dbo].[guidtbl]( [GUID] [uniqueidentifier] NOT NULL )

CREATE CLUSTERED INDEX [ix_guidtbl] ON [dbo].[guidtbl] ( [GUID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) GO

Then I created A stored proc:

create proc [dbo].[insert_test] @rows int as 

SET NOCOUNT ON 

SET STATISTICS IO OFF 

SET STATISTICS TIME OFF 

truncate table guidtbl 

insert guidtbl (GUID) Values (NEWID()) 

TRUNCATE TABLE [dbo].[TableTest] 

Declare @GUID uniqueidentifier 

select @GUID = guid from guidtbl 

Insert results ([GUID], test, starttime) values (@GUID, 'INSERT_Test', getdate()) 

declare @i int 

select @i = 0 while @i < @rows 

begin 

INSERT INTO [dbo].[TableTest] ([Varchar_Col] ,[Datetime_Col] ,[GUID_Col]) VALUES (default ,default ,default) 

select @i = @i + 1 

end 

Update results set endtime = getdate() where test = 'Insert_Test' and endtime IS NULL 

DECLARE @rownr int 

select @rownr =  count(*) from TableTest update results set rownr =  @rownr where test = 'Insert_Test' and rownr IS NULL

 GO

And then I executed it: exec dbo.insert_test 100000

The results?  After a few testruns the results table looks like this on SQL AZURE: select  * from results And the results table on my laptop: select  * from results

The conclusion? My Laptop is faster than SQL Azure! Atleast when it comes to pure INSERT performance.

The reason? Obviously I can’t be 100% sure about why. But having repeated the test on several other servers, I have reached the following conclusion: The INSERT performance problems in SQL AZURE is likely caused by the underlying IO subsystem, particularly on the drive that holds the logfile, and by the fact that you have 3 redundant copies of your database. Using this script by Håkan Winther, I captured the following snapshots of what I am waiting for during the INSERT test: and   The results I have collected so far on other physical servers shows that SAN connected servers have a hard time getting more than about 1 INSERT per Millisecond. And the cause is that we wait for WRITELOG for about 0.8 milliseconds per row. The average wait time for WRITELOG on internal disks (Even when running on just a single laptop disc) is about 0.1 to 0.2 Milliseconds. Does this mean that my Laptop always are faster than SQL AZURE? Stay tuned, more tests are coming up 🙂