Reasons for slow MSSQL on Hetzner server?

Question

Hetzner

We bought a dedicated server on Hetzner, installed Win 2012 R2, MSSQL 2014.
The essence of the problem:
1. We connect from Moscow (Beeline) through Management Studio and execute a request to select a large amount of data. Takes 26 seconds .
2. We connect to our own server in Moscow (which is connected via a 20 Mbit channel) - with the same database and the same data, the same request is completed in 12 seconds .
Numerous channel speed tests (via Mikrotik BTest, LAN SpeedTest, etc.) show a greater throughput of the Hetzner channel.
I tried to test it on downloading a file via http: from a local server, the speed is 2.3-2.4 MB / s (which corresponds to the channel), with Hetzner it floats 3-6 MB / s - i.e. Large volumes via HTTP Hetzner returns faster.
What to do? Where to dig? Help me please

Answer 1

It is logical to just go to the server locally execute the request and see how long it takes
and then, after comparing the time, decide who slows down the channel or server

Answer 2

RTT (pings).
There are a lot of sequential operations for sure, each of them takes at least 45ms longer due to RTT. And so it is recruited.
Well, + disks can slow down if you have SATA-II there.

Answer 3

Hey!
There can be anything from slow disks to packet shaping by the beeline firewall in this direction. Try the same from another provider (from home for example). If the picture is the same, then look towards the load of disks / processor of the server at the time of the request.
If nothing helps, I advise you to write, but it's better to call Hetzner's TP

Answer 4

Miracles don't happen. If all other tests show that the channel is wide, then the point is:
1. in the server itself - the hardware does not pull, but yours is more productive.
2. on request. You need to look at the execution plan.
From personal experience - in 90 cases out of 100, the matter is in the request. It's not just select * from OnlyOneMyTable, right?
Here, try this one. A large table, and just a selection of everything in a row. If slows down - business in the server. If it doesn't slow down, it's in your request.

Answer 5

I answer all at once:
1. All kinds of performance tests (benchmarks) were done on both servers. They are very close in every way. Even the drives are the same (in one WD Black, in another WD Raid Edition). Sorry that I did not immediately describe in the question - we excluded the difference in server performance immediately at the first stage. Locally, on both servers, various queries (including very complex ones) are executed in comparable time.
2. The request is the simplest one: select * from SimpleTable. The table is simple, 8 datetime columns, 200k records are pre-inserted into it.
They threw me such an interesting article on this topic: https://www.simple-talk.com/sql/learn-sql-server/i...but does SQL really do a lot of synchronized packet exchanges back and forth to transfer a large batch of data? :(
With package shaping, the idea is also interesting, but the trouble is that Beeline is also at home :) I will ask my colleagues to conduct such tests.

Answer 6

Sergey , did you manage to take it? How did it all end? There is a desire to raise SQL Server BI Edition, I would like to have an idea about the pitfalls in advance. If you have any thoughts on this, please share. Surprisingly, there is nothing on the foreign Internet (google) on this issue.

Answer 7

In general, he won:
/sbin/ethtool -s eth0 speed 100 duplex full advertise 0x0F autoneg on
To increase the speed, you need to reduce it by 10 times, a paradox.
You need to configure the shaper, but lazily, 100M is enough. Someone crap on the way from Germany to Russia, I assume that the provider saves.