Teratrax Performance Monitor

Teratrax Performance Monitor has been discontinued. Please refer to the following help file (v4.0) for details on using an existing copy of this product:

Teratrax Performance Monitor is a low-footprint performance monitoring tool for SQL Server. It is designed to help you isolate SQL Server bottlenecks and identify potential problems in your mission critical Microsoft SQL Server databases.

Teratrax Performance Monitor consists of two components that are installed separately:

Teratrax Performance Agent

Monitors a single SQL Server instance in the background and collects performance data for historical analysis. Teratrax Performance Agent can monitor servers locally and remotely. For best results, Teratrax recommends installing Teratrax Performance Agent locally on the server machine.

Teratrax Performance Agent is installed separately from Teratrax Performance Viewer. It collects performance data for historical analysis in a database on the monitored instance. Teratrax Performance Agent runs as a service and monitors only one SQL Server instance. To monitor another instance of SQL Server running on the same machine, you need to install a copy of Teratrax Performance Agent on a different machine and configure it to monitor the other instance remotely.

To monitor a group of SQL Server instances running on separate machines, install a local copy of Teratrax Performance Agent on each machine and configure it to monitor each instance locally. By default Teratrax Performance Agent queries the monitored SQL Server instance every five minutes to check its pulse and collect performance data. The data collected can be viewed and analyzed from Teratrax Performance Viewer.

Supported Database Servers

SQL Server 2000 – All Editions
SQL Server 2005 – All Editions

Supported Operating Systems

Windows Server 2003
Windows XP
Windows 2000 (SP3 or higher; ADO 2.7 or higher)

Hardware Requirements

Intel Pentium III or compatible 600 MHz or higher processor
256 MB of RAM
15 MB of hard disk space

Teratrax Performance Viewer

Provides a client interface to view SQL Server performance information, analyze performance data collected by Teratrax Performance Agent, and setup e-mail alerts for various performance events. You can register and connect to an unlimited number of SQL Server instances monitored by Teratrax Performance Agent.

Teratrax Performance Viewer provides a user interface (client) to view and analyze SQL Server performance information collected by Teratrax Performance Agent. It allows you to register an unlimited number of SQL Server instances monitored by Teratrax Performance Agent.

Supported Database Servers

SQL Server 2000 – All Editions
SQL Server 2005 – All Editions

Supported Operating Systems

Windows Server 2003
Windows XP
Windows 2000 (SP3 or higher; ADO 2.7 or higher)

Hardware Requirements

Intel Pentium III or compatible 600 MHz or higher processor
256 MB of RAM
5 MB of hard disk space

Installing Teratrax Performance Agent

The Teratrax Performance Agent install program guides you through the installation in simple and easy steps. You can configure the SQL Server instance you want to monitor during installation or at a later time. You can install only one copy of Teratrax Performance Agent on each machine. To monitor another instance of SQL Server running on the same machine, you need to install a copy of Teratrax Performance Agent on a different machine and configure it to monitor the other instance remotely.

To monitor a group of SQL Server instances running on separate machines, install a local copy of Teratrax Performance Agent on each machine and configure it to monitor each instance locally.

Teratrax Performance Agent does NOT require a computer reboot after installation.

Upgrading to Version 4.0

Teratrax Performance Agent 4.0 can run in parallel with previous versions of Teratrax Performance Monitor. However, Teratrax recommends that you uninstall previous copies of Teratrax Performance Monitor before installing Teratrax Performance Agent.

Uninstalling Version 3.0

You can simply uninstall Teratrax Performance Monitor 3.0 from the “Add or Remove Programs” dialog in Windows Control Panel. The uninstall program removes both Viewer and Agent belonging to version 3.0. Uninstalling version 3.0 does not remove the TPM30 database from the server. You can manually delete the database if you are not using the historical data.

Uninstalling Version 2.0/2.5

To uninstall Teratrax Performance Monitor 2.0 or Teratrax Performance Monitor 2.5, use the Uninstall shortcut in the Teratrax program group in the Start menu. Uninstalling these versions from the “Add or Remove Programs” dialog in Windows Control Panel does cleanly remove all components from your computer. Uninstalling version 2.0/2.5 does not remove the associated database from the server. You can manually delete the database if you are not using the historical data.

Uninstalling Version 1.0

You can simply uninstall Teratrax Performance Monitor 1.0 from the “Add or Remove Programs” dialog in Windows Control Panel.

