1

We have a 4 node cluster running 2 SQL Failover Instances.

Windows 2012 Standard & SQL 2014

Microsoft Failover Cluster Service has been consitently between 25 - 30 % CPU. This is happening on 2 Nodes.

enter image description here

I have stopped antivirus ( Trend Micro ) services on the nodes and that has not made any difference.

There are unusal entries in the cluster log or the event logs.

Any help with with would be very helpful ..


Priority 50: Performance:

  • Poison Wait Detected: THREADPOOL - 0:01:51:42 of this wait have been recorded. This wait often indicates killer performance problems.

Priority 100: Performance:

  • Many Plans for One Query - 2470 plans are present for a single query in the plan cache - meaning we probably have parameterization issues.

  • Resource Governor Enabled - Resource Governor is enabled. Queries may be throttled. Make sure you understand how the Classifier Function is configured.

  • TraceFlag On

    • Trace flag 1117 is enabled globally.

    • Trace flag 3226 is enabled globally.

    Priority 200: Non-Default Server Config:

  • Agent XPs - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.

  • backup compression default - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.

  • clr enabled - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.

  • cost threshold for parallelism - This sp_configure option has been changed. Its default value is 5 and it has been set to 50.

  • Database Mail XPs - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.

  • fill factor (%) - This sp_configure option has been changed. Its default value is 0 and it has been set to 90.

  • max degree of parallelism - This sp_configure option has been changed. Its default value is 0 and it has been set to 2.

  • max server memory (MB) - This sp_configure option has been changed. Its default value is 2147483647 and it has been set to 26624.

  • max text repl size (B) - This sp_configure option has been changed. Its default value is 65536 and it has been set to -1.

  • Ole Automation Procedures - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.

  • remote admin connections - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.

  • scan for startup procs - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.

  • xp_cmdshell - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.

Priority 200: Performance:

  • Old Compatibility Level distribution - Database distribution is compatibility level 90, which may cause unwanted results when trying to run queries that have newer T-SQL features

Priority 240: Wait Stats:

  • 1 - CXPACKET - 1323.7 hours of waits, 152.3 minutes average wait time per hour, 17.3% signal wait, 951998189 waiting tasks, 5.0 ms average wait time.

Priority 250: Server Info:

  • Default Trace Contents - The default trace holds 29 hours of data between Jun 14 2018 12:56PM and Jun 15 2018 5:16PM. The default trace files are located in: D:\data1\data1\MSSQL12.01B\MSSQL\Log

  • Drive C Space - 91491.00MB free on C drive

  • Drive D Space - 5034.00MB free on D drive

  • Hardware - Logical processors: 4. Physical memory: 32GB.

  • Hardware - NUMA Config - Node: 0 State: ONLINE Online schedulers: 4 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 135

  • Power Plan - Your server has 2.39GHz CPUs, and is in high performance power mode

  • Server Last Restart - Apr 5 2018 1:02AM

  • Server Name - PRODSQL\01B

  • Services

    • Service: SQL Full-text Filter Daemon Launcher (01B) runs under service account NT Service\MSSQLFDLauncher$01B. Last startup time: May 24 2018 11:52PM. Startup type: Manual, currently Running.

    • Service: SQL Server (01B) runs under service account healthcare\svc_SQL01B. Last startup time: May 24 2018 11:52PM. Startup type: Manual, currently Running.

    • Service: SQL Server Agent (01B) runs under service account healthcare\svc_SQL01B. Last startup time: May 24 2018 11:56PM. Startup type: Manual, currently Running.

  • SQL Server Last Restart - May 24 2018 11:52PM

  • SQL Server Service - Version: 12.0.5538.0. Patch Level: SP2. Cumulative Update: CU3. Edition: Enterprise Edition: Core-based Licensing (64-bit). Availability Groups Enabled: 1. Availability Groups Manager Status: 1

  • Virtual Server - Type: (HYPERVISOR)

  • Windows Version - You're running a pretty modern version of Windows: Server 2012R2 era, version 6.3

Priority 254: Rundate:

  • Captain's log: stardate something and something...
  • Any monitoring service making unusual calls ? Is this vm and by any chance can you try to stop WinRM service to see if it makes any difference? – Kin Shah Jun 14 '18 at 2:19
  • yes it is a VM. Stopped WinRM and SCOM. Did not make a diference unfortunately. – Halt_And_Catch_Fire Jun 14 '18 at 3:40
  • Can you include the contents of EXEC sp_Blitz OutputType = ‘markdown’, CheckServerInfo = 1 as described here: brentozar.com/archive/2017/10/getting-help-slow-server I have a hunch that this is a really small VM (few cores, and slow core speeds), and it's not as much of a problem that the cluster service is high CPU, but that the VM just doesn't have much access to CPU power, period. – Brent Ozar Jun 14 '18 at 10:34
  • Thanks Brent. I have pasted the results removing some information. – Halt_And_Catch_Fire Jun 15 '18 at 5:19
  • 1
    @Halt_And_Catch_Fire ooo, wow, yeah, 2 hours of threadpool waits in ~3 weeks is a pretty big deal. Your server is coming under severe CPU pressure (or blocking chains) from time to time. I have a hunch that you're hitting CPU ready time issues: the host isn't able to provide the CPU power that you need. Troubleshooting this is a little beyond what I can do quickly at Stack, though. – Brent Ozar Jun 15 '18 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.