Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host
-
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!
