Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host
-
@DustinB3403 said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2016 on same host:
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2016 on same host:
I've had chats with the developers, and they suggest the SQL server and application are are optimized. Leaving me with SQL Query issues in and out of the VMs.
The devs are idiots who work in just their silo. They know the software they are working on, and that's about it.
The host and its underlying hardware is where you are expected to be the expert and to pinpoint the issue(s).
I'm guessing you currently don't have any sort of system monitoring in place, besides what is a default on Windows Server 2016 Standard (no special tools). So you should consider setting up something like Zabbix to monitor this hypervisor, that way you'll be able to state with fact where the slowdown is, be it a IOPS issue (very likely) or a different contention issue with the RAM or CPU.
Zabbix requires the host be installed on something other than Windows. I'm going to try and compile statistics another way. I'll come back to Zabbix if I am unsuccessful.
-
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
@DustinB3403 said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2016 on same host:
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2016 on same host:
I've had chats with the developers, and they suggest the SQL server and application are are optimized. Leaving me with SQL Query issues in and out of the VMs.
The devs are idiots who work in just their silo. They know the software they are working on, and that's about it.
The host and its underlying hardware is where you are expected to be the expert and to pinpoint the issue(s).
I'm guessing you currently don't have any sort of system monitoring in place, besides what is a default on Windows Server 2016 Standard (no special tools). So you should consider setting up something like Zabbix to monitor this hypervisor, that way you'll be able to state with fact where the slowdown is, be it a IOPS issue (very likely) or a different contention issue with the RAM or CPU.
Zabbix requires the host be installed on something other than Windows. I'm going to try and compile statistics another way. I'll come back to Zabbix if I am unsuccessful.
That's correct, it is a UNIX only application.
-
This post is deleted! -
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
No idea on the IOPs. I'll install "Zabbix" mentioned in your other post and report back.
Probably simpler to just use the Performance Monitor on Windows to get the IOPS.
-
Sorry for the note about Zabbix, I had misunderstood the thread.
-
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
Two Xeon Gold 5117 2.27GHz 14 Core CPUs
(2 sockets, 28 cores, 56 logical processors)OMG the Windows Server licensing cost for this.
Server 2019 Standard = 16 cores + 2-Core License Packs * 6 = 12 cores = 28 Cores
edit: Server 2019 licensing is assuming you recently purchased. Obviously, you can install 2016, or even 2012R2 if you felt like it.
-
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
Four 1TB SSD drives in RAID10 for SQL Data and VMs
IOPS is not likely your problem if your RAID card is configured correctly.
But
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
Two 500GB SSD drives in RAID1 for the OS
This is likely splitting your RAID card's capabilities.
What card is it?
Edit: This is less of a problem with SSD because there is almost no read/write delay unlike with spinning disks.
-
@JaredBusch said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
Four 1TB SSD drives in RAID10 for SQL Data and VMs
IOPS is not likely your problem if your RAID card is configured correctly.
But
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
Two 500GB SSD drives in RAID1 for the OS
This is likely splitting your RAID card's capabilities.
What card is it?
Edit: This is less of a problem with SSD because there is almost no read/write delay unlike with spinning disks.
H740P
-
I won't know definitively until mid morning, but I think I sorted it out. It was the Distributed Transaction Coordinator in the Windows 10 VDI firewall. I allowed it for the domain profile and now the queries are running faster than I can witness.
From 15-16 seconds....to under one second. Holy crap!
I'll report back in a few hours.
-
Winner Winner, Chicken Dinner. It was MDTC and Windows Firewall. (Which is frustrating, because the firewall is turned off for the domain!)
-
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
Distributed Transaction Coordinator
I have never heard of
MS Distributed Transaction Coordinator
And i think im privileged, it seems like failed attempt to fix something that should be baked in already or something over complex and can be simply be reverse proxy -
@JasGot Glad you figured that out, thats an amazing improvement.
-
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
Winner Winner, Chicken Dinner. It was MDTC and Windows Firewall. (Which is frustrating, because the firewall is turned off for the domain!)
Good job at troubleshooting. I'm wondering what led you down this path versus something related to Networking, hard drives or software drivers?
-
@Emad-R said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
Distributed Transaction Coordinator
I have never heard of
MS Distributed Transaction Coordinator
And i think im privileged, it seems like failed attempt to fix something that should be baked in already or something over complex and can be simply be reverse proxyI don't follow..... MSDTC has been around a long time. You'll find it preconfigured in all Windows Firewall apps.
-
@pmoncho said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
@JasGot said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host:
Winner Winner, Chicken Dinner. It was MDTC and Windows Firewall. (Which is frustrating, because the firewall is turned off for the domain!)
Good job at troubleshooting. I'm wondering what led you down this path versus something related to Networking, hard drives or software drivers?
Remembered it from several years ago with another SQL based application. Don't know what made me think of it at 1:30am. But I'm ok with it!
