2

We have a newly created Azure SQL Server 2014 instance. The specs for the server are as follows

enter image description here

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?

  • Hmmmm could be network – Max Vernon Jul 2 '15 at 22:10
  • 1
    I'm starting to think so as well. I'm going to write a stored proc that does some heavy lifting. That should take networking out of the equation. – Andy E Jul 6 '15 at 12:33
1

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.

  • Yeah I didn't think the iteration would be happening at my local box, but I suppose it is possible. I think I'll write a stored proc that does some heavy lifting and see how that does. – Andy E Jul 6 '15 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.