SEPM: poor database performance
| Article:TECH155046 | | | Created: 2011-03-08 | | | Updated: 2012-04-04 | | | Article URL http://www.symantec.com/docs/TECH155046 |
Problem
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
- Long delays loading tabs in the SEPM console
- Database timeouts or deadlocks showing in the SEPM tomcat logs
- Problems updating client definitions from the SEPM
- Clients blocked from the network in an Enforcer configuration (where the SEPM is too slow replying to the Enforcer client authentication requests)
Cause
Poor database performance can have a number of different causes; one identified cause on a larger network is excessive logging 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.
The best 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.
Solution
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.
- 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)
- A large number of Enforcer appliances (or DHCP Enforcer plugins) are used with a SEPM version prior to RU7 (see TECH155239).
- 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
- TECH131695 Separate the different database files on different drives
- TECH136691 Stored procedures to improve SEPM Reporting performance.
- TECH92852 Gathering SQL trace logs for the Symantec Endpoint Protection Manager (SEPM) database
- TECH155239 Enforcer appliance is unable to register with SEPM (relating to a database performance issue resolved in RU7)
- TECH154741 Significant Replication performance decrease after migration (resolved in RU7).
- TECH169953 SEPM: SQL server is using too much memory.
(Note that "SQL server is using too much memory" is not a performance issue. See Microsoft KB 321363 for an explanation of the intended behavior of the SQL Server buffer pool cache.)
|
|
Article URL http://www.symantec.com/docs/TECH155046
Terms of use for this information are found in Legal Notices
Thank you.