Heartbeat Query - Performance Tuning

A heartbeat query can be any SQL statement run at specific intervals whose response time is
being monitored.

Measure response time as an indicator of system demand or system/database hangs
Initiate an alert system if response time degrades so that you can take appropriate action

Heartbeat queries are classified as

System Heartbeat Queries:

used to check overall system/database hangs, to react when response time reaches a certain thresholds, or when stalled, such as send alert and/or capture system level information
it should execute diagnostics that capture the state of the system if performance stalls
they are intended to focus on Teradata core system. They should be short running , low impact queries on tables that are normally not write locked
they are more useful when run frequently (for example 5-10 mins)
they should be run on system nodes (which eliminates other factors like middle tiers, network connections)

Example of system heartbeat query:

select * from dbc.dbcinfo;

As the query runs, Teradata Manager can monitor the query, logging start and end times.

Production Heartbeat Queries:

response times play a major role in the production heartbeat queries, these need to be monitored and stored for tracking purposes
response time are an indicator of system demand. When system demand is high, heartbeat response is high
they can be run on production user tables
monitor overall response
monitor specific area of the job mix
can be more complex and similar in nature to a particular type of production query, running in the same Priority Scheduler performance group
they are run less frequently (for example 20-60 mins)

Teradata