Using Canary Queries - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Using Canary Queries

Canary queries are SQL statements that represent the characteristics of a particular application. These queries are introduced into the work stream entering your data warehouse in order to monitor system responsiveness. Some sites run canary queries once every 1 to 5 minutes, while others run them only every 30 minutes. Canary queries provide a quick health check for tactical applications specifically and for your Teradata Database system in general. By monitoring the response times when the canary queries run, you can identify delays or congestion states early on.

Some users run canary queries in each Priority Scheduler Performance Group that is active, others only use them for their tactical query applications or in groups that have a defined service level.

Many sites make charts or graphs of canary query behavior during the previous 24 hours, with particular attention paid to any outliers. When the response time for a canary query is out of line with expectations, you can attempt to match that occurrence to system conditions at that time. It is a common practice for canary queries that exceed a specified response time threshold to send an e-mail alert to the DBA staff.

Take care to avoid over-scheduling canary queries. If the information your canary queries produce is too large to be easily processed and analyzed, reduce the scope of their execution.

Teradata Viewpoint is designed to automate the submission of canary queries. See the Teradata Viewpoint online HELP to learn how to do this.

The term canary query comes from the 19th century coal mining practice of lowering a caged canary into a mine to detect the presence of odorless, toxic gases. If the canary was dead when the cage was pulled back to the surface, the miners knew not to descend into the shaft until it could be properly ventilated. Because these small birds are so sensitive to toxic fumes, they were also an excellent early warning system for the detection of toxic gases that might build up after the miners had already begun work in the mine. If your canary died, you knew it was time to ascend to the surface until the workplace could once again be made safe.