SQL Diagnostic Manager allows you to enable either OLE automation or direct Windows Management Interface (WMI) procedures for monitoring OS metrics.

If your SQL Server instance includes enabled Lightweight Pooling, SQL Diagnostic Manager cannot collect OS metrics information.

If SQL Diagnostic Manager cannot provide OS metrics, a message displays on the Dashboard and Services Summary views providing more information.

When SQL Diagnostic Manager cannot collect OS Metrics due to the disabled collection, a bar displays a link to enable OS metric collection procedures for your SQL Server version.

CPU UsageSQL Diagnostic Manager collects metrics that track the performance of the computers that host your monitored SQL Server instances. These metrics are the following:

OS Metrics tab

You can access the OS Metrics tab of the Monitor SQL Server Properties window by right-clicking the appropriate monitored SQL Server instance and selecting Properties. When SQL Diagnostic Manager displays the monitored SQL Server Properties window, click OS Metrics option from the left side tab options.

Once the right-side Properties window displays the OS Metrics configuration options, you find the following options of how the OS metrics are being collected:

 The advantages of using OLE automation are the following:

  1. You do not require any additional ports to be opened, since the standard SQL communication port is sufficient.
  2. SQL Diagnostic Manager uses the SQL Server service account permissions to capture WMI metrics.
  3. And, uses SQL Authentication.
When using OLE automation you must enable this option; also, OLE automation opens up possibilities for a sysadmin to run queries to reach outside SQL, although you must be a sysadmin to run these queries, it may not be the best option for some environments.

The advantages of using WMI are the following:

  1. You do not require additional SQL Server configuration adjustments.
  2. You do not require SQL Server to gather the performance data because the collection service reaches directly to WMI. 

When using direct WMI are that you must have RPC port 135 opened for WMI request initiation access, you must have permissions configured on each SQL instance to allow for remote access to WMI, and you must use a Windows account. For more information about other high-end ports open in firewall to allow WMI to communicate, see the Microsoft document, .

It is preferred that the WMI user is a local administrator on the monitored SQL Server instance. However, if you do not want to grant administrator access, use the following steps to configure remote WMI access in Microsoft Windows:

  1. Add the user account to the Performance Log Users and distributed COM users groups.
  2. Grant privileges to WMI.

You also may need to add the WMI user account to the following policies:

For more information about using a direct WMI connection, see the Microsoft document, 

Edit your specific registry settings to allow WMI to run out-of-process. To edit these registry settings, perform the following steps:

  1. In the cd image of the SQL Diagnostic Manager install, open Scripts folder.
  2. Copy the SQLdmoutofprocoleautomation.reg file onto each of your monitored SQL Server instances.
  3. Run this file on each of your monitored SQL Server instances.

SQL Diagnostic Manager allows you to configure the WMI timeout value. In some environments, customers may experience WMI timeouts when their machine is too busy to respond in a timely manner. Although WMI timeouts should not be ignored and often point to an environmental issue, you can control this value when the behavior displayed is normal in your environment. The default WMI timeout value in SQL Diagnostic Manager is of 90 seconds to reduce the number of intermittent errors.

To configure the WMI timeout value:

  1. NAvigate to the SQL Diagnostic Manager install directory
  2. Open SQLdmCollectionService.exe.Config
  3. Modify the <CollectionService> node under the <Services> node
  4. Add wmiQueryTimeOut="300" as a parameter to this node
  5. Save and close file
  6. Restart the collection service

    Sample configuration entry:

<Services>

<CollectionService instanceName="Default" servicePort="5167"

managementServiceAddress="Server" managementServicePort="5166"

heartbeatIntervalSeconds="180" wmiQueryTimeOut="300"/>

</Services>

Issues you may encounter before SQL Diagnostic Manager begins collecting OS Metrics:

The following issues require user action before SQL Diagnostic Manager can begin collecting OS metrics:

SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
IDERA WebsiteProductsPurchaseSupportCommunityAbout UsResources Legal