The SQL Server Monitor opens a connection to the specified SQL server and tests the performance of its subsystems to determine the server's general health. The overall performance of the server is typically dictated by its weakest performing subsystem.

The Monitor's ability to retrieve and analyze data allows an Administrator to:

• Use preconfigured performance counters made available by the Windows Management Instrumentation service to test multiple SQL sever subsystems at once.
• Quickly identify any performance degradation in critical SQL Server components.
• Determine the exact point of failure.
• Take corrective action before outages occur.

Top of page

The Test Parameters dialog box is used to specify the parameters the SQL Server Monitor will use to open a connection to the SQL server.

Server Name / Primary IP Address
To select a Server Name:

• Click the Browse... button to pop up the Server Listing dialog.
• Select the computer you want to monitor and click the Continue... button to populate the Server Name field.

Alternatively, you may also directly type in the primary IP Address or NetBIOS Name of the Exchange server you want to monitor.

If you enter an IP address, note that the SQL Sever Monitor requires it to be the Primary IP Address (the IP address of the computer's primary internal network adapter).

Note: Entering an IP address will eliminate any variables introduced by performing a lookup on the DNS server. If your network uses a DHCP server to dynamically assign IP addresses, enter an IP address only if it is "reserved". Otherwise, enter a Domain Name.

Credential for Monitoring
Assigning a Credential for Monitoring is an optional setting. If you wish to take advantage of ipMonitor's security model, you may assign a Credential to have the SQL Server Monitor impersonate an account with the administrative privileges required to access the SQL server. When the Monitor connects to the SQL server, it will use the Credential's account and password information to authenticate to the target machine.

If a Credential is not assigned, ipMonitor will use the current account privileges of the ipMonitor Service on the local machine.

To assign a Credential:

• Click the Select button to pop up the Credentials for Monitoring dialog
• Select an existing Credential from the Windows category
• To create a new Credential, click the New Credential button to start the Wizard

Note: For specific configuration details regarding configuration of a Credential to be used with the SQL Server Monitor, refer to Credentials for Monitoring :: SQL Server

Note: For more information about Credentials, refer to the section titled About Credentials.

Top of page

The Analysis of Test Results section is used to configure the required settings to perform diagnostics of critical SQL Server subsystems.

Note: You do not need to enable every counter. Although the Monitor can test all counters during the monitoring process, you may choose to only enable those counters that directly relate to the subsystems you wish to monitor.

SQL Performance Counters

Disk Read Bytes per Second
The number of bytes read on the disk, per second. This value is useful to determine Input/Output (IO) bottlenecks.

Disk Write Bytes per second
The number of bytes written to the disk, per second. This value is useful to determine Input/Output (IO) bottlenecks.

Page Reads per Second
The number of times pages are read from disk, per second, in order to resolve hard page faults. A hard page fault occurs when a process tries to reference a virtual memory section that is not working or that is no longer located at that address, so the data must be retrieved from disk.

Page Writes per Second
The number of times pages that were modified since their last retrieval are written to disk, per second.

Page File Percentage Used
The percentage of all page files currently in use by the SQL server process. Page files are shared by all processes running on the system. Therefore, if the SQL server process uses a large percentage of the page file space, other processes may not be able to use the memory on the system.

Process Queue Length
The number of threads currently waiting to be processed by the CPU. This value should not exceed 2 processes per processor listed on the system. A consistently high value is a sign of CPU congestion.

Context Switches
The rate of switches among threads. This occurs when the Operating System or the application is forced to change the executing thread on one processor to another thread executed on another processor. On single CPU systems, this value should remain as low as possible. However, on multiple CPU systems, context switches are frequent. A value lower then 10 000 switches is generally acceptable.

Number of Processes
The total number of processes running on the system.

SQL Server Counters

Minimum SQL Memory (kb)
The amount of memory remaining on the SQL server, in kilobytes (kb). A low value indicates that the system may require additional RAM.

Cache Hit Ratio Percentage
How often the SQL Server retrieves data from the buffer instead the hard disk. A low value indicates that the system may require additional RAM.

SQL Page Reads per Second
The number of pages read per second. This value does not take into account the pages that are read from the Windows NT virtual memory disk. A value of 20 pages per second or less is ideal.

SQL Page Writes per Second
The total number of physical page writes per second. This value includes checkpoint writes, lazy writer writes, and large block writes performed during index creation or bulk copy operations.

Lazy Writes per second
The number of times per second that the Lazy Writer process flushes dirty pages from the buffer to disk in order to free up buffer space.

Page Life Expectancy (ms)
The average length of time, in milliseconds (ms), that data pages remain in the buffer.

Lock Waits per Second
The number of requests that take place for a type of lock per second.

Average Lock Wait (ms)
The average wait time, in milliseconds (ms), to acquire a lock.

User Connections
The number of user connections (includes both RPC and Internal connections) currently taking place on the SQL Server. Please note that this value does not indicate the number of users connected.

Logins per Second
The number of user logins that occur per second.

Verify
Click the Verify button to test the Monitor's ability to connect to the SQL server and retrieve the appropriate value for each of the selected counters. Counters not respecting the specified threshold will display the retrieved value in red.

Top of page

When the Monitor is in an Up state, test results are reported as shown in the example below:

rtt - Round-Trip Time. This value indicates the time it took the test packet to reach the monitored resource and return a response to ipMonitor. Round-trip time is measured in milliseconds (ms).

When the Monitor is in a Warn, Down, or Lost state, the Last Result field indicates the problem encountered. Different Monitor types generate specific Error Codes in accordance with the technical capabilities of the Monitor. Refer to the Error Codes section of this document for details.

Top of page

In order for the SQL Server Monitor to properly monitor a SQL Server, Windows Management Instrumentation (WMI) must be enabled and functioning properly. In addition, the remote server must be accessible through an RPC connection in order to run the WMI queries.

With the exception of the Minimum SQL Memory (kb) and the Cache Hit Ratio Percentage counters, the default value represents a threshold rate that is not to be exceeded.

Counters are tested in the order that they appear. In the event of multiple counter failures, only the first counter error encountered will be reported.

The SQL Server Monitor's built-in internal sampling helps combat counter spikes. The Monitor will issue the WMI query 5 times (once every second), and then calculate an average based on the query results.

For information on settings common to all Monitor types, such as Identification, Timing, Notification Control and Recovery Parameters, refer to General Monitor Settings.

Top of page

The following error codes are generated by the SQL Server Monitor. Error codes are displayed in Real-time and Historical Reports. They can also be added to Alerts using Tokens.

Message	Details
Configuration information for this item is missing some required fields	A required element(s) for Monitor connectivity or testing was not supplied to ipMonitor.
Unable to connect to the remote device	The specified address to the connection-based Service is unavailable.
The remote device timed out before sending a response	The resource being monitored did not respond within the maximum allowable time.
The specified resource does not exist on the server	The specified application to be monitored was not found on the remote server being queried.
The response from the remote device was incomprehensible	The resource being monitored responded with invalid data.
The ipMonitor 8 service context does not have the necessary privileges to impersonate	ipMonitor was unable to process the request because of insufficient permissions.
The remote device provided an empty list of values	No data was returned from the remote server.
The remote device rejected the request due to an internal error	Upon connecting to the server, an initial message indicating the server is unavailable was received.
The threshold rate of the Disk Read Bytes per Second counter has been exceeded	The Disk Read Bytes per Second counter threshold has been exceeded, indicating a high level of Input/Output (I/O) activity.
The threshold rate of the Disk Write Bytes per Second counter has been exceeded	The Disk Write Bytes per Second counter threshold has been exceeded, indicating a high level of Input/Output (I/O) activity.
The threshold rate of the Page Reads per Second counter has been exceeded	The Page Reads per Second counter threshold has been exceeded, indicating excessive memory pressure and/or memory shortage.
The threshold rate of the Page Writes per Second counter has been exceeded	The Page Writes per Second counter threshold has been exceeded, indicating excessive memory pressure and/or memory shortage.
The threshold rate of the Context Switches counter has been exceeded	The Context Switches counter threshold has been exceeded, indicating a large number of CPU switches between threads.
The threshold rate of the Page File Percentage Used counter has been exceeded	The Page File Percentage Used counter threshold has been exceeded, indicating a lack of space in paging files.
The threshold rate of the Memory counter has been exceeded	The Memory counter threshold has been exceeded, indicating that the SQL Server may not have enough memory to operate properly.
The threshold rate of the Cache Hit Ratio counter has been exceeded	The Cache Hit Ratio counter threshold has been exceeded, indicating that the SQL Server cache is not operating efficiently.
The threshold rate of the Page Reads per Second counter has been exceeded	The Page Reads per Second counter threshold has been exceeded, indicating that the number of physical database pages read per second is too high.
The threshold rate of the Page Writes per Second counter has been exceeded	The Page Writes per Second counter threshold has been exceeded, indicating that the number of physical database pages written to per second is too high.
The threshold rate of the Lazy Writes per Second counter has been exceeded	The Lazy Writes per Second counter threshold has been exceeded, indicating that the Lazy Writer process is moving too many pages from the buffer to disk in order to free up buffer space.
The threshold rate of the Page Life Expectancy counter has been exceeded	The Page Life Expectancy counter threshold has been exceeded, indicating that the length of time the SQL Server estimates a page will remain in the buffer cache is too short.
The threshold rate of the Lock Waits counter has been exceeded	The Lock Waits counter threshold has been exceeded, indicating that a large number of lock requests could not be acquired immediately.
The threshold rate of the Lock Average Time (ms) counter has been exceeded	The Lock Average Time (ms) counter threshold has been exceeded, indicating that the average lock time for latch requests is too high.
The threshold rate of the User Connections counter has been exceeded	The User Connections counter threshold has been exceeded, indicating that too many users are currently connected to the SQL Server.
The threshold rate of the Logins per Second counter has been exceeded	TThe Logins per Second counter threshold has been exceeded, indicating that too many SQL Server login attempts are occurring per second.

Top of page