none
Cluster AlwaysON SQL Server CPU 100%,客户端没有性能的问题 RRS feed

  • 问题

  • Hello,

    We have a new cluster environment(node A, node B, node C, all nodes have 512 GB memory, 10 sql server instance, each instance has 50GB memory) A and B are sync, C is async.

    A and B each has half customers online, but A has high cpu usage now(90-100%), B cpu is around 10%, did a xperf dump, found LowPhysicalMemoryQueryRoutine, checking the instances on A, all instances have good buffer hits, page read, page write, no locked transactions.

    Any idea? Thanks in advance

    Albert

    2014年12月3日 22:58

答案

  • Thanks All.

    The solution is very simple, reset the max or min memory on even single one instance.

    The problem is there is 12 GB ram left for OS, but we were doing backup copying to the windows os then restore, the file.copy somehow will consume windows memory, so windows tried to ask sql server to reduce memory usage, but sql server cannot due to the min/max, reset the value could fix the memory conflict immediately.

    Thanks,

    Albert

    2014年12月5日 2:33

全部回复

  • 抓包看看,那些SQL消耗CPU比较多。针对优化一下。


    family as water

    2014年12月4日 1:33
  • Find out which OS process uses most cpu time. If it's one of sql instance, find out which session caused high cpu usage.
    2014年12月4日 2:49
  • 单纯的 CPU 使用率说明不了什么, 你需要看CPU占用最高的进程,是否是 SQL Server 的进程,如果是,那么需要进一步 trace sql server 中,使用 CPU 最多的是什么
    2014年12月5日 1:15
  • Thanks All.

    The solution is very simple, reset the max or min memory on even single one instance.

    The problem is there is 12 GB ram left for OS, but we were doing backup copying to the windows os then restore, the file.copy somehow will consume windows memory, so windows tried to ask sql server to reduce memory usage, but sql server cannot due to the min/max, reset the value could fix the memory conflict immediately.

    Thanks,

    Albert

    2014年12月5日 2:33
  • Don't need copy backup files to local disk if you use domain account with proper file access permission for sql services.
    2014年12月5日 4:59