Phone Lines icon GFI Support

Articles in this section

See more

SQL Server Slows Down While Enabling Microsoft SQL Server Auditing

Author: Luis Fernandes Updated

Overview

By default, Microsoft SQL Server generates a large number of events for all kinds of operations performed on all databases the Microsoft SQL Server holds. This can lead to thousands of events generated per minute.

Due to a large number of events created, collecting all the information for Microsoft SQL Server using EventsManager always requires additional resources to be available on the Microsoft SQL Server.

This might slow down the SQL Server and EventsManager.

Environment

  • EventsManager
  • All supported environments

Root Cause

Enabling auditing/tracing adds a significant load to Microsoft SQL Server.

Resolution

If it is needed to audit all events (in comparison to only security-related events) limit auditing to only generate events on SQL level that refer to a database which need to be monitored:

  1. Start the EventsManager configuration tool.
  2. Navigate to Configuration > Event Sources > From Group Type > Database Servers Groups.
  3. Open the properties of the group itself or the server within a group.
  4. Open the Settings tab.
  5. Ensure to choose the option "Scan all the events that are related to the following databases only." and specify the database name(s).

To optimize this process further and limit the resources required by EventsManager, one can manually edit the trace file which is registered on the Microsoft SQL Server. In this way, it is possible to generate events only for operations.

To manually edit the trace file, perform the following:

  1. Open the folder: <GFI\EventsManager\Data>.
  2. Create a backup of the following files: <scan_profile.trc> and <scan_security.trc>.*
  3. Open the SQL Profiler and select File > Open. Select <scan_profile.trc> or <scan_security.trc> mentioned above.*
  4. After the file has been opened, choose File > Properties.
  5. Open the Events Selection tab and disable the events that are not relevant to your environment.
  6. Configure additional filters to improve performance event further under Column Filters.
  7. After the changes have been made, save and close the trace file.
  8. Restart the EventsManager service for changes to take effect.

Ensure to use the correct file that corresponds to the configuration of the SQL event source setting:

  • Use <scan_profile.trc> when having "Scan all the events for all databases." or "Scan all the events that are related to the following databases only." configured.
  • Use <scan_security.trc> when having "Scan only the security events for all databases." configured.

NOTE: Ensure not to audit the EventsManager database itself.