Updated July 20th, 2016
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.
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.
NOTE: This resolution is taken when you cannot upgrade to 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