Applications Systems

100% CPU usage on MS SQL Server monitored by DPA

The CPU usage goes to 100% on a Microsoft SQL Server that uses SSD storage to host one or more instances you are monitoring. This causes the server and its instances to have high signal waits.

First published date

10/30/2018 10:34 PM

Last published date

10/30/2018 10:34 PM

Overview

The CPU usage goes to 100% on a Microsoft SQL Server that uses SSD storage to host one or more instances you are monitoring. This causes the server and its instances to have high signal waits.
 

Product section

Database Performance Analyzer

Cause

There is a known issue regarding high CPU usage and the Microsoft SQL Server (SQL Server 2012 and earlier)  I/O handling mechanism that creates high I/O wait times. For more information about the issue, see this article (© 2018 Microsoft, available at https://connect.microsoft.com, obtained on January 24, 2018).
 

Resolution

As a workaround, disable I/O statistics in SolarWinds DPA for the instances hosted on that MS SQL Server. The easiest way is to disable the IO_STATS_POLL_ENABLED option. You can do this for individual instances or for all instances.

To disable I/O statistics for individual instances  

  1. Click the DB Instance Options tab.
  2. Click the Support Options checkbox.
  3. Select the database instance from the dropdown.
  4. Click the IO_STATS_POLL_ENABLED option.
  5. In Edit Option, select New Value: False.
  6. Click Update.
  7. Restart monitoring on the database instance .

To disable I/O statistics for all instances 

 
  1. Click the System Options tab.
  2. Click the Support Options checkbox.
  3. Click the IO_STATS_POLL_ENABLED option.
  4. In Edit Option, select New Value: False.
  5. Click Update.
  6. Restart all monitors.

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.