Configuring Teratrax Performance Agent

The user interface for Teratrax Performance Agent consists of the following tabs:

Monitoring Tab

The Monitoring tab allows you to specify which SQL Server instance you want to monitor and configure monitoring thresholds for slow SQL code, SQL Server blocks, and SQL Agent jobs.

  • Instance Name: The Instance Name specifies which SQL Server instance is monitored. To change the monitored instance, click the Change button and enter a new instance name in the Server Configuration dialog.
  • Authentication: Indicates whether Teratrax Performance Agent is using SQL Server authentication or Windows authentication to connect to the instance. Windows authentication can only be used on local instances and the instance name must start with “(local)”. Authentication can be changed from the Server Configuration dialog.
  • Monitor This SQL Server Instance: Check this box and click Apply to start monitoring the specified SQL Server instance. To stop monitoring, simply uncheck the box and click Apply. Configuring the SQL Server instance name and authentication alone does not start monitoring.
  • Capture all SQL code that take longer than x milliseconds to run: Check this box if you want Teratrax Performance Agent to trace slow performing SQL batches and RPC events. You can also specify the minimum duration required for collected batches or RPC events. You can set the duration to 0 to collect all SQL batches and RPC events. However, Teratrax recommends a value of 3000 ms or higher on a SQL Server instance with a high volume of transactions. Stored procedure calls are either reported as RPC events or batch events depending on the way they are called from client applications.
  • Capture SQL Server blocks that exceed x millisecond: Check this box if you want Teratrax Performance Agent to monitor SQL Server blocking events. You can also specify the minimum duration required for a block to be captured.
  • Capture Agent jobs that fail or take longer than x seconds to run: Check this box if you want Teratrax Performance Agent to monitor SQL Server Agent jobs that fail or take a long time to execute.
  • Defaults: Click this button to reset all default settings.

Once you complete all the necessary configurations, click Apply for the changes to take effect.

Alerts Tab

The Alerts tab allows you to configure the Outgoing Mail Server (SMTP) and the Server Down Alert. Teratrax Performance Agent uses the SMTP server specified to send out Server Down Alerts and other alerts created and managed from Teratrax Performance Viewer.

    • Outgoing Mail Server (SMTP): Used by Teratrax Performance Agent to send out e-mail alerts.
      • SMTP Server: The SMTP server name (Example: mail.domain.com).
      • Port: The port used by the SMTP server.
      • User Name: The login for SMTP authentication. Usually, this is either an e-mail address or the first part of an e-mail address. E-mail alerts received by end users indicate that they are sent from this account.
      • Password: The password used for SMTP authentication.

Teratrax Performance Agent does not support SMTP server that require encrypted connections (SSL).

    • Server Down Alert: An e-mail alert that notifies you if SQL Server stops responding.
      • Send me an e-mail alert when this server instance stops responding: Check this box if you want to receive an e-mail message notifying you that the SQL Server instance is not responding.
      • E-mail: E-mail alerts are sent to this e-mail address from the SMTP user name indicated in the “Outgoing Mail Server (SMTP)” section. This can be any valid e-mail address.
      • Send Test E-mail: Click this button to send a test e-mail to the specified e-mail address.

Teratrax Performance Agent sends an e-mail alert every hour until the server starts responding again.

  • Verify SMTP: Click this button to verify SMTP configuration.

 

Once you complete all alert configurations, click Apply to save all changes.

About Tab

Displays the Teratrax end user agreement and licensing information.

Continuous Monitoring and Alerting

Teratrax Performance Agent has a very low footprint on the server. It is designed to monitor SQL Server instances permanently and not only during periods of slow performance. You can control how much data you want Agent to collect by simply changing the threshold values in the Monitoring tab. Agent creates an optimized trace and, by default, accesses the server only once every five minutes to retrieve performance counters. If the “Capture SQL Server blocks that exceed x milliseconds” option is enabled, Agent will check for blocks every twenty seconds.

Permanent monitoring allows you to ensure that all of your enterprise servers are up and running. If any of your servers is not reachable, Agent will send you an e-mail message indicating that the server could not be reached.

Permanent monitoring also allows you to view the history of SQL Server performance counters and cache hit ratios and compare it over time. It also allows you to take preemptive actions to correct performance problems before they escalate. For example, an UPDATE statement taking 100 ms today, could require more time as the table grows. With permanent monitoring, you can configure Agent to alert you when a query exceeds a certain duration. It can even send you an e-mail message indicating which SQL queries are performing slower than expected.

