What is limiting the batch requests per sec We have a newly created Azure SQL Server 2014 instance. The specs for the server are as follows



Pretty bare bones, I know. Since this is just a testing server, we have opted to set up the data files on the C:\ drive itself.

We wanted to do some performance testing to see what it would be like compared to an on-premise solution. To do this, we created a database with the following table:

CREATE TABLE [dbo].[temptbl_1](
    [seq] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [field1] [varchar](50) NOT NULL,
    [field2] [int] NOT NULL,
    [field3] [datetime] NOT NULL,
 CONSTRAINT [PK_temptbl_1] PRIMARY KEY CLUSTERED 
(
    [seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


Then, we ran update stats on the entire db. We then ran the following insert statement to see what kind of results we would get:

insert into dbo.temptbl_1 (field1, field2, field3)
select 'here is a great big string',123456, getdate()
go 10000


The first time we ran it from my local machine's SSMS (2016 preview), and were shocked to see that it took ~9 minutes!

We then ran the query again from the server itself in SSMS, and it took ~2 minutes. This seemed much more plausible. But why is it taking so long from the client? 

Watching the activity monitor, I can see while the query is running from my local machine, the batch requests per/sec never goes above 20. Doing the math you can see why this would never finish faster than 8-9 minutes. However when running the query from the server, we can see that the batch requests per/sec go all the way up to 100.

We have tried to read as much as we can, and have looked through settings, and cannot figure out why it seems to be limiting the query from my local machine, but the query from the server it doesn't seem too.

Why does the server seem to be limiting the query from my workstation, but not when run from the server itself?
This is just a pure guess (I cannot find anything to support this), but I believe that the reason it is much slower when you perform it on your local box is that it is going through the iteration on your local box.

If you did this as a cursor, I believe this would process everything from SQL and be much faster.

AKA GO 10000 is being iterated 10000 times from your local so it has to worry about the network for each statement as well.