Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >
article

KB-4843: High CPU observed in SQL server when using per command auditing due to lack of index

Centrify DirectAudit ,  

12 April,16 at 11:13 AM

Applies to –  All versions of Centrify DA 3.x (per command auditing)

Problem: 

If per command auditing is enabled and if customer is auditing a large number of users/commands, the performance of database insert may get affected because of lack on a certain index on the Session table of active Audit Store database. The lack of index may cause high CPU usage especially when the DirectAudit system is auditing a large number of users or commands. This issue may affect customers who are using per-command auditing more
compared to customers who are using shell auditing.

Cause

When per-command auditing is enabled, one entry is made in session table of active Audit Store database for each audited command executed by the
user. E.g. If customer is auditing dzdo command, there will be x number of rows in session table where x is the total number of times the dzdo command
was executed by one or more users. Before this row is inserted, another select query is run which searches session table and performance of this query
may be affected depending on amount of data in session table. If there is a large number of rows in session table, lack of index affects the query performance.

Workaround:

Customer can add a new index to the active Audit Store database to speed up the search query. 

The following SQL query can be run on active Audit Store database to create the missing index,

CREATE NONCLUSTERED INDEX IX_Session_MachinePrincipal_State_Tty
ON [dbo].[Session] ([MachinePrincipal],[State],[Tty])
INCLUDE ([SessionId])


Resolution:

This issue is targeted to be fixed in Centrify Suite 2015

Still have questions? Click here to log a technical support case, or collaborate with your peers in Centrify's Online Community.