Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

Home > Success Center > Database Performance Analyzer (DPA) > DPA monitor stops on SQL Server with max connections exceeded

DPA monitor stops on SQL Server with max connections exceeded

Updated July 20th, 2016

Overview

This article describes the issue where sessions become unresponsive as the WMI call to the Windows O/S is never returned. There is a situation wherein, we run DPA via SQL Server to make a WMI call to the Windows O/S to get counters using sp_OACreate and sp_OAMethod calls. These sessions from a SQL Server perspective still look active and in an executing state. However, stopping monitoring and issuing a normal kill operation of those sessions may not clean up these sessions. A restart of the SQL Server service will remove them, but they are likely to re-occur.

 

In order to help stabilize monitoring and get some of the metrics included in monitoring, here are some troubleshooting steps and actions you can take.

Environment

  • Ignite 8.X
  • DPA 9.0 to 9.2 and later versions

Cause

SQL Server makes calls to the Windows OS via sp_OACreate and sp_OAMethod stored procs. During the process, if WMI becomes unresponsive, the call remains active and the session from SQL Server appears active. This results in a connection that will neither complete nor error out.  At times, this causes DPA monitoring to fail.

Resolution

NOTE: This resolution is taken when you cannot upgrade to DPA 10.0+. 

  1. Make sure your OLE Automation is enabled on these instances (reference KB Troubleshoot WMI configuration for SQL Server resource metrics:
  • EXEC sp_configure 'Ole Automation Procedures';
  • sp_configure 'show advanced options', 1;
  • GO
  • sp_configure 'show advanced options', 1;
  • GO
  • RECONFIGURE;
  • GO
  • sp_configure 'Ole Automation Procedures', 1;
  • GO
  • RECONFIGURE;
  • GO
  1. Disable the following metrics on the monitored instance. The CPU and memory metrics tend to be the most problematic, you could potentially run with the Physical disk metrics still enabled even though they do make WMI calls.
  • CPU Utilization
  • CPU Queue Length
  • Physical I/O Rate
  • Physical Read Rate
  • Physical Write Rate
  • Disk Queue Length
  • Memory Utilization
  • Memory Paging Rate

DPA 10.0 

Several metrics were chaned WMI metrics to DMV's. Some of the DMV's we are now pulling from don't exist in SQL 2005 so you may want to turn the WMI metrics back on on those servers after upgrade. 

 

To turn on any of the WMI metrics that we removed to options >Administration tab>Advanced Options>DB instance options tab>click the support options check box>look for>SQL_SERVER_WMI_METRICS_ENABLED 

 

If this is set to true we start pulling back in all the WMI metrics that were there before. 

 

Here is the items that we changed. The items that say disabled were commented out in the XML file \iwc\tomcat\webapps\iwc\WEB-INF\classes\resources\metrics\default-metrics-queries.XML


Here is the items that will and will not work in the metrics for you by SQL version. 
SQL Server Version Metric Matrix
 

 

Last modified
15:16, 13 Feb 2017

Tags

Classifications

Public