2

Looking for anything such as known bugs in Sql Server 2016 SP1, CU4 and CU5 (both have same issue) on windows 2012 R2 on vmware. Esp. if where parallel threads wait on each other.

Issue: After going production we have one specific query in an SSIS dataflow that was very poorly performing in 2016 (was fine in 2012). So I found some indexes needed to be added so added them which cut down on runtime by about 80%. Then it started randomly freezing (say once or twice a day) never returning any rows. Doesn't seem to correlate to how busy the server is, locks, or data volume. We have very high cxpacket waits but who doesn't? I'm running maxdop 8 with 12 virtual cores.

Activity monitor very often shows session id is blocked by same session id on 2016. I never looked on 2012 to see if this happens. It also shows this when a query hangs. No deadlocks. I'm running with option recompile and default server maxdop 8 (database is maxdop 0).

Any ideas greatly appreciated.

Improve this question
7
  • 1
    might help:connect.microsoft.com/SQLServer/feedback/details/804376/…
    – Ali Razeghi - AWS
    Commented Oct 27, 2017 at 18:07
  • Interesting link and disturbing that they had threads which didn't recompile the rows uniquely which is a massive integrity issue. However in my case I have no indication of incorrect results but rather the query simply hangs and returns no rows.
    – Saxman
    Commented Oct 27, 2017 at 18:15
  • 1
    I am inclined to try legacy cardinality estimator to see if there's a bug in the new one.
    – Saxman
    Commented Oct 27, 2017 at 19:04
  • 1
    That might really be your best course of action outside of reaching out to MS or reading all the bugs (I didn't find this particular item listed but maybe I missed it). If you do find it as a bug please do open a Connect ticket with MS and report it to them.
    – Ali Razeghi - AWS
    Commented Oct 27, 2017 at 19:08
  • 2
    Can you post the query plan? I'm aware of two issues in 2016 related to parallel merge joins and TOP. Does your plan have either one of those?
    – Joe Obbish
    Commented Oct 27, 2017 at 22:58

1 Answer 1

Reset to default
2

Problem was due to use of the Vmware CPU Hot Plug feature in combination with Sql Server 2016.

This feature disables vNUMA and is not recommended by Microsoft or Vmware (see document Architecting Microsoft Sql Server on Vmware vSphere).

Switching this feature off and then rebooting the server resolved this issue and it resulted in average query performance of over 50% as well as showing improved utilization of CPUs.

Since we were running this Vmware setting when using Sql Server 2012 and did not experience freezing/hanging queries, my guess is something in 2016 makes this environmental setup more problematic. It could be the new default "soft Numa" configuration in 2016.

Btw...the query plan did not seem affected by this Vmware setting. You can however see a difference when you run select * from sys.dm_os_memory_nodes; With Numa switched off there is an added "Node 1" and the virtual address space reserved in node 0 goes from 100% of max mem with no Numa to 50% of total max mem when Numa awareness in vmware is turned on.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.