E-mail alerts indicating that the server is not responding (Server Down Alerts) are configured in the Alerts tab in Teratrax Performance Agent. All other e-mail alerts can be created and managed from Teratrax Performance Viewer.

Performance Views

The TPM40 database on the monitored SQL Server instance contains predefined SQL views that expose collected performance information to users and other programs. These performance views provide a comprehensive way for retrieving performance data without having to understand the table structure of the database. Teratrax recommends the use of these views instead of querying the tables directly. You can issue queries against these views with a WHERE clause to filter information as needed.

SQL_Server_Information

This view contains details about the monitored instance of SQL Server.

System_Utilization_Hourly

This view reports aggregated information about performance counters on an hourly basis. CPU, Memory, Cache_Hit_Ratio, and Proc_Cache_Hit_Ratio figures represent the average percentages of utilization. Reads and Writes represent total non-cached disk operations.

System_Utilization_Daily

This view reports aggregated information about performance counters on a daily basis. CPU, Memory, Cache_Hit_Ratio, and Proc_Cache_Hit_Ratio figures represent the average percentages of utilization. Reads and Writes represent total non-cached disk operations.

Slow_SQL_Code

This view contains information about slow SQL batches (including stored procedures) and RPC events. Duration and CPU_Time are in milliseconds. The Disk_Reads column represents logical reads (Pages read from cache). The Disk_Writes column represents physical writes (Pages written to disk).

Slow_SQL_Code_Stats_Hourly

This view contains hourly statistics about slow SQL batches (including stored procedures) and RPC events. Min_Duration, Avg_Duration, Max_Duration, and Avg_CPU_Time are in milliseconds. The Avg_Disk_Reads column represents logical reads (Pages read from cache). The Avg_Disk_Writes column represents physical writes (Pages written to disk).

Slow_SQL_Code_Stats_Daily

This view contains daily statistics about slow SQL batches (including stored procedures) and RPC events. Min_Duration, Avg_Duration, Max_Duration, and Avg_CPU_Time are in milliseconds. The Avg_Disk_Reads column represents logical reads (Pages read from cache). The Avg_Disk_Writes column represents physical writes (Pages written to disk).

SQL_Server_Blocking

This view contains information about concurrency and SQL Server process blocking. Duration is in milliseconds.

SQL_Server_Blocking_Stat_Hourly

This view contains hourly statistics about SQL Server process blocking. Duration figures are in milliseconds.

SQL_Server_Blocking_Stat_Daily

This view contains daily statistics about SQL Server process blocking. Duration figures are in milliseconds.

Deadlocks

This view contains information about SQL Server deadlocks.

Deadlocks_Stat_Hourly

This view contains hourly statistics about SQL Server deadlocks.

Deadlocks_Stat_Daily

This view contains daily statistics about SQL Server deadlocks.

Slow_Failed_Jobs

This view contains information about SQL Agent jobs that are performing poorly. Duration figures are in seconds.

Slow_Failed_Jobs_Stat_Hourly

This view contains hourly statistics about SQL Agent jobs that are performing poorly. Duration figures are in seconds.

Slow_Failed_Jobs_Stat_Daily

This view contains daily statistics about SQL Agent jobs that are performing poorly. Duration figures are in seconds.

Failed_Logins

This view contains information about failed login attempts.

Failed_Logins_Stat_Hourly

This view contains hourly statistics about failed login attempts.

Failed_Logins_Stat_Daily

This view contains daily statistics about failed login attempts.

Monitoring SQL Server Clusters

For a clustered SQL Server environment, Teratrax Performance Agent needs to be installed on a machine that is not part of the cluster. Furthermore, do not use individual node instance names or IP addresses. Use the instance name or IP address of the cluster (Virtual instance) to ensure that monitoring will be switched over to the next instance in the event of a failover.

Monitoring 64-bit Editions of SQL Server

Teratrax Performance Agent is fully compatible with 64-bit editions of SQL Server. The Agent itself is a 32-bit application. It can be installed and configured to run on x64 editions of Windows under Windows on Windows (WoW). For Itanium-based servers (IA-64), you need to install Teratrax Performance Agent on a 32-bit computer and configure it to monitor the 64-bit instance of SQL Server remotely. Itanium-based editions of Windows cannot run 32-bit applications natively.