Advantage Developer Zone

 
 
 

SQL Tools

Tuesday, September 19, 2006

Advantage has several tools and features that can help you optimize your SQL statements to get the best possible performance. New to Advantage 8.0 is the ability to view and cancel all currently running queries and the addition of query logging, which generates a table containing information about all the queries processed on the server.

Viewing Currently Running Queries

The ability to view the active queries on the server provides a powerful tool for diagnosing server and query performance. Information about the current queries includes the Query Number, whether it is Active, Percent Complete, Connection Name, Seconds Until Finished, Start Time, Database, and the Current Query statement itself. This information can be used to determine which queries are running on your system and which ones are taking the most time to run. You can also identify queries that are taking much longer to process than you would expect. These queries can be canceled to free up resources for other users.
Note:

Keep in mind, canceling a query at the server could produce unexpected behavior of the client application.

Although there are several ways to obtain information about currently running queries, the easiest way is by using the Advantage Data Architect (ARC) management utility. In ARC 8.0, a new tab labeled “Active Queries” shows all the queries currently running on the specified server. Selecting a query from the list will display the query details in the pane below the grid. The Active Queries tab is shown below:
 

To cancel individual queries, right-click on the query you wish to cancel and choose Cancel Query from the menu.

 

Query Logging

Advantage 8.0 also includes a query logging capability that can be very useful when debugging query problems, determining bottlenecks, and evaluating performance. However, query logging does consume server resources and could impact performance. Additionally, it logs the text of the SQL statement, which could pose potential security issues. Essentially query logging is best used during development and testing, and should be used on a very limited basis in a production environment.

Enabling Query Logging

When enabling Query Logging, the server records information about queries to a specified table. This table includes the following information: ID, Start Time, Optimized, Return Code, Rows Affected, End Time, Run Time, Database, Connection Name, Application Name and the Query statement. An example of the contents of this table is displayed below:
 
Query logging is enabled by running the sp_EnableQueryLogging system procedure. This procedure takes the following parameters:
 
Parameter
Setting
Result
Tablename
Name of table to log query information to.
Information will be log to the designated table.
TruncateExistingData
True
Clears the table before beginning the logging.
LogOnlyUnoptimizedQueries
True
Only logs un-optimized queries.
MinimumTimeBeforeLogging
Numeric entry time (in minutes) to be allowed before logging begins.
Logs queries that are taking a long time to process.
EncryptionPassword
Actual password required to encrypt the logging table.
If set, logging table will be encrypted. Not required with data dictionary.
 
Example:
The following procedure call will log all queries to a table called QueryLog. The table will be emptied and all queries will be logged. No encryption password is set.
 
EXECUTE PROCEDURE sp_EnableQueryLogging( ‘QueryLog’, True, False, 0, NULL)
 
The logging table can be viewed at any time once it has been created. A tremendous amount of information can be gained by reviewing this table.
 
To optimize these queries and improve performance, copy the SQL statement from the Query field where the Optimized field is False and view the resulting SQL Execution Plan. SQL Statements that take a long time to execute can be located by sorting the RunTime field. The ResultCode field will identify queries that failed to execute properly.
 
Note:

More information on using the SQL Execution Plan tools can be found in the August 2006 Tech Tip.

 

Disabling Query Logging

Query Logging can be disabled by calling the sp_DisableQueryLogging procedure. This procedure does not require any parameters and it will disable logging for all connections on the server. The sp_ViewQueryLogging system procedure will return a list of all connections that have query logging currently active.
 
Some final notes about query logging:
  • You must be logged in as ADSSYS on a data dictionary connection to enable query logging and an encryption password is not required.
  • We recommend that you encrypt the query log table if you are using a free table connection for security reasons.
  • You can log queries if you are using local server, however, it must be enabled for each connection instance in order to capture the queries run by each connected user.

Summary

With the release of Advantage 8.0, we have enhanced SQL query management capabilities.
The Active Queries tab in the ARC management utility provides a wealth of information about currently running queries and allows individual queries to be canceled. The Query Logging tool provides assistance in debugging SQL queries, identifying potential performance issues, and by incorporating query logging into your test process, it can bring great benefit to development efforts.