Applies to
ApexSQL Log
Summary
This article explains methods that can be used to improve auditing performance and speed-up the exporting operation with ApexSQL Log.
Description
One of the biggest performance impacts can come from auditing via network, especially for heavy traffic and/or low performance networks. In order to perform auditing, ApexSQL Log needs to read inputs – online transaction log, transaction log backup, and/or full backup files. The size of these files can be substantial, and transferring (reading) those over network can be limited by the network structure, consequentially degrading the performance of auditing process. When possible, auditing should always be done on the local system for best performance. Also, additional backup files used in the process will ideally exist on the local system, to avoid limitations of the network trafficking.
The next most common way to achieve better performance is to utilize filters. This can significantly decrease exporting time, especially when dealing with large transaction log files and/or backup files.
Time range filter
Instead of auditing the whole transaction log, it is highly beneficial to audit smaller chunk(s). The smaller the time window is, it is likely it will contain less operations, whose number directly influences exporting process. The fewer operations there are the better performance will be achieved. This can be achieved via the Time range filter. The smaller the time window is, the easier and faster will it be to complete the auditing operation. The Custom From/To filter offers the highest precision.
Operations filter
Auditing of schema operations (DDL) greatly impacts performance. Ignoring some DDL operations will result in slightly better performance. But, ignoring all DDL operations will result in a very significant performance gain, improving the exporting speed even several times. Hence, excluding all DDL operations is highly recommended, when possible.
In addition to excluding DDL operations, exporting performance can further be increased by excluding unneeded data operations (DML). This will result in better performance gain than excluding just some DDL operations, but not as significant performance improvement as when excluding all DDL operations.
Table filter
Another important and highly beneficial filter that can enhance exporting performance is the Table filter. Both system tables (disabled by default) and user tables can be numerous. To increase performance, simply exclude unneeded tables from the process. Depending on the database structure and tables content, the process might speed-up several times. When using table filters, it is important to keep in mind that when DDL auditing is enabled (when at least one DDL operation is selected for auditing) it is not possible to audit system tables because DDL operations are in reality a collection of DML operations on system tables.
The Field values filter is one of the filters that have a negative influence on the auditing process. Over-use of this filter impacts performance by prolonging and slowing down the process. Even a simple field value filter can substantially degrade the performance because all operations need to be read and their values checked. Due to these facts, it is advisable to avoid excessive usage of the field values filters.
Another feature that will negatively impact the performance is adding additional columns in results. Each column adds new details for each audited operation and consequentially slows down the auditing due to the fact that additional details need to be extracted from transaction log file and presented in the application grid. Also, disabling some of the columns enabled by default can positively influence the auditing performance. Even though columns filter does not affect performance significantly, it is recommended that users chose only columns that are really needed, which will also remove excessive noise.
Grid filter
In addition to the Session filters and their direct influence on the performance, the Grid filter offers another chance to filter the results and to influence performance of the Export process. The Grid filter does not influence export performance directly – it has its own flow and time needed for its execution which is drastically smaller than the auditing time, since the data has already been processed. The main usage of Grid filter is easier tracking of the specific operations that has been audited i.e. user needs to create undo of an update operation, and the time frame is known, so are the affected tables, the Grid filter can then be utilized to help pinpoint the possible candidates for creation of undo script.
When dealing with files with large size, or with files in great numbers it is possible to encounter performance degradation. Commonly, most of these files are unneeded for the process, so it is advisable to exclude them. Also, if you are not sure about the needed files, it can be beneficial not to process them all at once, but to split up the process of discovering the needed files into several groups.
Export to file is another way to go if you need to increase the process performance. This option allows you to skip the (sometimes) time-consuming process of grid population while opening results in grid, and to directly export results to a specified file format, or create undo and redo scripts. In general, audit into grid should be utilized when there is a need for additional user interaction with the displayed result. If there is no need for additional user interaction, and exporting can be done directly, it is better to skip grid results display, and to export results to file directly from the audit wizard.
The final way to increase performance of ApexSQL Log is to utilize the CLI. Operating directly from the CLI, in comparison to operating from the GUI will save memory, which can greatly affect performance, especially on memory-intensive systems. The most efficient way to do this is to use Create batch command from the auditing wizard. After choosing output details and applying desired filters, you can create batch file which is easy to use and edit. Additionally, Batch script that can be manually used will be created in the process.
