PRTG Manual: WMI Microsoft SQL Server 2012 Sensor
The WMI Microsoft SQL Server 2012 sensor monitors the performance of a Microsoft SQL Server via Windows Management Instrumentation (WMI).
The sensor can monitor the following:
- Structured Query Language (SQL) General Statistics
- Access Methods
- Buffer Manager
- Memory Manager
- Locks Manager
- SQL Statistics
The channels that are actually available for a sensor depend on the performance counters that you choose during setup.
Channel |
Description |
---|---|
User Connections |
Number of user connections. Because each user connection consumes some memory, configuring overly high numbers of user connections could affect throughput. Set user connections to the maximum expected number of concurrent users. |
Logins |
Total number of logins started per second. |
Logouts |
Total number of logout operations started per second. |
Full Scans |
Number of unrestricted full scans per second. These can be either base-table or full-index scans. |
Page Splits |
Number of page splits per second that occur as the result of overflowing index pages. |
Table Lock Escalations |
Number of times that locks on a table were escalated. |
Buffer Cache Hit Ratio |
Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since an instance of SQL Server was started. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server. |
Database Pages |
Number of pages in the buffer pool with database content. |
Stolen Pages |
Number of pages used for miscellaneous server purposes (including procedure cache). |
Page Life Expectancy |
Number of seconds a page stays in the buffer pool without references. |
Connection Memory (KB) |
Total amount of dynamic memory the server is using for maintaining connections. |
Optimizer Memory (KB) |
Total amount of dynamic memory the server is using for query optimization. |
Total Server Memory (KB) |
Total amount of dynamic memory (in kilobytes) that the server is using. |
Target Server Memory (KB) |
Total amount of dynamic memory the server can consume. |
SQL Cache Memory (KB) |
Total amount of dynamic memory the server is using for the dynamic SQL cache. |
Lock Requests |
Number of new locks and lock conversions per second requested from the lock manager. |
Deadlocks |
Number of lock requests per second that resulted in a deadlock. |
Average Wait Time |
Average amount of wait time (in milliseconds) for each lock request that resulted in a wait. |
Batch Requests |
Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as input/output (I/O), number of users, cache size, or complexity of requests). High batch requests mean good throughput. |
SQL Compilations |
Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles because of recompiles. After the SQL Server user activity is stable, this value reaches a steady state. |
SQL Re-Compilations |
Number of SQL recompiles per second. Counts the number of times recompiles are triggered. In general, you want the recompiles to be low. |
- Dutch: WMI Microsoft SQL Server 2012
- French: Serveur WMI Microsoft SQL 2012
- German: WMI Microsoft SQL Server 2012
- Japanese: WMI Microsoft SQL Server 2012
- Portuguese: Microsoft SQL Server 2012 WMI
- Russian: WMI Microsoft SQL Server 2012
- Simplified Chinese: WMI Microsoft SQL Server 2012
- Spanish: WMI Microsoft SQL Server 2012
- Sensors that use the WMI protocol have a high impact on system performance. Try to stay below 200 WMI sensors per probe. Above this number, consider using multiple remote probes for load balancing.
- This sensor requires credentials for Windows systems in the settings of the parent device.
- This sensor requires WoW64 (Windows 32-bit on Windows 64-bit) for target systems that run Windows Server 2016.
- You can only add this sensor to a device (computer) running a Microsoft SQL database.
- This sensor supports the IPv6 protocol.
- This sensor has a high performance impact.
You cannot add this sensor to the hosted probe of a PRTG hosted by Paessler instance. If you want to use this sensor, add it to a remote probe device.
The Add Sensor dialog appears when you manually add a new sensor to a device. It only shows the setting fields that are required for creating the sensor. Therefore, you do not see all setting fields in this dialog. You can change (nearly) all settings in the sensor's Settings tab later.
Select the instances that you want to monitor. PRTG creates one sensor for each instance that you select in the Add Sensor dialog. The settings you select are valid for all sensors that you create when you finish this dialog.
The following settings in the Add Sensor dialog differ in comparison to the sensor's Settings tab.
SQL Server Settings
Setting |
Description |
---|---|
Server Instances |
You see a list with the names of all items that you can monitor. Add check marks in front of the respective lines to select the desired items. You can also use the check box in the table header to select all items or cancel the selection.
|
SQL Counter Specific
Setting |
Description |
---|---|
SQL Performance Counters |
You see a list of different groups of performance counters that the sensor can monitor for the instances that you selected above. Every sensor that PRTG creates for the server instances monitors the performance counters you select here. Choose from:
Depending on your selection, PRTG creates a sensor with the specified channels.
|
Click the Settings tab of a sensor to change its settings.
Usually, a sensor connects to the IP Address or DNS Name of the parent device on which you created the sensor. See the device settings for details. For some sensors, you can explicitly define the monitoring target in the sensor settings. See below for details on available settings.
Setting |
Description |
---|---|
Sensor Name |
Enter a meaningful name to identify the sensor. By default, PRTG shows this name in the device tree, as well as in alarms, logs, notifications, reports, maps, libraries, and tickets.
|
Parent Tags |
Shows tags that the sensor inherits from its parent device, parent group, and parent probe. This setting is shown for your information only and cannot be changed here. |
Tags |
Enter one or more tags. Confirm each tag with the Spacebar key, a comma, or the Enter key. You can use tags to group objects and use tag-filtered views later on. Tags are not case-sensitive. Tags are automatically inherited.
The sensor has the following default tags that are automatically predefined in the sensor's settings when you add the sensor:
|
Priority |
Select a priority for the sensor. This setting determines the position of the sensor in lists. The highest priority is at the top of a list. Choose from the lowest priority ( |
Setting |
Description |
---|---|
Service |
Shows the service that this sensor monitors. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew. |
Name |
Shows the name of the server instance that this sensor monitors. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew. |
WMI Class Name |
Select whether PRTG automatically selects the name of the WMI class used for monitoring:
|
WMI Class |
This setting is only visible if you enable Manual above. This setting is intended for experienced users only. Enter the WMI class name that the sensor uses for monitoring your server instance. |
Result Handling |
Define what PRTG does with the sensor result:
|
Setting |
Description |
---|---|
SQL Performance Counters |
Shows the performance counter that this sensor monitors. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew. |
Setting |
Description |
---|---|
Primary Channel |
Select a channel from the list to define it as the primary channel. In the device tree, the last value of the primary channel is always displayed below the sensor's name. The available options depend on what channels are available for this sensor.
|
Graph Type |
Define how different channels are shown for this sensor:
|
Stack Unit |
This setting is only visible if you enable Stack channels on top of each other as Graph Type. Select a unit from the list. All channels with this unit are stacked on top of each other. By default, you cannot exclude single channels from stacking if they use the selected unit. However, there is an advanced procedure to do so. |
By default, all of the following settings are inherited from objects that are higher in the hierarchy. You should change them centrally in the root group settings if necessary. To change a setting for this object only, click under the corresponding setting name to disable the inheritance. You then see the options described below.
For more information, see section Inheritance of Settings.
Click to interrupt the inheritance.
Setting |
Description |
---|---|
Scanning Interval |
Select a scanning interval from the dropdown list. The scanning interval determines the amount of time that the sensor waits between two scans. Choose from:
|
If a Sensor Query Fails |
Select the number of scanning intervals that the sensor has time to reach and to check a device again if a sensor query fails. Depending on the option that you select, the sensor can try to reach and to check a device again several times before the sensor shows the Down status. This can avoid false alarms if the monitored device only has temporary issues. For previous scanning intervals with failed requests, the sensor shows the Warning status. Choose from:
|
Schedules, Dependencies, and Maintenance Window
You cannot interrupt the inheritance for schedules, dependencies, and maintenance windows. The corresponding settings from the parent objects are always active. However, you can define additional schedules, dependencies, and maintenance windows. They are active at the same time as the parent objects' settings.
Setting |
Description |
---|---|
Schedule |
Select a schedule from the list. You can use schedules to monitor during a certain time span (days or hours) every week. Choose from:
|
Maintenance Window |
Select if you want to set up a one-time maintenance window. During a maintenance window, monitoring stops for the selected object and all child objects. They show the Paused status instead. Choose between:
|
Maintenance Begins |
This setting is only visible if you enable Set up a one-time maintenance window above. Use the date time picker to enter the start date and time of the one-time maintenance window. |
Maintenance Ends |
This setting is only visible if you enable Set up a one-time maintenance window above. Use the date time picker to enter the end date and time of the one-time maintenance window. |
Dependency Type |
Select a dependency type. You can use dependencies to pause monitoring for an object depending on the status of a different object. You can choose from:
|
Dependency |
This setting is only visible if you enable Select a sensor above. Click |
Dependency Delay (Sec.) |
This setting is only visible if you enable Select a sensor above. Define a time span in seconds for the dependency delay. After the master sensor for this dependency returns to the Up status, PRTG additionally delays the monitoring of the dependent objects by the time span you define. This can prevent false alarms, for example, after a server restart or to give systems more time for all services to start. Enter an integer value.
|
Click to interrupt the inheritance.
Setting |
Description |
---|---|
User Group Access |
Define the user groups that have access to the sensor. You see a table with user groups and group access rights. The table contains all user groups in your setup. For each user group, you can choose from the following group access rights:
|
KNOWLEDGE BASE
My WMI sensors don't work. What can I do?
For more information about sensor settings, see the following sections: