PRTG Manual: WMI Microsoft SQL Server 2008 Sensor

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 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 will stay 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 currently.

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 I/O, number of users, cache size, complexity of requests, and so on). 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 due to recompiles. After 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.

MS SQL Server Instance

You see a list of all server instances available on the device. Display name and service name are provided as returned by the SQL server. Please choose one or more instances by adding a check mark in front of the respective line. For each instance one sensor will be created.

SQL Server Version

Enter the version of the SQL server. Usually, you can keep the default value.

SQL Performance Counters

You see a list of different performance counters which can be monitored for the instance(s) selected above. The sensors created for the server instances will all monitor the performance counter you select here. Choose from:

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

Depending on your selection, a sensor with the specified channels will be created. Note: In order to monitor several performance counters for an instance, please add the sensor several times.

Sensor Name

Enter a meaningful name to identify the sensor. The name will be shown by default in the device tree and in all alarms.

Tags

Enter one or more tags, separated by space or comma. You can use tags to group sensors and use tag-filtered views later on. Tags are not case sensitive. We recommend using the default value. You can add additional tags to it, if you like. Other tags are automatically inherited from objects further up in the device tree. Those are not visible here.

Priority

Select a priority for the sensor. This setting determines where the sensor will be placed in sensor lists. Top priority will be at the top of a list. You can choose from one star (low priority) to five stars (top priority).

Service

Shows the service monitored by this sensor. Once a sensor is created this value cannot be changed. It is shown for reference purposes only. If you need to change this, please add the sensor anew.

Name

Shows the name of the server instance monitored by this sensor. Once a sensor is created this value cannot be changed. It is shown for reference purposes only. If you need to change this, please add the sensor anew.

WMI Class Name

Select whether PRTG will select the name of the WMI class used for monitoring automatically. Choose between:

• Automatic: Choose WMI class automatically. This is the recommended setting.
• Manual: Manually enter a WMI class name. Use this if your server instance returns an error code in automatic mode.

WMI Class

This field is only shown if manual WMI class selection is enabled above. This setting is intended for experienced users only. Enter the WMI class name that will be used for monitoring your server instance.

Sensor Result

Define what will be done with the results the sensor receives. Choose between:

• Discard sensor result: Do not store the results.
• Write sensor result to disk (Filename: "Result of Sensor (ID].txt"): Store the last result received to the "Logs (Sensors)" directory (on the Master node, if in a cluster). This is for debugging purposes. The file will be overridden with each scanning interval. For information on how to find the folder used for storage, please see Data Storage section.

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 will always be displayed underneath the sensor's name. The available options depend on what channels are available for this sensor. Note: You can set another primary channel later by clicking on the pin symbol of a channel in the sensor's overview tab.

Chart Type

Define how different channels will be shown for this sensor.

• Show channels independently (default): Show an own 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 will generate an easy-to-read graph which visualizes the different components of your total traffic. Note: This option cannot be used in combination with manual Vertical Axis Scaling (available in the Sensor Channels Settings settings).

Stack Unit

This setting is only available if stacked graphs are selected above. Choose a unit from the list. All channels with this unit will be 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.

Scanning Interval

Scanning Interval

The scanning interval determines the time the sensor waits between two scans. Select a scanning interval (seconds, minutes, or hours) from the list. You can change the available intervals in the system administration.

Note: Inheritance for schedules, dependencies, and maintenance windows cannot be interrupted; the according settings from the parent objects will always be active. However, you can define additional settings here. They will be active in parallel to the parent objects' settings.

Schedule

Select a schedule from the list. Schedules can be used to monitor for a certain time span (days, hours) throughout the week. With the period list option it is also possible to pause monitoring for a specific time span. You can create new schedules and edit existing ones in the account settings. Note: Schedules are generally inherited. New schedules will be added to existing ones, so all schedules are active.

Maintenance Window

Specify if you want to set-up a one-time maintenance window. During a maintenance window this object and all child objects will not be monitored. They will enter a paused state then. Choose between:

• Not set (monitor continuously): No maintenance window will be set.
• Set up a one-time maintenance window: Pause monitoring within a maintenance window.

Maintenance Begins At

This field is only visible if maintenance window is enabled above. Use the date time picker to enter the start date and time of the maintenance window.

Maintenance End At

This field is only visible if maintenance window is enabled above. Use the date time picker to enter the end date and time of the maintenance window.

Dependency Type

Define a dependency type. Dependencies can be used to pause monitoring for an object depending on the status of another. You can choose between:

• Use parent: Pause the current sensor if the device it is created on is in a Down status, or if it is paused by another dependency.
• Select object: Pause the current sensor if the device it is created on is in an Down status, or if it is paused by another dependency. Additionally, pause the current sensor if a specific other object in the device tree is in a Down status, or if it is paused by another dependency. Select below.
• Master object for parent: Make this sensor the master object for its parent device. The sensor will influence the behavior of the device it is created on: If the sensor is in a Down status, the device will be 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 will be paused if the parent group of its parent device is in a Down status, or if it is paused by another dependency.

Note: Testing your dependencies is easy! Simply choose Simulate Error Status from the context menu of an object that other objects depend on. A few seconds later all dependent objects should be paused.

Dependency

This field is only visible if the select object option is enabled above. Click on the reading-glass symbol and use the object selector to choose an object on which the current sensor will be dependent on.

Delay (Seconds)

Define a time span. After the master object for this dependency comes back to an Up status, monitoring of the depending objects will be 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. Please enter an integer value in seconds. Note: This setting is not available if you choose this sensor to be the Master object for parent. In this case, please define delays in the parent Device Settings or the superior Group Settings.

Access Rights

User Group Access

Define which user group(s) will have access to the object you're editing. A table with user groups and right 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 settings of the parent object.
• None: Users in this group cannot see or edit the object. The object does not show up in lists and in the device tree. Exception: If a child object is visible to the user, the object is visible in the device tree, though not accessible.
• 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 the object's settings. They cannot edit access rights settings.
• Full: Users in this group can see the object, review its monitoring results, edit the object's 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.

For more details about access rights, please see section User Access Rights.