PRTG Manual: WMI Microsoft SQL Server 2008 Sensor

The WMI Microsoft SQL Server 2008 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.

WMI Microsoft SQL Server 2008 Sensor

WMI Microsoft SQL Server 2008 Sensor

Sensor in Other Languages

  • Dutch: WMI Microsoft SQL Server 2008
  • French: Serveur WMI Microsoft SQL 2008
  • German: WMI Microsoft SQL Server 2008
  • Japanese: WMI Microsoft SQL Server 2008
  • Portuguese: Microsoft SQL Server 2008 WMI
  • Russian: WMI Microsoft SQL Server 2008
  • Simplified Chinese: WMI Microsoft SQL Server 2008
  • Spanish: WMI Microsoft SQL Server 2008

Remarks

  • 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.

i_podYou 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.

Add Sensor

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.

i_round_bluePRTG creates one sensor for each selection.

i_round_blueDisplay name and service name are provided as returned by the SQL Server.

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:

  • General Statistics: Read general performance counters. This shows the number of user connections and the number of logins and logouts per second.
  • Access Methods: Read access method counters. This shows the number of full scans, page splits, and table lock escalations (per second).
  • Buffer Manager: Read buffer manager counters. This shows the buffer cache hit ratio in percent and the number of database pages and stolen pages.
  • Memory Manager: Read memory manager counters. This shows the connection memory, optimizer memory, total server memory, target server memory, and SQL cache memory (in kb).
  • Locks: Read locks counters. This shows the number of lock requests and deadlocks (per second), and the average wait time.
  • SQL Statistics: Read SQL statistics. This shows the number of batch requests, SQL compilations, and SQL re-compilations (per second).

Depending on your selection, PRTG creates a sensor with the specified channels.

i_round_blueTo monitor more than one of the listed groups of performance counters, add the sensor several times for the respective instances.

Basic Sensor Settings

Click the Settings tab of a sensor to change its settings.

i_round_blueUsually, 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.

i_round_blueIt is not possible to enter tags with a leading plus (+) or minus (-) sign, nor tags with parentheses (()) or angle brackets (<>).

The sensor has the following default tags that are automatically predefined in the sensor's settings when you add the sensor:

  • wmisqlserversensor
  • wmisqlserversensor2008

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 (i_priority_1) to the highest priority (i_priority_5).

SQL Server Settings

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:

  • Automatic: Automatically select WMI class. We recommend this setting.
  • Manual: Manually enter a WMI class name. Select this option if your server instance returns an error code in automatic mode.

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.

Sensor Result

Define what PRTG does with the sensor results:

  • Discard sensor result: Do not store the sensor result.
  • Write sensor result to disk (file names: Result of Sensor [ID].*): Store the last result received from the sensor to the \Logs\sensors subfolder of the PRTG data directory on the probe system (master node, if in a cluster). File names: Result of Sensor [ID].* and Result of Sensor [ID].Data.*. This is for debugging purposes. PRTG overwrites these files with each scanning interval.

i_podThis option is not available when the sensor runs on the hosted probe of a PRTG hosted by Paessler instance.

SQL Counter Specific

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.

Sensor Display

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.

i_round_blueYou can set a different primary channel later by clicking b_channel_primary below a channel gauge on the sensor's Overview tab.

Graph Type

Define how different channels are shown for this sensor:

  • Show channels independently (default): Show a graph for each channel.
  • Stack channels on top of each other: Stack channels on top of each other to create a multi-channel graph. This generates a graph that visualizes the different components of your total traffic.
    i_round_redYou cannot use this option in combination with manual Vertical Axis Scaling (available in the channel settings).

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.

Inherited Settings

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 b_inherited_enabled under the corresponding setting name to disable the inheritance. You then see the options described below.

i_square_cyanFor more information, see section Inheritance of Settings.

Scanning Interval

Click b_inherited_enabled 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:

  • 30 seconds
  • 60 seconds
  • 5 minutes
  • 10 minutes
  • 15 minutes
  • 30 minutes
  • 1 hour
  • 4 hours
  • 6 hours
  • 12 hours
  • 1 day

i_round_blueYou can change the available intervals in the system administration on PRTG on premises installations.

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:

  • Set sensor to down immediately: Set the sensor to the Down status immediately after the first request fails.
  • Set sensor to warning for 1 interval, then set to down (recommended): Set the sensor to the Warning status after the first request fails. If the second request also fails, the sensor shows the Down status.
  • Set sensor to warning for 2 intervals, then set to down: Set the sensor to the Down status only after the third request fails.
  • Set sensor to warning for 3 intervals, then set to down: Set the sensor to the Down status only after the fourth request fails.
  • Set sensor to warning for 4 intervals, then set to down: Set the sensor to the Down status only after the fifth request fails.
  • Set sensor to warning for 5 intervals, then set to down: Set the sensor to the Down status only after the sixth request fails.

i_round_blueSensors that monitor via Windows Management Instrumentation (WMI) always wait at least one scanning interval before they show the Down status. It is not possible to immediately set a WMI sensor to the Down status, so the first option does not apply to these sensors. All other options can apply.

i_round_blueIf you define error limits for a sensor's channels, the sensor immediately shows the Down status. None of the interval options apply.

i_round_blueIf a channel uses lookup values, the sensor immediately shows the Down status. None of the interval options apply.

Schedules, Dependencies, and Maintenance Window

i_round_blueYou 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:

  • None
  • Saturdays
  • Sundays
  • Weekdays
  • Weekdays Eight-To-Eight (08:00 - 20:00)
  • Weekdays Nights (17:00 - 09:00)
  • Weekdays Nights (20:00 - 08:00)
  • Weekdays Nine-To-Five (09:00 - 17:00)
  • Weekends

i_square_cyanYou can create schedules, edit schedules, or pause monitoring for a specific time span. For more information, see section Schedules.

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:

  • Not set (monitor continuously): Do not set up a one-time maintenance window. Monitoring is always active.
  • Set up a one-time maintenance window: Set up a one-time maintenance window and pause monitoring. You can define a time span for a the pause below.

i_round_blueTo terminate an active maintenance window before the defined end date, change the time entry in Maintenance Ends to a date in the past.

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:

  • Use parent: Use the dependency type of the parent object.
  • Select a sensor: Use the dependency type of the parent object. Additionally, pause the current object if a specific sensor is in the Down status or in the Paused status because of another dependency.
  • Master sensor for parent: Make this sensor the master object for its parent device. The sensor influences the behavior of its parent device: If the sensor is in the Down status, the device is paused. For example, it is a good idea to make a Ping sensor the master object for its parent device to pause monitoring for all other sensors on the device in case the device cannot even be pinged. Additionally, the sensor is paused if the parent group is paused by another dependency.

i_round_blueTo test your dependencies, select Simulate Error Status from the context menu of an object that other objects depend on. A few seconds later, all dependent objects are paused. You can check all dependencies under Devices | Dependencies in the main menu bar.

Dependency

This setting is only visible if you enable Select a sensor above. Click b_search_light and use the object selector to select a sensor on which the current object will depend.

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.

i_round_redThis setting is not available if you set this sensor to Use parent or to be the Master sensor for parent. In this case, define delays in the parent device settings or in its parent group settings.

Access Rights

Click b_inherited_enabled 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:

  • Inherited: Inherit the access rights settings of the parent object.
  • No access: Users in this user group cannot see or edit the sensor. The sensor neither shows up in lists nor in the device tree.
  • Read access: Users in this group can see the sensor and view its monitoring results. They cannot edit any settings.
  • Write access: Users in this group can see the sensor, view its monitoring results, and edit its settings. They cannot edit its access rights settings.
  • Full access: Users in this group can see the sensor, view its monitoring results, edit its settings, and edit its access rights settings.

i_square_cyanFor more details on access rights, see section Access Rights Management.

More

i_square_blueKNOWLEDGE BASE

My WMI sensors don't work. What can I do?

Sensor Settings Overview

For more information about sensor settings, see the following sections: