Inserting data into Physical Table on Azure Taking Longer Than Expected

Question

Post reply

Inserting data into Physical Table on Azure Taking Longer Than Expected

Weegee71

SSChasing Mays

Points: 604

April 3, 2023 at 4:44 pm

#4168549

We have an issue on Azure with inserting data into a physical table .

We have done some benchmark testing and we are finding that inserting to a physical table on Azure, is taking

4 times longer compared to using a User Defined Table Type or Temp Table

I wanted to know why this might be the case with a physical table on Azure?

ive attached a script to create the scenarios (with Timings) for the following:

1) Inserting into a physical table

2) Inserting into a table Type

3) Inserting into a Temp Table

/************************************************************************************************/
/*1) PHYSICAL TABLE    */
/************************************************************************************************/

DROP TABLE IF EXISTS [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F]
GO


CREATE TABLE [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F](
[SerialNumber] [nvarchar](60) NULL,
[ChannelID] [TINYINT] NULL,
[Si] [decimal](10, 2) NULL,
[Raw] [int] NULL,
[ReadingDateTime] [datetime] NULL
) ON [PRIMARY]
GO


/************************************************************************************************/
/*2) TABLE TYPE    */
/************************************************************************************************/

DROP TYPE IF EXISTS [dbo].[ChannelReadingType]
GO

/****** Object:  UserDefinedTableType [dbo].[ChannelReadingType]    Script Date: 03/04/2023 15:41:32 ******/
CREATE TYPE [dbo].[ChannelReadingType] AS TABLE(
[SerialNumber] [nvarchar](60) NULL,
[ChannelID] [TINYINT] NULL,
[Si] [decimal](10, 2) NULL,
[Raw] [int] NULL,
[ReadingDateTime] [datetime] NULL
)
GO


/************************************************************************************************/
/*3) TEMP TABLE    */
/************************************************************************************************/

DROP TABLE IF EXISTS [#ChannelReading_939029_11]

CREATE TABLE [#ChannelReading_939029_11](
[SerialNumber] [nvarchar](60) NULL,
[ChannelID] [TINYINT] NULL,
[Si] [decimal](10, 2) NULL,
[Raw] [int] NULL,
[ReadingDateTime] [datetime] NULL
) ON [PRIMARY]
GO

-- TEST 1  -> PHYSICAL TABLE
SET NOCOUNT OFF

DECLARE@timeStarted datetime2,@TimeFinished datetime2

SET @timeStarted = CAST(SYSDATETIME() AS NVARCHAR(200))

INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.26 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T13:58:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(24.94 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:08:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.39 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:18:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.01 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:28:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.43 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:38:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.08 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:48:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(24.97 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:58:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.17 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:08:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(24.92 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:18:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.21 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:28:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.78 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:38:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.19 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:48:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.99 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:58:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.90 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:08:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.07 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:18:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.76 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:28:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.07 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:38:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.67 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:48:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.99 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:58:57.000' AS DateTime))
INSERT [dbo].[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.64 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T17:08:57.000' AS DateTime))


SET @TimeFinished = CAST(SYSDATETIME() AS NVARCHAR(200))

SELECT @timeStarted as TimeStarted, @TimeFinished as TimeFinished,(datediff(millisecond,@timeStarted,@TimeFinished)) as TimeElapsed, 'Physical Table' as Comment


-- TEST 2  -> TABLE TYPE
DECLARE@ChannelReadings [dbo].[ChannelReadingType]

SET @timeStarted = CAST(SYSDATETIME() AS NVARCHAR(200))

INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.26 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T13:58:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(24.94 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:08:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.39 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:18:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.01 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:28:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.43 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:38:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.08 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:48:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(24.97 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:58:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.17 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:08:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(24.92 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:18:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.21 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:28:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.78 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:38:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.19 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:48:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.99 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:58:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.90 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:08:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.07 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:18:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.76 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:28:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.07 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:38:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.67 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:48:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.99 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:58:57.000' AS DateTime))
INSERT @ChannelReadings([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.64 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T17:08:57.000' AS DateTime))

SET @TimeFinished = CAST(SYSDATETIME() AS NVARCHAR(200))

SELECT @timeStarted as TimeStarted, @TimeFinished as TimeFinished,(datediff(millisecond,@timeStarted,@TimeFinished)) as TimeElapsed, 'Table Type' as Comment


--TEST 3 -> TEMP TABLE

SET @timeStarted = CAST(SYSDATETIME() AS NVARCHAR(200))

INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.26 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T13:58:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(24.94 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:08:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.39 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:18:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.01 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:28:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.43 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:38:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.08 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:48:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(24.97 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:58:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(25.17 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:08:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES (N'939029', 11, CAST(24.92 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:18:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.21 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:28:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.78 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:38:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.19 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:48:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.99 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T15:58:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.90 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:08:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.07 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:18:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.76 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:28:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(25.07 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:38:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.67 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:48:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.99 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:58:57.000' AS DateTime))
INSERT [#ChannelReading_939029_11] ([SerialNumber], [ChannelID], [Si], [Raw], [ReadingDateTime]) VALUES ( N'939029', 11, CAST(24.64 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T17:08:57.000' AS DateTime))

SET @TimeFinished = CAST(SYSDATETIME() AS NVARCHAR(200))

SELECT @timeStarted as TimeStarted, @TimeFinished as TimeFinished,(datediff(millisecond,@timeStarted,@TimeFinished)) as TimeElapsed, 'Temp Table' as Comment

select COUNT(1)as PhysicalTableRowCountfrom [ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F]

select COUNT(1) as TableTypeRowCountfrom @ChannelReadings

select COUNT(1) as TempTableRowCountfrom [#ChannelReading_939029_11]

The attached screenshots shows the results when running this on Azure (Free Pay as you go subscription)

Attachments:

You must be logged in to view attached files.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27060 More actions · Answer 1

Out of curiosity, I ran this on my local machine.

Screenshot

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

Michael L John wrote:

Out of curiosity, I ran this on my local machine.

Ok thats interesting. But are you able to run this on Azure?

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Weegee71 wrote:

Ok thats interesting. But are you able to run this on Azure?

On what in Azure?

Azure SQL Database? If so what size? Is it DTU? Is it vCore? What config?

Azure SQL Managed Instance? Again what size? What config?

Azure SQL on IaaS? Again what size? What config?

When talking the cloud, please give as much detail as you can so it may be possible that someone could replicate the environment, if they have the ability to do so given costs etc?

Weegee71 SSChasing Mays Points: 604 More actions · Answer 4

Ant-Green wrote:

Weegee71 wrote:
Ok thats interesting. But are you able to run this on Azure?
On what in Azure? Azure SQL Database? If so what size? Is it DTU? Is it vCore? What config? Azure SQL Managed Instance? Again what size? What config? Azure SQL on IaaS? Again what size? What config?
When talking the cloud, please give as much detail as you can so it may be possible that someone could replicate the environment, if they have the ability to do so given costs etc?

Hey Ant

We are running the Azure free Pay as You Go Subscription model with following:

Pricing tier: Standard S): 10 DTUS

Maximum Storage Size: 250 GB

I dont think this is vcore

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Only thing I can see is that it is waiting for log memory, the temp/table var don't require log memory.

But it is constant physical table under performs on the standard tier, the documentation is fairly lack on the I/O details you get on these but it is down to the "standard" service tier I/O throttling I would assume here.

20runs

As you can see once I switched it to a P1 (god help my budget), the timing becomes a little better

But yeah the PaaS offering has a lot of QOS behind the scenes.

https://www.brentozar.com/archive/2019/02/theres-a-bottleneck-in-azure-sql-db-storage-throughput/

Weegee71 SSChasing Mays Points: 604 More actions · Answer 6

Ant-Green wrote:

Only thing I can see is that it is waiting for log memory, the temp/table var don't require log memory.

But it is constant physical table under performs on the standard tier, the documentation is fairly lack on the I/O details you get on these but it is down to the "standard" service tier I/O throttling I would assume here.

20runs

As you can see once I switched it to a P1 (god help my budget), the timing becomes a little better

But yeah the PaaS offering has a lot of QOS behind the scenes.

https://www.brentozar.com/archive/2019/02/theres-a-bottleneck-in-azure-sql-db-storage-throughput/%5B/quote%5D

Thank you very much Ant... for running that test

As i suspected the SO tier is not good enough to run this

you switch to S1 and the times for the Physical Table drop to 25% of what they were on S0

Michael L John One Orange Chip Points: 27060 More actions · Answer 7

And now you understand why the cloud is not cheaper than on-prem!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/