SEPM: poor database performance

The Microsoft SQL database used by the Symantec Endpoint Protection Manager (SEPM) is showing symptoms of poor performance, showing on the SEPM server as for example:

• Problems logging in to the SEPM console.
• Tabs take a long time to load in the SEPM console.
• Reports that are run from the SEPM console time out.
• Database timeouts or deadlocks showing in the SEPM tomcat logs.
• Problems updating client definitions from the SEPM.
• Issues or long delays when generating delta definitions on the SEPM, clients request full.zip updates.
• Clients blocked from the network in an Network Access Control configuration (where the SEPM is too slow replying to the Enforcer client authentication requests).

Poor database performance can have a number of different causes; one identified cause on large sites is when excessive logging has been enabled in the SEP client firewall policies.

Verify if traffic logging or packet logging has been enabled for a particular firewall rule that is likely to match large amounts of traffic across all client machines, such as if logging has been enabled for an "allow all applications" or "block all" rule.
 

To see the number of records kept in the database for different firewall rules the following SQL query can be used: "select count(*) as records,rule_name from sem5.agent_traffic_log_1 with (nolock) group by rule_name order by records desc" (repeating the query for agent_traffic_log_2).

One way to verify if a large amount of the queries from SEPM that are handled by the database are related to agent logging is to use the SQL Profiler tool that comes with Microsoft SQL server. Article TECH92852 describes the steps for capturing an SQL trace log. If dozens or hundreds of queries containing "insert into AGENT_TRAFFIC_LOG_" can be seen during each second then performance may be impacted.

The name of the problematic firewall rule should also be visible inside each query in the trace, as well as the name of the application sending or receiving the traffic.

To edit the firewall policy use the Clients - Policies tab in SEPM; under Rules in the firewall policy check which individual rules have "Write to Traffic Log" or "Write to Packet Log" checked. Packet log in particular will generate large amounts of entries and is best used only temporarily for troubleshooting purposes.

If a particular problem rule has been identified (like a "block all" rule at the bottom of the list), and a particular application (for example ntoskrnl.exe) is the cause of a majority of these entries then one option is keeping the traffic logging enabled for the "block all" rule, while adding a new block rule just above matching only this application (and without logging enabled). This way other blocked traffic that may be of interest to collect logs for can still be captured.

Other possible causes of SEPM database performance issues include;

• The SEPM database is on a dynamic virtual disk, suffering a disk I/O bottleneck.
• Database maintenance has not been configured on the Microsoft SQL Server.
• Index fragmentation may be high for the SEPM database tables (Microsoft TechNet article).
• The hardware on the database server or SEPM server is not scaled appropriately for the amount of clients handled by the SEPM.
• The network link between the SEPM server and database server is poor.
• The SQL server Recovery Model may be configured as Full instead of Simple, increasing transaction log overhead. (MSDN article)
• SEPM log tables are switching frequently due to too many logs being generated/uploaded (see TECH185648).

Other articles with information about SEPM database performance:

• TECH97727    Improving SEPM Performance with the SEM_GETUSN Stored Procedure.
• TECH136691  Stored procedures to improve SEPM Reporting performance.
• TECH92852    Gathering SQL trace logs for the Symantec Endpoint Protection Manager (SEPM) database.
• TECH169953  SEPM: SQL server is using too much memory.