Library

How to track slow SELECT statements in the production Firebird database

Firebird SQL has very suitable means to track slow SQL queries and other problems with performance through the mechanism called TraceAPI plugins. HQbird (advanced Firebird distribution for big databases) includes tool FBPerfMon, which implements TraceAPI and offers easy GUI interface to track all performance problems in Firebird.
In order to find the slowest SELECT SQL statements in the production Firebird databases use the following instruction:

1. Request HQbird download information. (trial version will be sufficient for quick check-up). Download information will be sent by email.
2. Download and install HQbird ServerSide on the server (Windows or Linux) and HQbird Admin on the computer with Windows. On Windows HQbird ServerSide installer will install the necessary Trace API plugin automatically (for Firebird 2.5 or 3.0, for 32 bit or 64 bit)), on Linux it will require manual copying to the Firebird/plugin folder. Remove standard plugin (fbtrace.dll/fbtrace.so) and restart Firebird to activate the new plugin.
3. Start HQbird PerfMon Settings from Windows Start menu: IBSurgeon/HQbird Firebird Admin/FBPerfMon Settings.
4. Select and add the database to be traced. Specify exactly the same connection string as you use in your applications. If you use alias – specify alias, the full connection string – specify the full path.
5. Add trace session for the database
   1.   Create log database (where to store trace results)
   2.   Specify parameters to track (you can use default configuration initially and change it later, for example, enable SQL plan logging)
6. Start trace session. After the successful start you will see the notification, and then all SQL queries and related information will be written to the log database. Remember that tracing captures data from all connections, so log could grow pretty fast.
7. Wait some time (enough to capture problematic queries, usually 15-20 minutes) and then stop the trace session.
8. Start HQbird PerfMon Viewer (from Windows Start menu) and select from the list the log database that was used for the recent trace session (or use "Open existing database").
9. HQbird PerfMon Viewer starts with the server load graphs, to see statements, click on «Details» and then on tab "Statements" sort all SQL queries by "Total Time".
10. Copy texts of slow SQL queries with "Copy SQL text" button.