Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

SQL Server (Advanced Metrics)

SQL Server (Advanced Metrics) monitor collects information on the availability and performance of individual SQL Server databases.

You only need to configure one SQL Server (Advanced Metrics) monitor for each system. You can, however, create multiple SQL Server (Advanced Metrics) monitors for a system if you need to separately capture different SQL Server performance metrics. See the section for more information.

Info

You cannot run SQL Server (Advanced Metrics) monitor against a VM or Agent-collected element or entity. This feature works properly with Agent and WMI elements as it does not directly connect to the database, but rather via performance counters. The VM or Agent-controlled element cannot be used because it has no access to those counters.

 

For example, consider a host configured to have the following:

  • an Uptime Infrastructure Monitor agent installed
  • two database instances
  • four databases

The SQL Server (Advanced Metrics) monitor can capture performance information from all four databases. It can also aggregate the information to present a single performance value for each metric.

Using Multiple SQL Server (Advanced Metrics) Monitors

You can create several SQL Server (Advanced Metrics) monitors for a system if you must separately capture different SQL Server performance metrics. For example, the SQL Server (Advanced Metrics) monitor provides metrics for SQL Server locks including lock requests, waits, and averages. For information about locks, see the Uptime Knowledge Base article “SQL Server Locks.”

Lock requests do not always provide meaningful information. When you compare the length of waits with the number of lock requests, the length of the lock waits should be much lower than requests. If the lengths of waits and requests are about the same, then there is a performance problem. When the average lock wait time is high, there is a problem with SQL Server.

Configuring SQL Server (Advanced Metrics) Monitors

To configure SQL Server (Advanced Metrics) monitors, do the following:
  1. In the SQL Server (Advanced Metrics) monitor template, complete the monitor information fields.
    To learn how to configure monitor information fields, see Monitor Identification.
  2. In the Instance field, type the name of the SQL server instance to which you want to connect.

    Info

    If you have configured your agent to use SSL but do not select Use SSL, Uptime Infrastructure Monitor does not receive performance information.

  3. Complete the following options by clicking the checkbox beside each option, then specifying a warning and critical threshold.
    If the thresholds that you set are exceeded, then Uptime Infrastructure Monitor generates an alert. For more information, see Configuring Warning and Critical Thresholds.
    • Lock Wait / Sec.
      The amount of time, in seconds, to wait for a database lock. For more information about locks, see the Uptime Knowledge Base article “SQL Server Locks.”
    • Lock Requests / Sec.
      The number of new database locks and lock conversions that are requested from the lock manager every second. For more information about locks, see the Uptime Knowledge Base article “SQL Server Locks.”
    • Average Lock Wait Time
      The average time, in milliseconds, that you must wait for database locks to clear before Uptime Infrastructure Monitor sends an alert.
    • User Connections
      The number of user connections that are allowed before Uptime Infrastructure Monitor sends an alert.
      For example, a single host is running two databases. There are five users logged on to the first database and three users logged on to the second database. The total number of user connections is eight.
    • Transactions / Sec.
      In the Warning and Critical threshold fields, enter the number of transactions started for the databases across the host per second.
    • Data File(s) Size / KB
      The cumulative size of all the files in all of the databases on the host system.
      This metric is returned from the SQL Server Database object. The Database object provides such information about the database as the amount of free log space available or the number of active transactions in the database. There can be multiple instances of this object.
    • Total Latch Wait Time (ms)
      The total time, in milliseconds, that it takes to complete the latch requests that were waiting over the last second.
    • Latch Waits / Sec.
      The number of latch requests that were not immediately granted, and which waited before the grant.
    • Average Latch Wait Time (ms)
      The average time, in milliseconds, that latch requests had to wait before the grant.
    • Maximum Workspace Memory (KB)
      The maximum amount of memory, in kilobytes, that the server has available to execute such processes as sort, bulk copy, hash, and index creation.
      This metric is returned by the SQL Server Memory Manager object, which monitors overall server memory usage. By monitoring overall server memory usage, you can determine whether:
      • Bottlenecks exist due to a lack of available physical memory for storing frequently accessed data in cache. If so, SQL Server must retrieve the data from the disk.
      • You can improve query performance by adding more memory or by making more memory available to the data cache or to SQL Server internal structures.
    • Connection Memory (KB)
      The total amount of dynamic memory, in kilobytes, that the server is using to maintain connections.
    • SQL Cache Memory (KB)
      The amount of memory, in kilobytes, that the server is using for the dynamic SQL cache.
    • Total Server Memory (KB)
      The total amount of committed memory from the buffer pool, in kilobytes, that the server is using.
    • Response Time
      Enter the Warning and Critical Response Time thresholds. If the amount of time taken to perform a check exceeds the defined thresholds, it could indicate a problem that requires investigation.
  4. To save the data from the thresholds for graphing or reporting, click the Save for Graphing checkbox beside each of the metrics that you selected in step 3.
  5. Complete the following settings:
  6. Click Finish.

...

Sybase

The Sybase monitor does the following:

  • determines if the database is responding on the standard port
  • sends Sybase/Transact-SQL scripts to the database for processing
    The Transact-SQL scripts can be very basic SQL statements, such as:
    sphelp_db sampledb1; exit (select 1);
    The scripts can also be more complex statements that involve functions and other data processing.

Configuring Sybase Monitors

To configure Sybase monitors, do the following:
  1. In the Sybase monitor template, complete the monitor information fields.
  2. To learn how to configure monitor information fields, see Monitor Identification.
  3. Complete the following fields:
    • Port
      The number of the port number on which the database is listening. The default is 5000.
    • Port Check (Optional)
      Select this option to open a socket connection that determines whether the database is listening on the defined port.
    • Username
      The user name that is required to login to the database.
    • Password
      The password that is required to login to the database.
    • Database
      The name of the Sybase database to which you want to connect.
    • Script
      Click the Script checkbox and then type or copy the script that you want Uptime Infrastructure Monitor to against the database into this text box. Use this option if you do not have access to the file system on the Monitoring Station or if your script is short or does not regularly change.
    • Script File
      Click the Script File check box and then enter the full path on the Monitoring Station to the script that this monitor runs against the database.

      Info

      If you configured your database to allow logins without a user name and password and you specify the script file but no login information, the script fails and an error message appears on the Global Scan dashboard. The script runs if you have configured your database to allow logins without a user name and password.

    • Match (Regular Expression)
      Enter a regular expression that you want to match against the string returned from the database. If the string matches, the status is OK. Otherwise, the status is Critical.
    • Response Time
      Enter the Warning and Critical Response Time thresholds. For more information, see Configuring Warning and Critical Thresholds.
  4. Click the Save for Graphing checkbox to save the data for a metric to the DataStore, which can be used to generate a report or graph.
  5. Complete the following settings:
  6. Click Finish.