PRTG Manual: WMI Microsoft SQL Server 2017 Sensor

The WMI Microsoft SQL Server 2017 sensor monitors the performance of a Microsoft SQL Server via Windows Management Instrumentation (WMI).

WMI Microsoft SQL Server 2017 Sensor

WMI Microsoft SQL Server 2017 Sensor

i_square_cyanFor a detailed list and descriptions of the channels that this sensor can show, see section Channel List.

Sensor in Other Languages

  • Dutch: WMI Microsoft SQL Server 2017
  • French: Microsoft SQL serveur 2017 (WMI)
  • German: WMI Microsoft SQL Server 2017
  • Japanese: WMI Microsoft SQL Server 2017
  • Portuguese: Microsoft SQL Server 2017 (WMI)
  • Russian: WMI Microsoft SQL Server 2017
  • Simplified Chinese: WMI Microsoft SQL Server 2017
  • Spanish: Microsoft SQL Server 2017 (WMI)

Remarks

  • This sensor has a high performance impact. Try to stay below 200 WMI sensors in total 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.

i_podYou cannot add this sensor to the hosted probe of a PRTG Hosted Monitor 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 settings that are required to create the sensor. You can change nearly all settings on the sensor's Settings tab after creation.

SQL Server Settings

Setting

Description

Server Instances

Select the instances that you want to monitor. PRTG creates one sensor for each instance that you select.

i_round_blueEnable check boxes in front of the respective lines to select the items. Use the check box in the table header to select all items or to cancel the selection. In large tables, use the search function in the upper-right corner.

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

SQL Counter Specific

Setting

Description

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.

Basic Sensor Settings

Basic Sensor Settings

Setting

Description

Sensor Name

Enter a name to identify the sensor.

Parent Tags

Shows tags that the sensor inherits from its parent device, parent group, and parent probe.

i_round_blueThis setting is for your information only. You cannot change it.

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 (<>).

i_round_blueFor performance reasons, it can take some minutes until you can filter for new tags that you added.

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

  • wmisqlserversensor
  • wmisqlserversensor2017

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

i_round_blueUsually, a sensor connects to the IP Address/DNS Name of the parent device. See the device settings for details. For some sensors, you can explicitly define the monitoring target in the sensor settings.

SQL Server Settings

SQL Server Settings

SQL Server Settings

Setting

Description

Service

Shows the service that this sensor monitors.

i_round_bluePRTG shows this value 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.

i_round_bluePRTG shows this value for reference purposes only. If you need to change this value, add the sensor anew.

Naming Method

Select whether PRTG automatically selects the name of the WMI class used for monitoring:

  • Automatically determine the WMI class name: Automatically select WMI class. We recommend this setting.
  • Manually enter the WMI class name: 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 select Manually enter the WMI class name above. Enter the WMI class name that the sensor uses to monitor the server instance.

Result Handling

Define what PRTG does with the sensor result:

  • Discard result: Do not store the sensor result.
  • Store result: Store the last sensor result in the \Logs\sensors subfolder of the PRTG data directory on the probe system. The file names are Result of Sensor [ID].txt and Result of Sensor [ID].Data.txt. This setting is for debugging purposes. PRTG overwrites these files with each scanning interval.

i_round_blueIn a cluster, PRTG stores the result in the PRTG data directory of the master node.

SQL Counter Specific

SQL Counter Specific

SQL Counter Specific

Setting

Description

Performance Counters

Shows the performance counter that this sensor monitors.

i_round_bluePRTG shows this value for reference purposes only. If you need to change this value, add the sensor anew.

Sensor Display

Sensor Display

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 these settings are inherited from objects that are higher in the hierarchy. We recommend that you 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 and to display its options.

i_square_cyanFor more information, see section Inheritance of Settings.

Scanning Interval

Scanning Interval

Scanning Interval

i_square_cyanFor more information, see section Root Group Settings, section Scanning Interval.

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.

Schedules, Dependencies, and Maintenance Window

Schedules, Dependencies, and Maintenance Window

i_square_cyanFor more information, see section Root Group Settings, section Schedules, Dependencies, and Maintenance Window.

Access Rights

Access Rights

Access Rights

i_square_cyanFor more information, see section Root Group Settings, section Access Rights.

Channel List

i_round_blueWhich channels the sensor actually shows might depend on the target device, the available components, and the sensor setup.

Channel

Description

Average Wait Time

The average amount of wait time for each lock request that resulted in a wait

Batch Requests

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

Buffer Cache Hit Ratio

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

Connection Memory (KB)

The total amount of dynamic memory the server is using for maintaining connections

Database Pages

The number of pages in the buffer pool with database content

Deadlocks

The number of lock requests per second that resulted in a deadlock

Full Scans

The number of unrestricted full scans per second. These can be either base-table or full-index scans.

Lock Requests

The number of new locks and lock conversions per second requested from the lock manager

Logins

The total number of logins started per second

Logouts

The total number of logout operations started per second

Optimizer Memory (KB)

The total amount of dynamic memory the server is using for query optimization

Page Life Expectancy

The number of seconds a page stays in the buffer pool without references

Page Splits

The number of page splits per second that occur as the result of overflowing index pages

SQL Cache Memory (KB)

The total amount of dynamic memory the server is using for the dynamic SQL cache

SQL Compilations

The 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

The number of SQL recompiles per second. Counts the number of times recompiles are triggered. In general, you want the recompiles to be low.

Stolen Pages

The number of pages used for miscellaneous server purposes (including procedure cache)

Table Lock Escalations

The number of times that locks on a table were escalated

Target Server Memory (KB)

The total amount of dynamic memory the server can consume

Total Server Memory (KB)

The total amount of dynamic memory that the server is using

User Connections

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

More

i_square_blueKnowledge Base

What security features does PRTG include?

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