Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host
-
Tags added.
-
@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.
-
Going from a design standpoint, this entire thing is
@JaredBusch said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2016 on same host:
just WTF.
You never should install Microsoft Windows Server <Anything> to bare metal and then install Hyper-V. You can download and install Hyper-V to the hardware, always and first. Link to download center.
-
Title says SQL Server 2016, description says SQL Server 2014. Doesn't matter much, but should be clarified.
-
@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 all optimized. Leaving me with SQL Query issues in and out of the VMs.
No way to know BUT the real issue is that it is slower than the same system before. That's the key here.
Can you describe the former setup?
-
@scottalanmiller said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2016 on same host:
Can you describe the former setup?
This is the bit that I believe is critical. I have a feeling the devs needed a dev space, so the idea was VDI on the hypervisor that is hosting SQL. Keeps everything nice and clean, but was under spec'd for the amount of usage.
And that VDI was added, and is thus causing the slow down.
-
How much RAM do the VMs have assigned?
The Dom0 Windows instance can use all the RAM not assigned to a VM ( I think ), so that's likely one place it could be winning, depending upon how the RAM is divied up.
-
@DustinB3403 said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2016 on same host:
What kind of IOPS are you getting from the hypervisor?
How is the server configured?
How many concurrent users, 10?
Have you monitored the performance to try and determine where the slow down is occurring?No idea on the IOPs. I'll install "Zabbix" mentioned in your other post and report back.
Dell R540 Server with the following specs:
Two Xeon Gold 5117 2.27GHz 14 Core CPUs
(2 sockets, 28 cores, 56 logical processors)
128GB Ram
Two 500GB SSD drives in RAID1 for the OS
Four 1TB SSD drives in RAID10 for SQL Data and VMsWindows Server 2016 Standard
MS SQL Server 2014 StandardServer Runs AD, DHCP Server, DNS Server, SQL, and the VMs.
VMS:
Nine Windows 10 Enterprise VMs
PCs:
Six Windows 10 Pro Workstations also accessing the SQL Data@scottalanmiller said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2016 on same host:
Check the network setup, make sure that the database server and the Windows 10 client VMs all have proper network drivers configured. Could be a point of issues if not.
I'll check this.
@scottalanmiller 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 all optimized. Leaving me with SQL Query issues in and out of the VMs.
No way to know BUT the real issue is that it is slower than the same system before. That's the key here.
Can you describe the former setup?
I minced my words and created confusion. It's all new hardware and design. Previously it was SBS2011 with SQL installed and another server running Windows Server 2008 with Terminal Services. Both with spinning 10K drives and 32GB ram. It wasn't fast, but it was faster than this.
@Dashrender said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2016 on same host:
How much RAM do the VMs have assigned?
The Dom0 Windows instance can use all the RAM not assigned to a VM ( I think ), so that's likely one place it could be winning, depending upon how the RAM is divied up.
I had them set for 6GB Dynamic Ram They never use more than 2GB at peak, usually around 1.7 or 1.8. I changed a couple of them to 6GB NOT Dynamic and did not see any change in the user experience.
I also had them set for two vCPUs with 10% for VM Reserve, VM Limit, and Relative weight.
I tried 3 vCPUs, no change, and then I went to one vCPU with 100% VM Reserve, VM Limit, and Relative weight. I thought I noticed an improvement, but I can't be sure via the user interface, hopefully Zabbix will shed some light.....
@Dashrender said in Slow MS SQL Queries between Windows 10 VMs and MS SQL 2016 on same host:
How much RAM do the VMs have assigned?
The Dom0 Windows instance can use all the RAM not assigned to a VM ( I think ), so that's likely one place it could be winning, depending upon how the RAM is divied up.
I'll list what I know about the memory usage, not sure how it is spread about:
Total Physical RAM: 128GB
In use Compressed: 58GB
Available: 70GB
Committed: 60GB/147GB
Cached: 35.5GB
Paged pool: 592MB
Non Paged Pool: 1.92GB
Hardware Reserved 318MB
Speed 2666MHzI haven't seem RAM usage above 50% and I have not seen CPU usage (taskmanager) above 7%
I'm going to go and grab Zabbix now.
-
@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.
