Advantage 8 included a feature for logging all queries run on the server. This can be a very useful tool for seeing what queries are being run and identifying unoptimized or slow queries. Query logging is enabled using a system procedure called sp_EnableQueryLogging which has five parameters.
The first is the name of the table to log to, second a flag to truncate existing data, third flag for logging only unoptimized queries, fourth minimum time before logging and finally an encryption password used if you wish to encrypt the table on free connections. If you are using a dictionary connection, you must be logged on with an administrator account and the query logging table will be added to the data dictionary.
The resulting log table contains the following information about the queries; ID, Start Time, Optimized, Return Code, End Time, Run Time, Database, Connection Name, Application ID and Query. By filtering on the Optimized field you can quickly identify queries which are unoptimized. You can also sort the data by Run Time to locate queries which take a long time to run. You can see the text of the query in the Query field.
To stop logging queries use the sp_DisableQueryLogging system procedure. This procedure stops query logging on the current data dictionary or for all free table connections. You can get more information about query logging in this tech-tip.
No comments:
Post a Comment