FBScanner is a part of HQbird Standard now!

FBScanner (Firebird Scanner) is a tool to solve all types of performance problems with Firebird.

FBScanner Documentation

• FBScanner User Guide (English)
• Guia do Usuário FBScanner (Portuguese)

FBScanner Modules

FBScanner is one of the most efficient IBSurgeon tools: it tracks SQL traffic between server and clients. FBScanner features:

List of FBScanner's most exciting abilities are:

• Logging SQL queries, connections and transactions with all parameters for detailed analysis (for the single or several workplaces), in order to find performance bottlenecks at Firebird (ALL versions) and InterBase (ALL versions).
• Real-time monitoring of connections. FBScanner Viewer shows all connections to the selected database server: the IP/DNS name of the connected client, database and connection time.
• Real-time monitoring of SQL queries. For each connection, FBScanner shows all the currently running SQL queries along with their transaction parameters.
• Detection of the oldest connection and the oldest active transaction to allow you to analyze that may have non-optimal transaction behavior, or incorrect transaction design or show users who might be using the application in a manner that may be affecting performance.
• Client disconnects (10054 errors). Check that disconnection is taking place correctly and you can also use this software to disconnect users in order to perform maintenance or database upgrades.
• FBScanner allows the routing of specific applications or particular users to allow you to zoom in on specific applications or users.

Quick Tour

How can FBScanner help to identify and resolve Firebird or InterBase performance problem? Let's quickly review the most interesting features of FBScanner, and start with LogAnalyzer module.

STEP 1: LogAnalyzer "Server Load"

Download FBScanner demo version and install it. Run "FBScanner Service Settings" from the Start menu, then click "Advanced options", tab "SQL Log ".

Create log database (it should be on another Firebird instance on this or another computer - please refer to "FBScanner User Guide", pages 14-16 for details). After that, all SQL traffic which goes through FBScanner will be stored in the log database.

Run "FBScanner Log Analyzer " and connect to the log database. At the first tab, "Server load", you can see an overview of SQL connections and statements. It is easy to see peaks of load and determine the moments where performance was bad.

You can zoom and scroll this performance graph, turn on and off parameters to show - in order to catch the moment when the problem starts. After that right click on the point where you want to go into details and choose "Go to the SQL list on HH:mm".

STEP2: SQL Log in LogAnalyzer

If you have selected tab "All Statements", subsequent list of all SQL queries will be shown. Please note that there are SQL queries from all connections which are logged by FBScanner (it is possible to setup FBScanner to monitor only the one connection or group of computers, or only selected application - all you need is to use an appropriate connection string and/or ports - please read about it in "FBScanner User Guide").

You can narrow your search - set start and end moments and then queries on EXECUTION_TIME in order to see the longest queries.

For each query you can see SQL query text, its parameters, start/end time, count of fetched records, and also know who and when executed it: IP address, username, etc. 

You can also force FBScanner to log SQL plans, for this you need to set an appropriate checkmark in "FBScanner Service Settings".

When you have identified the query which has problems, you can see its connection and transaction: for this right-click and choose "View connection and transactions for statement ID=...". It will bring you the tab "All connections and Logs".

STEP 3: Connections and Transactions

Tab "All connections and Logs" contains the list of all connections and related as master-detail transactions and statements.

When you click on the SQL query in the grid below, all statements in the frames of the same transaction will be marked bold. It gives you the understanding of real transactions flow. Also, there are parameters of the transaction (empty transaction parameters mean that this transaction has the default "wait, write, snapshot" parameters).

Also, FBScanner LogAnalyzer allows to run selected query against another database version and compare its performance. It also can perform bulk SQL execution, which can be useful in case of migration between Firebird versions.

Important! Demo version of FBScanner stores some queries texts as "Demo", and it works only 7 days. Full version does not have these restrictions.

STEP 4: FBScanner Viewer

FBScanner Viewer is a part of FBScanner which is responsible for real-time tracking of connections, queries, and transactions. FBScanner Viewer connects directly to FBScanner Service and retrieves SQL traffic information.
FBScanner Viewer can be run on any machine in local network: it connects through port 30100 by default and pulls online data from FBScanner Service.
By default, FBScanner Viewer refreshes its status every 0.5 sec, and also possible to see 20 most latest queries of the connection.
For slow (Internet) connections FBScanner Viewer can be set to refresh data each 5 second, for example.

Also, FBScanner Viewer can be used for connections management: it can disconnect users (selected, all, by mask) with simulation of 10054 disconnect error, and supports white- and black-list functionality.

Tips for successful optimization

Important! It is recommended to start performance problems investigation with MON$Logger: take several snapshots from the database during the heavy load time (when users experience the most difficult slowness), and try to figure out what business processes consume the most IO (attention to queries with high READ_SEQ_READS and RECORD_IDX_READS values).

Then install FBScanner Service on these workstations (or use dedicated server) and log all queries from suspicious workstations: investigate their EXECUTE and PREPARE times, check SQL plans, etc. Find and fix the problem, then check that new version runs faster than previous!

Use FBScanner Viewer for real-time viewing of queries executed on the server (this feature is also very useful for debugging complex SQLs).

FBScanner description

FBScanner is a powerful tool for Firebird and InterBase administrators and developers, designed for interception and analyzing of TCP/IP traffic between server and client applications.
FBScanner extracts in real-time a lot of details about connections, queries (statements), transactions, mark and track all these traffic objects with unique tags mechanism. FBScanner also can log all activity to the external Firebird database and perform a number of unique administrative tasks.

FBScanner (Firebird Scanner) is a tool that can monitor and view all traffic between Firebird and InterBase servers and their client applications.

FBScanner Service works as a gate (proxy) at the computer with Firebird/InterBase (Windows only) or can be installed on the remote computer and work with Firebird at all supported operation system (Windows, Linux, Mac OS X, HP-UX, etc).

FBScanner can be used to profile database applications, monitor user activity, manage database connections (including client disconnection on both Classic and SuperServer architecture). It’s also ideal for troubleshooting INET errors (INET/inet_error: send errno = 10054), as well as auditing existing applications and performance tuning.

FBScanner supports Firebird (1.0, 2.0, 2.1, 2.5) and InterBase (4.0, 5.x, 6.x, 7.0, 7.1, 7.5, 2007, 2009, XE, XE3).

FBScanner is transparent as far as the database application is concerned and does not require any changes in application or database source code, logic or configuration.

FBScanner V3.x works on Windows only and requires the .NET 2.0 (or higher) Framework to be installed. 

Presentations about FBScanner

How FBScanner works

FBScanner Service

Important! In this section we don't describe work of MON$Logger and TraceAPI Plugin, it is devoted only to FBScanner Service, FBScanner Viewer, and LogAnalyzer.

The FBScanner Service normally has to be installed on the same computer where the Firebird or InterBase server is running (as an option, you can install it on other computer and then redirect traffic to the actual Firebird or InterBase server). During installation the FBScanner configuration utility will ask you to choose: install FBScanner to the default port of InterBase or Firebird (3050) and change the configuration of Firebird service to 3052 (this can be adjusted), or install FBScanner to listen on another TCP port.

Important! FBScanner Service works only with TCP connections, so clients using either local (XNET) and NetBEUI (WNET) connection strings will bypass FBScanner. To monitor such client applications you should use MON$Logger and TraceAPI Plugin.

If you choose the first installation option, all of the applications requests will pass through FBScanner Service.

Server-side installation	Remote installation

Performance impact

The usual scenario of performance optimization process includes installation of FBScanner on 1-2 typical workstations to catch the typical SQL traffic. There is no reason to intercept and log all SQL traffic from all workstations - there is too much information to analyze, and too many repeating queries.

However, if you want to install FBScanner as a proxy for the whole server and log all operations there, make sure to provide the same IO bandwidth and comparable CPU and RAM as on the production server. For high-load databases, FBScanner's log can grow by several Gb per day. In such cases, we recommend dedicated (remote server) installation of FBScanner.

FBScanner Service does not change anything in transferred traffic and works simply like a transparent proxy, so all applications will continue as normal. FBScanner Service consumes approximately 125-150 Mb of memory for monitoring 50-70 active clients, and it will decrease database performance by 5 to 15% when in use. Memory consumption depends on the client application design. Required IO bandwidth depends on the SQL traffic intensity.

During testing, we have checked FBScanner at work in several real-world environments and applications where the database has 30-200 active connections and noticed that the performance degradation was minimal. In many cases, users were unable to figure out that monitoring is active.

Segmentation of Traffic

The use of FBScanner on a non-standard port allows it to segment different traffic from different applications. For example, there are applications A and B. B is an old and stable application which does not require any tuning or monitoring, and A is a new application. It’s easy to setup connection string for the new application A to use a non-standard port (e.g server/3070:Disk:\Path\database.gdb) and route all requests from the A application via FBScanner in order to monitor SQL queries and the application behavior.

Using this approach you can easily recognize and solve issues within a production environment, whether the issue comes from the new application, or even if it comes from the particular segment of your local network.