ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    Slow MS SQL Queries between Windows 10 VMs and MS SQL 2014 on same host

    IT Discussion
    database rdbms ms sql server ms sql server 2016 sql windows 10 windows server 2016 hyper-v
    8
    27
    2.1k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • J
      JasGot @Guest
      last edited by

      This post is deleted!
      1 Reply Last reply Reply Quote 0
      • scottalanmillerS
        scottalanmiller @JasGot
        last edited by

        @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.

        1 Reply Last reply Reply Quote 0
        • scottalanmillerS
          scottalanmiller
          last edited by

          Sorry for the note about Zabbix, I had misunderstood the thread.

          1 Reply Last reply Reply Quote 0
          • JaredBuschJ
            JaredBusch @JasGot
            last edited by JaredBusch

            @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.

            1 Reply Last reply Reply Quote 0
            • JaredBuschJ
              JaredBusch @JasGot
              last edited by JaredBusch

              @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.

              J 1 Reply Last reply Reply Quote 1
              • J
                JasGot @JaredBusch
                last edited by

                @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

                1 Reply Last reply Reply Quote 0
                • J
                  JasGot
                  last edited by

                  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.

                  Emad RE 1 Reply Last reply Reply Quote 2
                  • J
                    JasGot
                    last edited by

                    Winner Winner, Chicken Dinner. It was MDTC and Windows Firewall. (Which is frustrating, because the firewall is turned off for the domain!)

                    jmooreJ pmonchoP 2 Replies Last reply Reply Quote 0
                    • Emad RE
                      Emad R @JasGot
                      last edited by Emad R

                      @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

                      J 1 Reply Last reply Reply Quote 0
                      • jmooreJ
                        jmoore @JasGot
                        last edited by

                        @JasGot Glad you figured that out, thats an amazing improvement.

                        1 Reply Last reply Reply Quote 0
                        • pmonchoP
                          pmoncho @JasGot
                          last edited by

                          @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?

                          J 1 Reply Last reply Reply Quote 1
                          • J
                            JasGot @Emad R
                            last edited by

                            @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 proxy

                            I don't follow..... MSDTC has been around a long time. You'll find it preconfigured in all Windows Firewall apps.

                            1 Reply Last reply Reply Quote 0
                            • J
                              JasGot @pmoncho
                              last edited by

                              @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!

                              1 Reply Last reply Reply Quote 3
                              • 1
                              • 2
                              • 1 / 2
                              • First post
                                Last post