<%NUMBERING1%>.<%NUMBERING2%>.<%NUMBERING3%> PRTG Manual: WMI Microsoft SQL Server 2005 Sensor (Deprecated)

Important Notice

This sensor is deprecated.

Monitoring of Microsoft SQL Server 2005 using PRTG is discontinued. Microsoft SQL Server 2005 cannot be monitored with PRTG anymore with the latest updates of SQL Server 2005. The reason for this is a software update delivered by Microsoft in August 2012. The following updates cause this issue:

  • Security Update for SQL Server 2005 Service Pack 4 (KB2716429)
  • Security Update for SQL Server 2005 Service Pack 4 (KB2716427)

We have made reasonable effort to fix this from our side but we were unable to do so. We do not have instructions to circumvent this issue at this time. Please ask the vendor to fix this.

More

icon-book-bulbKnowledge Base: Why does my WMI Microsoft SQL Server 2005 Sensor not work anymore?

The WMI Microsoft SQL Server 2005 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 Overview

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 currently 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 Sensor

WMI Microsoft SQL Server Sensor

Remarks

  • Sensors using 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.
  • Requires Windows credentials in the settings of the parent device.
  • 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.

icon-prtg-on-demandYou 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

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.

icon-i-roundPRTG creates one sensor for each selection.

icon-i-roundDisplay name and service name are provided as returned by the SQL Server.

SQL Counter Specific

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.

icon-i-roundTo monitor more than one of the listed groups of performance counters, add the sensor several times for the respective instances.

Sensor Settings

On the details page of a sensor, click the Settings tab to change its settings.

icon-i-roundUsually, 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.

Basic Sensor Settings

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 this sensor inherits from its parent device, group, and probe. This setting is shown for your information only and cannot be changed here.

Tags

Enter one or more Tags, separated by spaces or commas. You can use tags to group sensors and use tag–filtered views later on. Tags are not case sensitive. We recommend that you use the default value.

There are default tags that are automatically predefined in a sensor's settings when you add a sensor. See section Default Tags below.

You can add additional tags to the sensor if you like. Other tags are automatically inherited from objects further up in the device tree. These are visible above as Parent Tags.

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

Priority

Select a priority for the sensor. This setting determines where the sensor is placed in sensor lists. A sensor with a top priority is at the top of a list. Choose from one star (low priority) to five stars (top priority).

Default Tags

wmisqlserversensor

SQL Server Settings

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 field 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 name: Result of Sensor [ID].txt): Store the last result received from the sensor to the Logs (Sensors) subfolder of the PRTG data directory on the probe system that the sensor runs on (on the Master node if in a cluster). File names: Result of Sensor [ID].txt and Result of Sensor [ID].Data.txt. This is for debugging purposes. PRTG overwrites these files with each scanning interval.
    For more information on how to find the folder used for storage, see section Data Storage.

icon-prtg-on-demandThis option is not available when the sensor runs on the Hosted Probe of a PRTG hosted by Paessler instance.

SQL Counter Specific

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

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.

icon-i-roundYou can set a different primary channel later by clicking the pin symbol of a channel 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.
    icon-i-roundThis option cannot be used in combination with manual Vertical Axis Scaling (available in the Sensor Channel Settings settings).

Stack Unit

This field 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 and should be changed there if necessary. Often, best practice is to change them centrally in the Root group's settings. For more information, see section Inheritance of Settings. To change a setting for this object only, disable inheritance by clicking the button next to inherit from under the corresponding setting name. You then see the options described below.

Scanning Interval

Click inherited_settings_button to interrupt the inheritance. See section Inheritance of Settings for more information.

Scanning Interval

Select a scanning interval (seconds, minutes, or hours). The scanning interval determines the amount of time that the sensor waits between two scans. You can change the available intervals in the system administration on PRTG on premises installations.

If a Sensor Query Fails

Define the number of scanning intervals that the sensor has time to reach and check a device again in case a sensor query fails. Depending on the option that you select, the sensor can try to reach and check a device again several times before the sensor shows a 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 a Warning status. Choose from:

  • Set sensor to down immediately: Set the sensor to a Down status immediately after the first failed request.
  • Set sensor to warning for 1 interval, then set to down (recommended): Set the sensor to a Warning status after the first failed request. If the following request also fails, the sensor shows an error.
  • Set sensor to warning for 2 intervals, then set to down: Set the sensor to a Down status only after three consecutively failed requests.
  • Set sensor to warning for 3 intervals, then set to down: Set the sensor to a Down status only after four consecutively failed requests.
  • Set sensor to warning for 4 intervals, then set to down: Set the sensor to a Down status only after five consecutively failed requests.
  • Set sensor to warning for 5 intervals, then set to down: Set the sensor to a Down status only after six consecutively failed requests.

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

icon-i-roundIf you define error limits for a sensor's channels, the sensor immediately shows a Down status. No "wait" option applies.

icon-i-roundIf a channel uses lookup values, the sensor immediately shows a Down status. No "wait" options apply.

Schedules, Dependencies, and Maintenance Window

icon-i-roundYou 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 settings here. They are active at the same time as the parent objects' settings.

Schedule

Select a schedule from the list. Schedules can be used to monitor for a certain time span (days or hours) every week.

icon-book-arrowsYou can create schedules, edit schedules, or pause monitoring for a specific time span. For more information, see section Account Settings—Schedules.

icon-i-roundSchedules are generally inherited. New schedules are added to existing schedules, so all schedules are active at the same time.

Maintenance Window

Specify if you want to set up a one-time maintenance window. During a maintenance window, the current object and all child objects are not monitored. They are in a Paused status instead. Choose between:

  • Not set (monitor continuously): No maintenance window is set and monitoring is always active.
  • Set up a one-time maintenance window: Pause monitoring within a maintenance window. You can define a time span for a monitoring pause below and change it even for a currently running maintenance window.

icon-i-roundTo terminate a current maintenance window before the defined end date, change the time entry in Maintenance Ends to a date in the past.

Maintenance Begins

This field 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 maintenance window.

Maintenance Ends

This field 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 maintenance window.

Dependency Type

Define a dependency type. You can use dependencies to pause monitoring for an object depending on the status of another 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 another specific sensor is in a Down status or in a Paused status caused by 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 a 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.

icon-i-roundTo 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 field is only visible if you enable Select a sensor above. Click the Search button and use the object selector to select a sensor on which the current object will depend.

Dependency Delay (Sec.)

This field is only visible if you enable Select a sensor above. Define a time span in seconds for dependency delay.

After the master sensor for this dependency comes back to an Up status, monitoring of the dependent objects is additionally delayed by the defined time span. This can help avoid false alarms, for example, after a server restart, by giving systems more time for all services to start up. Enter an integer value.

icon-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 inherited_settings_button to interrupt the inheritance. See section Inheritance of Settings for more information.

User Group Access

Define the user groups that have access to the selected object. A table with user groups and types of access rights is shown. It contains all user groups from your setup. For each user group, you can choose from the following access rights:

  • Inherited: Use the access rights settings of the parent object.
  • None: Users in this group cannot see or edit the object. The object neither shows up in lists nor in the device tree. Exception: If a child object is visible to the user, the object is visible in the device tree but it cannot be accessed.
  • Read: Users in this group can see the object and review its monitoring results.
  • Write: Users in this group can see the object, review its monitoring results, and edit its settings. They cannot edit access rights settings.
  • Full: Users in this group can see the object, review its monitoring results, edit its settings, and edit access rights settings.

You can create new user groups in the System Administration—User Groups settings. To automatically set all objects further down in the hierarchy to inherit this object's access rights, set a check mark for the Revert children's access rights to inherited option.

icon-book-arrowsFor more details on access rights, see section User Access Rights.

More

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

Edit Sensor Channels

To change display settings, spike filtering, and limits, switch to the sensor's Overview tab and click the gear icon of a specific channel. For detailed information, see section Sensor Channel Settings.

Notification Triggers

Click the Notification Triggers tab to change notification triggers. For detailed information, see section Sensor Notification Triggers Settings.

Others

For more general information about settings, see section Object Settings.

Sensor Settings Overview

For information about sensor settings, see the following sections:

Keywords: