How to improve ApexSQL Log performance over a network?

Applies to
ApexSQL Log

This article describes how to improve ApexSQL Log performance when the audited transaction log is located on a remote server

If the transaction log audited by ApexSQL Log is located on a remote server, it’s possible to experience a noticeable increase in the time needed to retrieve the database log information. This decrease in performance can be noted particularly in scenarios where the remote server is accessed via a slow broadband or VPN connection or when the audited transaction log file is quite large. Usually, the biggest chunk of time spent on auditing in these cases is wasted on waiting for the remote server to respond. In some cases, that chunk can take up to 99% of the time spent on the entire process

However, the very low effective I/O speed due to network I/O bottlenecks can be mitigated by:

  1. Improving network I/O
  2. Moving the auditing process closer to the server that stores the transaction log sources and to the server that stores the database
    If the transaction logs and the database aren’t stored on the same server, it’s better to have the auditing process running closer to the transaction log sources than the database, since reading the transaction log sources has the most impact on I/O
  3. Moving the transaction log sources closer to the auditing process
    This can be achieved by auditing transaction log backups or detached transaction log files, instead of auditing the remote online transaction log files

About ApexSQL Log
ApexSQL Log is a SQL Server Transaction Log reader that allows viewing transaction log data in read-friendly format. Audit and undo SQL database changes of your choosing. Determine who changed the data and when the change occurred. Read the transaction log to find out who created, changed or dropped a database object

Last updated
July 26, 2012