November 5, 2020 at 7:26 pm
I have a SQL server on a HyperV cloud. The server harddrive is stored on the SAN. I have separate LUNS (drive letters) to the mdf, ldf and tempdb files.
When I run "select * from product" on my local copy, it takes 2 seconds. When I run the same query on the sql server (on HyperV) it takes 12 seconds.
When I connect to HyperV sql server from other service in the same HyperV cloud, it takes 15 seconds.
I know that bare metal (my laptop) will be faster, but I would not think the HyperV server would be that much slower.
Here is some network info:
* SAN - all flash Dell Unity
* 10gb Connections
* large packet in use and enabled
* 132Tb of memory on server
Any ideas on what to look for?
November 5, 2020 at 8:24 pm
Execution plans would be my starting point. make sure that the queries are all doing the same thing.
Also make sure all queries are getting the same results.
Then I'd check wait stats and who else is using the server (could be blocking). Next, check the how much memory is available, how much CPU is available, disk I/O, network I/O, etc. Even with a 10GB connection, if that is shared between other things on that server, you may not be getting the full 10 GB. And to confirm - it is 10 GB network all the way from the server to the SAN? If any connection in between is slower, you may be running slower.
There are tools to do network speed tests, so that may not hurt. Make sure your network connection really is 10 Gbps to the SAN.
Running the query on your local copy, I am assuming you mean with the database files and software all on the same physical machine (like your desktop). This removes the network from the equation, but it also removes the server. If the server has 132 TB of memory (which is a LOT... I am very jealous... mine only have 128 GB), I expect that other things are chugging along on that server and it isn't 132 TB dedicated to your one SQL instance. How much is free when you ran your query? If it needed to dump things to disk, that will slow it down too.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 5, 2020 at 8:34 pm
Execution plans are the same: clustered Index Scan (Clustered) ; Cost: 100%
Yep - same query, same result. Just slower.
Tomorrow p.m., we have a 2 hour maintenance window. I will try this same test with no load on the server.
10Gb verified.
Anyways, I will keep looking. Any thing else - please tell me.
Thanks!
November 5, 2020 at 9:23 pm
Those are kind of my go-to's.
One thing that could be an interesting check would be how much data is being transmitted across that connection at the time the query is being run. Like if it is a 10 Gbps connection and you are requesting 10 Gb of data, I would expect it to complete in just over 1 second on that link IF I was the only thing on it. BUT if the server is busy using most of that 10 Gbps connection on other things, I may only be getting 10 Mbps of bandwidth out of it in which case that would be my bottleneck.
On the other hand, if the SAN is 5400 RPM spinning disk and your local laptop is a super fast SSD, it could be the disk is the bottleneck.
Since the server is shared for other things, getting JUST the SQL metrics can be a bit more tricky. BUT during your 2 hour window, that will help a lot to determine if it is due to load on the server/network/SAN that is causing the bottleneck or something else.
One thing you could try is to turn statistics time, statistics IO, and client statistics on when running the query which MAY help give some insight into the query to make sure it is actually doing the same thing on the different machines. Might not hurt to make sure they are all usign the same logical reads and cpu time and check the number of bits sent back and forth.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 6, 2020 at 11:51 am
Make sure you're capturing the performance metrics on the server so that you're getting processing time there and eliminating the round trip measurement. You want to know which part of the whole chain of stuff, disks, disk sub-system, memory, CPU, hypervisor, network, is causing things to slow down. So, query performance from the client, sure, but also at the server. Memory, CPU, I/O on the server, sure, but also at the hypervisor. Then, it's all down to configurations, settings, and behaviors.
Also, for a SELECT * query without a WHERE clause, don't sweat the execution plan. That kind of query is just down to hardware and system settings.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 6, 2020 at 2:47 pm
One that bites a few people is ensuring that the power plan is set in performance mode and not balanced, I have seen that affect alot of things over the years.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply