SQLdm requires that you enable either OLE automation or direct Windows Management Interface (WMI) procedures for monitoring OS metrics. In addition, if your SQL Server instance includes enabled Lightweight Pooling, SQLdm cannot collect OS metrics information. If SQLdm cannot provide OS metrics, a message displays on the Dashboard and Services Summary views providing more information.
When SQLdm cannot collect OS Metrics due to disabled collection, a bar displays a link to enable OS metric collection procedures for your SQL Server version.
SQLdm collects metrics that track the performance of the computers that host your monitored SQL Server instances. These metrics include:
- CPU Usage
- Processor Queue Length
- Memory Paging
- Total Memory Usage
- SQL Server Memory Usage
- Disk Queue Length
Access the 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 then selecting Properties. Click OS Metrics when SQLdm displays the Monitored SQL Server Properties window.
Disabling OS metric collection
OS metrics collection is not required in SQLdm. However, if you disable this feature, SQLdm ceases to collect OS metrics and does not raise any previously-associated alerts. You can easily re-enable OS metric collection at any time if you decide that you want to continue collecting these metrics.
Permissions necessary for direct WMI collection
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:
- Add the user account to the Performance Log Users and Distributed COM users groups.
- Grant privileges to WMI.
You also may need to add the WMI user account to the following policies:
- Act as part of the operating system
- Log on as a batch job
- Log on as a service
- Replace a process level token
For more information about using a direct WMI connection, see the Microsoft document, Securing a Remote WMI Connection.
Configure WMI to run out-of-process
Edit your specific registry settings to allow WMI to run out-of-process.
To edit these registry settings, perform the following steps:
- In the cd image of the SQLdm install, open the Scripts folder.
- Copy the SQLdmoutofprocoleautomation.reg file onto each of your monitored SQL Server instances.
- Run this file on each of your monitored SQL Server instances.
The following problems require user action before SQLdm can begin collecting OS metrics:
OLE Automation Procedures Disabled
Select the Collect Operating System data using OLE Automation option to enable OLE Automation procedures.
OLE Automation Procedures Unavailable
The stored procedures associated with OLE Automation are missing on the SQL Server instance. SQLdm cannot capture OS metrics until these stored procedures are available on the SQL Server instance.
WMI Service Not Running
The WMI Service on the monitored SQL Server instance may not be running. SQLdm cannot collect OS metrics until this service is started.
Lightweight Pooling Enabled
This is an advanced feature used in symmetric multiprocessing environments in SQL Server. SQLdm cannot collect OS metrics until this option is disabled on your monitored SQL Server instances.
Configure WMI timeouts
SQLdm 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 SQLdm is of 90 seconds to reduce the amount of intermittent errors.
To configure the WMI timeout value:
- Navigate to the SQLdm install directory
- Open
SQLdmCollectionService.exe.Config - Modify the <CollectionService> node under the <Services> node
- Add
wmiQueryTimeOut=”300”as a parameter to this node - Save and close file
- Restart the collection service
Sample configuration entry:
<Services>
<CollectionService instanceName="Default" servicePort="5167"
managementServiceAddress="Server" managementServicePort="5166"
heartbeatIntervalSeconds="180" wmiQueryTimeOut="300"/>
</Services>